SQL Run

Database Programming

« Previous

Home

Contents

» Next

9. Set Operations

Recall from Chapter 2 that set theory is fundamental to the relational model. But whereas mathematical sets are unchanging, database sets are dynamic—they grow, shrink, and otherwise change over time. This chapter covers the following SQL set operators, which combine the results of two SELECT statements into one result:

• UNION returns all the rows returned by both queries, with duplicates removed.
• INTERSECT returns all rows common to both queries (that is, all distinct rows retrieved by both queries).
• EXCEPT returns all rows from the first query without the rows that appear in the second query, with duplicates removed.

These set operations aren’t joins, but you can mix and chain them to combine two or more tables.

Combining Rows with UNION

A UNION operation combines the results of two queries into a single result that has the rows returned by both queries. (This operation differs from a join, which combines columns from two tables.) A UNION expression removes duplicate rows from the result; a UNION ALL expression doesn’t remove duplicates.

Unions are simple, but they have some restrictions:

• The SELECT-clause lists in the two queries must have the same number of columns (column names, arithmetic expressions, aggregate functions, and so on).
• The corresponding columns in the two queries must be listed in the same order in the two queries.
• The corresponding columns must have the same data type or must be implicitly convertible to the same type.
• If the names of corresponding columns match, then that column name is used in the result. If the corresponding column names differ, then it’s up to the DBMS to determine the column name in the result. Most DBMSs take the result’s column names from the first SELECT query in the union. If you want to rename a column in the result, then use an AS clause in the first query; see “Creating Column Aliases with AS” in Chapter 4.
• An ORDER BY clause can appear in only the final SELECT query in the union. The sort is applied to the final, combined result. Because the result’s column names depend on the DBMS, it’s often easiest to use relative column positions to specify the sort order; see “Sorting Rows with ORDER BY” in Chapter 4.
• GROUP BY and HAVING can be specified in the individual queries only; they can’t be used to affect the final result.

To combine rows:

• Type:

  select_query1
UNION [ALL]
select_query2;

  select_query1 and select_query2 are SELECT queries. The number and the order of the columns must be identical in both queries, and the data types of corresponding columns must be compatible. Duplicate rows are eliminated from the result unless ALL is specified.

Listing 9.1 lists the states where authors and publishers are located. By default, UNION removes duplicate rows from the result. See Figure 9.1 for the result.

Listing 9.2 is the same as Listing 9.1 except that it includes the ALL keyword, so all rows are included in the results, and duplicates aren’t removed. See Figure 9.2 for the result.

Listing 9.3 lists the names of all the authors and publishers. The AS clause in the first query names the column in the result. The ORDER BY clause uses a relative column position instead of a column name to sort the result. See Figure 9.3 for the result.

Listing 9.4 expands on Listing 9.3 and defines the extra column Type to identify which table each row came from. The WHERE conditions retrieve the authors and publishers from New York state only. See Figure 9.4 for the result.

Listing 9.5 adds a third query to Listing 9.4 to retrieve the titles of books published in New York state also. See Figure 9.5 for the result.

Listing 9.6 is similar to Listing 9.5 except that it lists the counts of each author, publisher, and book, instead of their names. See Figure 9.6 for the result.

In Listing 9.7, I revisit Listing 5.30 in “Evaluating Conditional Values with CASE” in Chapter 5. But instead of using CASE to change book prices and simulate if-then logic, I use multiple UNION queries. See Figure 9.7 for the result.

Tips for UNION

• UNION is commutative: A UNION B is the same as B UNION 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.

• Don’t use UNION where a compound condition will suffice:

  SELECT DISTINCT * FROM mytable
  WHERE col1 = 1 AND col2 = 2;

  usually is faster than

  SELECT * FROM mytable
  WHERE col1 = 1;
UNION
SELECT * FROM mytable
  WHERE col2 = 2;

• If you mix UNION and UNION ALL in a single statement, then use parentheses to specify order of evaluation. Take these two statements, for example:

  SELECT * FROM table1
UNION ALL
(SELECT * FROM table2
UNION
SELECT * FROM table3);

  and:

  (SELECT * FROM table1
UNION ALL
SELECT * FROM table2)
UNION
SELECT * FROM table3;

  The first statement eliminates duplicates in the union of table2 and table3 but doesn’t eliminate duplicates in the union of that result and table1. The second statement includes duplicates in the union of table1 and table2 but eliminates duplicates in the subsequent union with table3, so ALL has no effect on the final result of this statement.

