SQL VISUAL QUICKSTART GUIDE- P34:SQL (pronounced es-kyoo-el) is the standard programming language for creating, updating, and retrieving information that is stored in databases. With SQL, you can turn your ordinary questions (“Where do our customers live?”) into statements that your database system can understand (SELECT DISTINCT city, state FROM customers;) | Finding Common Rows with INTERSECT Chapter 9 Finding Common Rows with INTERSECT An INTERSECT operation combines the results of two queries into a single result that has all the rows common to both queries. Intersections have the same restrictions as unions see Combining Rows with UNION earlier in this chapter. To find common rows Type select_statement1 INTERSECT select_statement2 select_statement1 and select_statement2 are SELECT statements. The number and the order of the columns must be identical in both statements and the data types of corresponding columns must be compatible. Duplicate rows are eliminated from the result. Listing uses INTERSECT to list the cities in which both an author and a publisher are located. See Figure for the result. Listing List the cities in which both an author and a publisher are located. See Figure for the result. Listing SELECT city FROM authors INTERSECT SELECT city FROM publishers city New York San Francisco Figure Result of Listing . 310 Set Operations Tips INTERSECT is a commutative operation A INTERSECT B is the same as B INTERSECT A. The SQL standard gives INTERSECT higher precedence than UNION and EXCEPT but your DBMS might use a different order. Use parentheses to specify order of evaluation in queries with mixed set operators see Determining the Order of Evaluation in Chapter 5. It s helpful to think of UNION as logical OR and INTERSECTION as logical AND see Combining and Negating Conditions with AND OR and NOT in Chapter 4. If you want to know for example which products are supplied by vendor A or vendor B type SELECT product_id FROM vendor_a_product_list UNION SELECT product_id FROM vendor_b_product_list If you want to know which products are supplied by vendor A and vendor B type SELECT product_id FROM vendor_a_product_list INTERSECT SELECT product_id FROM vendor_b_product_list If your DBMS doesn t support INTERSECT you can replicate it with an INNER JOIN or an EXISTS subquery. Each of the .
