10 Combining queries with set operators

This chapter expands from single SELECT queries to composing queries that combine multiple result sets into one using set operators. It explains the shared syntax pattern and lays out key rules: each SELECT must project the same number of columns in the same order with compatible data types; the column names used for the final result come from the first SELECT; and any ORDER BY can appear only once at the end and can reference only columns included in the combined projection. These operators let you evaluate separate conditions or even unrelated tables side by side when joins aren’t appropriate.

UNION merges results while removing duplicate rows, making it useful for consolidating lists (for example, pulling names from customer and author) and for reconciling disparate filters. UNION ALL returns all rows, including duplicates, which can mirror the behavior of OR conditions across multiple predicates or sources. The chapter discusses practical patterns such as adding a literal column to indicate a row’s origin, the need to sort only by selected columns after a set operator, and performance trade-offs: UNION often costs more due to de-duplication, while UNION ALL may be faster but can return larger result sets. It also cautions against mixing UNION and UNION ALL within the same expression, as results can be surprising or unsupported.

Beyond UNION, the chapter shows how to emulate a FULL OUTER JOIN in systems without native support (such as MySQL) by combining a LEFT JOIN and a RIGHT JOIN with UNION ALL and filtering one side on NULL to avoid duplicate matches. It introduces INTERSECT (common rows across queries) and EXCEPT (rows in the first query absent in the second), both of which remove duplicates and can include NULLs that align with their set semantics; MySQL supports them starting in 8.0.31, and Oracle uses MINUS in place of EXCEPT. Together, these operators provide flexible ways to combine, compare, and contrast result sets when joins alone aren’t sufficient, especially when you need to control duplicates and include or exclude NULLs intentionally.

A portion (8 of the 31 rows) of the results of first and last names from the customer and author tables

shows a portion (8 of the 31 rows) of the results of first and last names from the customer and author tables, as well as a third column indicating the table where the rows are from.

The results for the full names of customers whose last name starts with D and authors whose last name starts with C, ordered by last name and first name

shows the results of first names from the customer table whose last names start with D, combined with a UNION with the results of first names from the author table whose first names start with C. The two rows for “Chris” are represented with a single row since UNION has removed the duplicate from the result set.

shows the results of first names from the customer table whose last names start with D, combined with a UNION ALL with the results of first names from the author table whose first names start with C.

A Venn diagram of the values included in a query of the orderheader table with a full outer join of the promotion table

A Venn diagram of the values included in a query of the orderheader table with a left outer join of the promotion table

A Venn diagram of the values included only in a query of the promotion table, with a right outer join of the orderheader table

A Venn diagram of the values included only in a query of the promotion table, with a left outer join of the orderheader table

1. Since there is no column name supplied for the last column in the first SELECT statement, the literal value “customer” is used for the final column name in the result set.
2. The results will not be the same. The first query using OR for filtering will return two rows—one for each match. The second query with the UNION will return only one row because UNION removes duplicates from the results.
3. The first query returns three rows, while the second query returns one. At first glance, this may seem confusing since the second query simply reverses the order of the SELECT statements.
The answer lies in precedence, which indicates the order in which the SELECT statements are presented. The first UNION or UNION ALL is applied first, and then the second one is applied next. Remember, UNION eliminates duplicates, and UNION ALL does not.
In the first query, the first two SELECT statements each return one row with the value “Daly”, and since the UNION eliminates duplicates, the result is one row—so far. But the third SELECT statement returns two rows, which when evaluated with a UNION ALL, creates a result set of three rows—one row from the first two SELECT statements and two rows from the last SELECT statement.
In the second query, we combine the first SELECT statement, which returns two rows, with the second SELECT statement, which returns one row, and we do this with a UNION ALL. If we only executed this part of the query, we would get three rows returned. But we then combine another SELECT statement that returns one row with a UNION, which will then remove all the duplicates from our result set. This is why the second query returns only one row.
If this seems more than a little confusing, don’t worry. As long as you avoid combining UNION and UNION ALL in the same query, you’ll never have to worry about this headache.

FAQ