UNION, INTERSECT, EXCEPT

Sometimes you need to combine the results of several queries into one list, or compare them with each other. That is what set operations do.

The condition: matching columns

The combined queries must have the same number of columns with compatible types. The column names of the result come from the first query.

UNION and UNION ALL

Everyone at the university — students and teachers — in one list:

SELECT first_name, last_name, 'talaba' AS role FROM students
UNION ALL
SELECT first_name, last_name, 'ustoz' FROM teachers
ORDER BY last_name
LIMIT 10;

• UNION ALL adds everything, duplicates included (faster);
• UNION removes duplicates.

SELECT city FROM students
UNION
SELECT 'Toshkent';

INTERSECT — in both

Last names that appear among students and among teachers:

SELECT last_name FROM students
INTERSECT
SELECT last_name FROM teachers;

EXCEPT — in the first, not in the second

Last names found among students but not among teachers:

SELECT last_name FROM students
EXCEPT
SELECT last_name FROM teachers;

Remember

• UNION ALL adds, UNION adds without duplicates.
• INTERSECT is the overlap, EXCEPT is the difference.
• The queries must have the same number and types of columns.