{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"set-operations","title":"UNION, INTERSECT and EXCEPT","url":"https://addaly.com/learn/data-and-sql/set-operations","position":19,"of":66,"minutes":7,"answer":"UNION removes rows that are identical in every column, so it silently deletes genuine repeats; UNION ALL keeps everything and is the one to reach for by default.","keyIdea":"UNION removes rows that are identical in every column, so it silently deletes genuine repeats; UNION ALL keeps everything and is the one to reach for by default."},"body":{"format":"markdown","text":"## Stacking results instead of widening them\n\nA join adds columns. A set operation adds rows. When two queries produce the same shape of result and you want them in one list, you stack them:\n\n```sql\nSELECT id, full_name, 'student' AS kind FROM students\nUNION ALL\nSELECT id, full_name, 'teacher' AS kind FROM teachers;\n```\n\nThe rules are strict and they are what make this predictable. Both sides need the same number of columns, in the same order, with compatible types. Column names come from the first query; the second query's names are ignored entirely, which surprises people who carefully alias them.\n\n## UNION versus UNION ALL, which is not a style choice\n\n`UNION ALL` concatenates. `UNION` concatenates **and removes duplicate rows**, which requires sorting or hashing the entire result.\n\nTwo consequences.\n\nThe performance one: on a million rows, `UNION` can be several times slower than `UNION ALL` for no benefit if the two sides cannot overlap. Students and teachers are different tables; there is nothing to deduplicate. Use `UNION ALL` unless you specifically want deduplication.\n\nThe correctness one, which matters more: `UNION` silently deletes genuine data. Two payments of ₹500 from the same student on the same day, identical in every selected column, become one payment. Your total is now short by ₹500 and nothing indicates it. If the rows carry an id, include it and the problem disappears; if they do not, `UNION` is a data-destroying operation dressed as tidiness.\n\n## INTERSECT and EXCEPT\n\n`INTERSECT` returns rows appearing in both results. `EXCEPT` (called `MINUS` in Oracle) returns rows in the first that are not in the second. Both deduplicate by default, with `ALL` variants that do not.\n\nTheir best use is comparing two datasets that should be identical:\n\n```sql\n-- rows the ledger has and the bank does not\nSELECT reference, amount FROM ledger\nEXCEPT\nSELECT reference, amount FROM bank;\n\n-- and the other direction, which is a different question\nSELECT reference, amount FROM bank\nEXCEPT\nSELECT reference, amount FROM ledger;\n```\n\nRunning both directions is the point. `EXCEPT` is not symmetric, and \"what did we miss\" and \"what did they add\" are different findings. This pair of queries is the fastest reconciliation tool in SQL, and it also works beautifully for verifying a migration: run the same aggregate against the old and new systems, `EXCEPT` in both directions, and an empty result on both is a proof rather than an impression.\n\n## When to prefer a set operation to a join\n\n`EXCEPT` and `NOT EXISTS` can answer the same question:\n\n```sql\nSELECT id FROM students\nEXCEPT\nSELECT student_id FROM payments;\n\nSELECT s.id FROM students s\nWHERE NOT EXISTS (SELECT 1 FROM payments p WHERE p.student_id = s.id);\n```\n\nThe `EXCEPT` version is shorter and reads well when you want a single column. The `NOT EXISTS` version lets you select other columns from `students`, which the `EXCEPT` version cannot without adding them to both sides. Both handle NULLs sensibly, unlike `NOT IN`. Choose by what you need in the output.\n\n## Ordering and parentheses\n\n`ORDER BY` applies to the whole combined result and belongs at the very end, once:\n\n```sql\nSELECT ... FROM a\nUNION ALL\nSELECT ... FROM b\nORDER BY 2 DESC;\n```\n\nAn `ORDER BY` inside one branch is either an error or ignored, depending on the engine. To sort or limit one branch, wrap it in a subquery or a CTE.\n\nPrecedence is worth knowing: `INTERSECT` binds tighter than `UNION` and `EXCEPT`, exactly as multiplication binds tighter than addition. Mixing them without brackets is how you get a result nobody predicted. Put the brackets in.\n\n## A practical use: the union of similar tables\n\nData often arrives split by month or by region: `sales_2024`, `sales_2025`, or one table per country. A `UNION ALL` view stitches them into one queryable surface:\n\n```sql\nCREATE VIEW sales AS\n  SELECT *, 2024 AS yr FROM sales_2024\n  UNION ALL\n  SELECT *, 2025 AS yr FROM sales_2025;\n```\n\nThis works, and it is worth knowing that most databases now offer native table partitioning that does the same thing while letting the planner skip irrelevant partitions entirely. The manual union is the portable version and the right first step; if it becomes slow, partitioning is what you are reaching for next."},"check":{"question":"You stack two payment exports with `UNION` and the total comes out slightly lower than the two files' totals added together. What happened?","options":[{"text":"The two exports have their columns in a different order, so some amounts were added into the wrong column and silently left out of the final total.","correct":false,"tempting":"A column-order mismatch with compatible types would corrupt the data visibly or raise a type error, not shave a small amount off the total."},{"text":"`UNION` removed rows identical across every selected column, which included genuine repeat payments of the same amount on the same day.","correct":true},{"text":"`UNION` cannot combine results from different sources, so one export was silently truncated.","correct":false,"tempting":"Invents a limitation; a set operation cares about column count and types, not where the rows came from."},{"text":"The sum skipped rows where any column was NULL, because set operations exclude NULL rows.","correct":false,"tempting":"Set operations keep NULL rows and treat two NULLs as duplicates of each other; they do not drop them."}]},"prev":{"slug":"self-joins","title":"Joining a table to itself","url":"https://addaly.com/learn/data-and-sql/self-joins"},"next":{"slug":"subqueries-and-exists","title":"Subqueries: a query inside a query","url":"https://addaly.com/learn/data-and-sql/subqueries-and-exists"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'UNION, INTERSECT and EXCEPT', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/set-operations (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"UNION, INTERSECT and EXCEPT","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":2,"slug":"putting-tables-together","title":"Putting tables together"},"answer":null,"keyIdea":"UNION removes rows that are identical in every column, so it silently deletes genuine repeats; UNION ALL keeps everything and is the one to reach for by default.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"stacking-results-instead-of-widening-them","runs":[{"text":"Stacking results instead of widening them"}]},{"t":"p","runs":[{"text":"A join adds columns. A set operation adds rows. When two queries produce the same shape of result and you want them in one list, you stack them:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT id, full_name, 'student' AS kind FROM students\nUNION ALL\nSELECT id, full_name, 'teacher' AS kind FROM teachers;"},{"t":"p","runs":[{"text":"The rules are strict and they are what make this predictable. Both sides need the same number of columns, in the same order, with compatible types. Column names come from the first query; the second query’s names are ignored entirely, which surprises people who carefully alias them."}]},{"t":"h2","id":"union-versus-union-all-which-is-not-a-style-choice","runs":[{"text":"UNION versus UNION ALL, which is not a style choice"}]},{"t":"p","runs":[{"text":"UNION ALL","code":true},{"text":" concatenates. "},{"text":"UNION","code":true},{"text":" concatenates "},{"text":"and removes duplicate rows","b":true},{"text":", which requires sorting or hashing the entire result."}]},{"t":"p","runs":[{"text":"Two consequences."}]},{"t":"p","runs":[{"text":"The performance one: on a million rows, "},{"text":"UNION","code":true},{"text":" can be several times slower than "},{"text":"UNION ALL","code":true},{"text":" for no benefit if the two sides cannot overlap. Students and teachers are different tables; there is nothing to deduplicate. Use "},{"text":"UNION ALL","code":true},{"text":" unless you specifically want deduplication."}]},{"t":"p","runs":[{"text":"The correctness one, which matters more: "},{"text":"UNION","code":true},{"text":" silently deletes genuine data. Two payments of ₹500 from the same student on the same day, identical in every selected column, become one payment. Your total is now short by ₹500 and nothing indicates it. If the rows carry an id, include it and the problem disappears; if they do not, "},{"text":"UNION","code":true},{"text":" is a data-destroying operation dressed as tidiness."}]},{"t":"h2","id":"intersect-and-except","runs":[{"text":"INTERSECT and EXCEPT"}]},{"t":"p","runs":[{"text":"INTERSECT","code":true},{"text":" returns rows appearing in both results. "},{"text":"EXCEPT","code":true},{"text":" (called "},{"text":"MINUS","code":true},{"text":" in Oracle) returns rows in the first that are not in the second. Both deduplicate by default, with "},{"text":"ALL","code":true},{"text":" variants that do not."}]},{"t":"p","runs":[{"text":"Their best use is comparing two datasets that should be identical:"}]},{"t":"code","lang":"sql","label":"SQL","text":"-- rows the ledger has and the bank does not\nSELECT reference, amount FROM ledger\nEXCEPT\nSELECT reference, amount FROM bank;\n\n-- and the other direction, which is a different question\nSELECT reference, amount FROM bank\nEXCEPT\nSELECT reference, amount FROM ledger;"},{"t":"p","runs":[{"text":"Running both directions is the point. "},{"text":"EXCEPT","code":true},{"text":" is not symmetric, and “what did we miss” and “what did they add” are different findings. This pair of queries is the fastest reconciliation tool in SQL, and it also works beautifully for verifying a migration: run the same aggregate against the old and new systems, "},{"text":"EXCEPT","code":true},{"text":" in both directions, and an empty result on both is a proof rather than an impression."}]},{"t":"h2","id":"when-to-prefer-a-set-operation-to-a-join","runs":[{"text":"When to prefer a set operation to a join"}]},{"t":"p","runs":[{"text":"EXCEPT","code":true},{"text":" and "},{"text":"NOT EXISTS","code":true},{"text":" can answer the same question:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT id FROM students\nEXCEPT\nSELECT student_id FROM payments;\n\nSELECT s.id FROM students s\nWHERE NOT EXISTS (SELECT 1 FROM payments p WHERE p.student_id = s.id);"},{"t":"p","runs":[{"text":"The "},{"text":"EXCEPT","code":true},{"text":" version is shorter and reads well when you want a single column. The "},{"text":"NOT EXISTS","code":true},{"text":" version lets you select other columns from "},{"text":"students","code":true},{"text":", which the "},{"text":"EXCEPT","code":true},{"text":" version cannot without adding them to both sides. Both handle NULLs sensibly, unlike "},{"text":"NOT IN","code":true},{"text":". Choose by what you need in the output."}]},{"t":"h2","id":"ordering-and-parentheses","runs":[{"text":"Ordering and parentheses"}]},{"t":"p","runs":[{"text":"ORDER BY","code":true},{"text":" applies to the whole combined result and belongs at the very end, once:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT ... FROM a\nUNION ALL\nSELECT ... FROM b\nORDER BY 2 DESC;"},{"t":"p","runs":[{"text":"An "},{"text":"ORDER BY","code":true},{"text":" inside one branch is either an error or ignored, depending on the engine. To sort or limit one branch, wrap it in a subquery or a CTE."}]},{"t":"p","runs":[{"text":"Precedence is worth knowing: "},{"text":"INTERSECT","code":true},{"text":" binds tighter than "},{"text":"UNION","code":true},{"text":" and "},{"text":"EXCEPT","code":true},{"text":", exactly as multiplication binds tighter than addition. Mixing them without brackets is how you get a result nobody predicted. Put the brackets in."}]},{"t":"h2","id":"a-practical-use-the-union-of-similar-tables","runs":[{"text":"A practical use: the union of similar tables"}]},{"t":"p","runs":[{"text":"Data often arrives split by month or by region: "},{"text":"sales_2024","code":true},{"text":", "},{"text":"sales_2025","code":true},{"text":", or one table per country. A "},{"text":"UNION ALL","code":true},{"text":" view stitches them into one queryable surface:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE VIEW sales AS\n  SELECT *, 2024 AS yr FROM sales_2024\n  UNION ALL\n  SELECT *, 2025 AS yr FROM sales_2025;"},{"t":"p","runs":[{"text":"This works, and it is worth knowing that most databases now offer native table partitioning that does the same thing while letting the planner skip irrelevant partitions entirely. The manual union is the portable version and the right first step; if it becomes slow, partitioning is what you are reaching for next."}]}],"check":{"question":[{"text":"You stack two payment exports with "},{"text":"UNION","code":true},{"text":" and the total comes out slightly lower than the two files’ totals added together. What happened?"}],"options":[{"text":[{"text":"The two exports have their columns in a different order, so some amounts were added into the wrong column and silently left out of the final total."}],"correct":false,"tempting":[{"text":"A column-order mismatch with compatible types would corrupt the data visibly or raise a type error, not shave a small amount off the total."}]},{"text":[{"text":"UNION","code":true},{"text":" removed rows identical across every selected column, which included genuine repeat payments of the same amount on the same day."}],"correct":true,"tempting":null},{"text":[{"text":"UNION","code":true},{"text":" cannot combine results from different sources, so one export was silently truncated."}],"correct":false,"tempting":[{"text":"Invents a limitation; a set operation cares about column count and types, not where the rows came from."}]},{"text":[{"text":"The sum skipped rows where any column was NULL, because set operations exclude NULL rows."}],"correct":false,"tempting":[{"text":"Set operations keep NULL rows and treat two NULLs as duplicates of each other; they do not drop them."}]}],"order":[3,2,1,0],"why":null},"prev":{"slug":"self-joins","title":"Joining a table to itself","minutes":8},"next":{"slug":"subqueries-and-exists","title":"Subqueries: a query inside a query","minutes":8}}}