A set operator combines or compares the rows returned by two compatible SELECT queries, producing one result set. Use one when the information you need is split across queries—for example, names stored in customer and author tables—or when you want values shared by, or missing from, another result. section 10.1 section 10.2 section 10.6
SELECT <some column>, <another column> FROM <some table> WHERE <some condition> <set operator> SELECT <some column>, <another column> FROM <some table> WHERE <another condition>;
Place a set operator between complete SELECT queries. Each query must return the same number of columns, with compatible data types in matching positions. Output column names come from the first query, and ORDER BY belongs only at the end of the final query. section 10.1
Choose the operator by the result you want:
- UNION joins results and removes duplicate rows. Think of it as bringing two lists together into one list without repeated identical rows. section 10.2
- UNION ALL joins results exactly as read, including duplicates. It can resemble using OR for separate conditions, but it may perform differently. section 10.3
- INTERSECT returns values common to both query results. Unlike an INNER JOIN, it removes duplicates and can include matching null values. section 10.5
- EXCEPT returns values from the first query that do not appear in the second. Unlike a LEFT JOIN used for missing matches, it removes duplicates and can retain qualifying null values. section 10.6
Combine people from two tables
To make one alphabetized contact list, select FirstName and LastName from customer, UNION those rows with the same columns from author, then order the final result by LastName and FirstName. Add a literal such as 'customer' or 'author' as a third column when you need to preserve each row’s table of origin. Remember: adding that label can make otherwise identical name rows different rows, so UNION will no longer remove them as duplicates. section 10.2
Duplicate handling matters. UNION does extra work to remove duplicates and is generally slower than UNION ALL; however, a very large duplicate-heavy UNION ALL result may send more data. Consider both forms when a query performs worse than expected. section 10.3
Set-operator support varies by database product. MySQL did not support INTERSECT or EXCEPT before 8.0.31, while Oracle uses MINUS instead of EXCEPT. UNION ALL can also combine a left join and selected right-join rows to emulate a FULL OUTER JOIN where needed. section 10.4 section 10.6
