Lesson 11 of 12 · 22 min
Set operations: UNION, INTERSECT, EXCEPT
By the end of this lesson you can combine query results with UNION, UNION ALL, INTERSECT and EXCEPT, follow their column rules, choose between UNION and UNION ALL deliberately, and use EXCEPT to find what one list has and the other lacks.
Joins combine tables side by side: more columns per row. Set operations combine query results on top of each other: rows from one result and rows from another, in the same columns. There are four:
| Operator | Keeps | Duplicates |
|---|---|---|
UNION ALL | every row of both results | kept |
UNION | every distinct row of either result | removed |
INTERSECT | distinct rows present in both | removed |
EXCEPT | distinct rows of the first that are not in the second | removed |
Play with the four operators on two small lists before writing them yourself.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons