If you are just starting out with SQL, you have probably already learned how to write a SELECT query, filter data with WHERE, and maybe even join two tables together. But what happens when you need to combine results from two completely different queries? That is exactly where SET operators come in.
Think of SET operators as tools that let you stack or compare the results of two SELECT queries. Instead of joining tables side by side like a JOIN does, SET operators combine query results on top of each other, row by row.
There are four SET operators in SQL:
• UNION — combines results and removes duplicates
• UNION ALL — combines results and keeps duplicates
• INTERSECT — returns all the rows that appear in both queries
• EXCEPT — returns results from the first query that do not appear in the second query
NB: For intersect and except to work, both select statements must have the same number of columns and columns must have compatible data types. If you try to combine a text column with a number column, PostgreSQL will throw an error.
Let us look at examples for more understanding;
UNION
Union combines results Without Duplicates.
I had two tables; one with student data and another with patients data. My task was to show a combined list of all unique cities from the students table and the patients table, ordered alphabetically. This is where UNION came in. See syntax below to solve the query;
SELECT city FROM nairobi_academy.students
UNION
SELECT city FROM city_hospital.patients
ORDER BY city ASC;
What UNION does here is take all the cities from students and patients table and combine them into one list, then remove any city that appears more than once. So even if Nairobi appears in both tables, it only shows up once in the final result.
UNION ALL
Union All combines results and Keeps everything, including duplicates.
Sometimes you do not want duplicates removed. Maybe you want to see every single name from multiple tables together, with a label showing where each name came from. That is exactly what UNION ALL does.
SELECT first_name AS name, 'Student' AS source
FROM nairobi_academy.students
UNION ALL
SELECT full_name AS name, 'Patient' AS source
FROM city_hospital.patients;
This query gives me one long list with every student name and every patient name. The second column called source tells us whether each name came from the students table or the patients table.
INTERSECT
Intersect finds the answer to the question, what do these two queries have in common?
In my assignment, I used it to find cities that appear in both the students table and the patients table, meaning cities where both students and patients live.
SELECT city FROM nairobi_academy.students
INTERSECT
SELECT city FROM city_hospital.patients
Only cities that exist in both results are returned. If a city appears in students but not in patients, it is excluded.
EXCEPT
Except helps find what is missing. It returns rows from the first query that do not appear in the second query. This is useful when you want to find missing records.
For example, if I wanted to find cities where students live but no patients are from, I would write;
SELECT city FROM nairobi_academy.students
EXCEPT
SELECT city FROM city_hospital.patients;
This returns only the cities unique to the students table. Any city that also appears in the patients table gets filtered out.
Parting shot!
SET operators took me a while to fully grasp, mostly because I kept mixing up UNION with JOIN. But once I started thinking of them as tools for stacking query results rather than connecting tables, everything clicked. If you are a beginner just getting into SQL, my advice is to practice each operator separately before combining them. Write a simple UNION first, check the result, then move to INTERSECT and EXCEPT. Take it one step at a time and do not be discouraged by the errors, they are part of the learning process. Keep going, it gets better!
Top comments (0)