Study Guide: SQL Set Operators
SQL set operators are powerful tools that allow you to combine results from multiple queries into a
single unified result set. This guide covers the three main set operators—UNION, INTERSECT, and
MINUS/EXCEPT—explaining their functions, requirements, and practical applications in both
everyday scenarios and business contexts.
Key Concepts
SQL Set Operators: Commands that combine the results of two or more SELECT queries into a
single result set. They require both queries to have the same number of columns and compatible
data types.
UNION: Combines the results of two queries and automatically removes duplicate rows from the
final result set. Use this when you need a distinct list of combined records.
UNION ALL: Combines the results of two queries but retains all rows, including duplicates. This
is faster than UNION since it skips the duplicate-checking process.
INTERSECT: Returns only the rows that appear in the output of both queries. Think of it as
finding the overlap or common ground between two data sets.
MINUS / EXCEPT: Returns rows from the first query that do not appear in the second query.
MINUS is used in Oracle/MySQL, while EXCEPT is used in SQL Server, PostgreSQL, and
SQLite.
Column Compatibility Requirement: A fundamental rule stating that for set operators to work,
both queries must return the same number of columns, and those columns must have compatible
(matching or convertible) data types.
Main Takeaways
* UNION combines data and removes duplicates, making it ideal for consolidated reporting from
multiple sources like merging current and archived customer tables.
* UNION ALL is similar to UNION but keeps duplicates, which can be useful when you need a
complete count of all records regardless of repetition.
* INTERSECT finds common records between two datasets, perfect for identifying customers
who engage across multiple channels (omnichannel analysis).
* MINUS/EXCEPT identifies records unique to the first query by subtracting matches from the
second query, useful for finding inactive leads or pending tasks.
* The syntax for subtraction operators differs by database platform: Oracle and MySQL use
MINUS, while SQL Server, PostgreSQL, and SQLite use EXCEPT.
* All set operators require queries to have matching column counts and compatible data types to
function correctly.
Important Facts
* Set operators can only work when both queries have the same number of columns with
compatible data types.
* UNION automatically removes duplicate rows; UNION ALL preserves all rows including
duplicates.
* INTERSECT is the logical equivalent of finding the overlap between two sets in mathematics.
* The MINUS keyword is specific to Oracle and MySQL databases.
* The EXCEPT keyword is used in SQL Server, PostgreSQL, and SQLite as the equivalent of
MINUS.
* Set operators are commonly used in business scenarios like consolidated reporting,
multi-channel customer analysis, and data reconciliation.
Summary
SQL set operators provide essential functionality for combining and comparing data from multiple
queries. The three primary operators each serve distinct purposes: UNION (and UNION ALL) merges
results from two queries, with UNION removing duplicates for clean consolidated reports—ideal for
combining data from different regions or legacy systems. INTERSECT identifies records that exist in
both query results, making it valuable for finding commonalities such as customers who shop both
online and in-store. MINUS (Oracle/MySQL) or EXCEPT (SQL Server/PostgreSQL/SQLite) returns
records from the first query that don't appear in the second, which is particularly useful for data
reconciliation tasks like identifying prospects who haven't converted to customers. The critical
prerequisite for all set operators is that both queries must return the same number of columns with
compatible data types. Understanding these operators enables analysts and developers to efficiently
combine, compare, and filter data across multiple tables or data sources within a single query
operation.