• For UNION operations, the DBMS performs an internal sort to identify and remove duplicate rows; hence, the result of a UNION might be sorted even if you don’t specify an ORDER BY clause. UNION ALL doesn’t sort because it doesn’t need to remove duplicates. Sorting is computationally expensive—don’t use UNION when UNION ALL will suffice.

• In Microsoft Access and Microsoft SQL Server, use + to concatenate strings (see “Concatenating Strings with ||” in Chapter 5). To run Listings 9.3 through 9.5, change the concatenation expressions to:

  au_fname + ' ' + au_lname

  In MySQL, use CONCAT() to concatenate strings (see “Concatenating Strings with ||” in Chapter 5). To run Listings 9.3 through 9.5, change the concatenation expressions to:

  CONCAT(au_fname, ' ', au_lname)

  In older PostgreSQL versions, convert the floating-point numbers in Listing 9.7 to DECIMAL; see “Converting Data Types with CAST()” in Chapter 5. To run Listing 9.7, change new-price calculations to:

  price * CAST((1.10) AS DECIMAL)
price * CAST((1.20) AS DECIMAL)

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_query1
INTERSECT
select_query2;

  select_query1 and select_query2 are SELECT queries. The number and the order of the columns must be identical in both queries, and the data types of corresponding columns must be compatible. Duplicate rows are eliminated from the result.

Listing 9.8 uses INTERSECT to list the cities in which both an author and a publisher are located. See Figure 9.8 for the result.

Tips for INTERSECT

• INTERSECT is commutative: 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, then 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, then 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, then you can replicate it with an INNER JOIN or an EXISTS subquery. Each of the following statements is equivalent to Listing 9.8 (inner join):

  SELECT DISTINCT authors.city
  FROM authors
  INNER JOIN publishers
    ON authors.city = publishers.city;

  or (EXISTS subquery):

  SELECT DISTINCT city
  FROM authors
  WHERE EXISTS
    (SELECT *
       FROM publishers
       WHERE authors.city = publishers.city;)

• Microsoft Access and MySQL don’t support INTERSECT. To run Listing 9.8, use one of the equivalent queries given in the preceding tip.

Finding Different Rows with EXCEPT

An EXCEPT operation, also called a difference, combines the results of two queries into a single result that has the rows that belong to only the first query. To contrast INTERSECT and EXCEPT, A INTERSECT B contains rows from table A that are duplicated in table B, whereas A EXCEPT B contains rows from table A that aren’t duplicated in table B. Differences have the same restrictions as unions; see “Combining Rows with UNION” earlier in this chapter.

To find different rows:

• Type:

  select_query1
EXCEPT
select_query2;

  select_query1 and select_query2 are SELECT queries. The number and the order of the columns must be identical in both queries, and the data types of corresponding columns must be compatible. Duplicate rows are eliminated from the result.

Listing 9.9 uses EXCEPT to list the cities in which an author lives but a publisher isn’t located. See Figure 9.9 for the result.

Tips for EXCEPT

• Unlike UNION and INTERSECT, EXCEPT is not commutative: A EXCEPT B isn’t the same as B EXCEPT 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.

• Don’t use EXCEPT where a compound condition will suffice.

  SELECT * FROM mytable
  WHERE col1 = 1 AND NOT col2 = 2;

  usually is faster than

  SELECT * FROM mytable
  WHERE col1 = 1;
EXCEPT
SELECT * FROM mytable
  WHERE col2 = 2;

• If your DBMS doesn’t support EXCEPT, then you can replicate it with an outer join, a NOT EXISTS subquery, or a NOT IN subquery. Each of the following statements is equivalent to Listing 9.9 (outer join):

  SELECT DISTINCT a.city
  FROM authors a
  LEFT OUTER JOIN publishers p
    ON a.city = p.city
  WHERE p.city IS NULL;

  or (NOT EXISTS subquery):

  SELECT DISTINCT city
  FROM authors
  WHERE NOT EXISTS
    (SELECT *
       FROM publishers
       WHERE authors.city = publishers.city);

  or (NOT IN subquery):

  SELECT DISTINCT city
  FROM authors
  WHERE city NOT IN
    (SELECT city
       FROM publishers);

• Microsoft Access and MySQL don’t support EXCEPT. To run Listing 9.9, use one of the equivalent queries given in the preceding tip.

  In Oracle, the EXCEPT operator is called MINUS. To run Listing 9.9, type:

  SELECT city FROM authors
MINUS
SELECT city FROM publishers;