Set Operators in SQL

The Set Theory Engine While Joins combine data horizontally by stitching columns together from different tables based on a matching relationship, Set Operators combine data vertically . They take the results of two or more independent SELECT queries and stack their data rows on top of each other into a single unified result set. HORIZONTAL JOINS (ON PK = FK) VERTICAL SET OPERATORS (UNION/EXCEPT) Table A Table B Query Result 1 ┌───────────┐ ┌───────────┐ ┌───────────┐ │ Column_1 │ │ Column_3 │ │ Column_1 │ └─────┬─────┘ └─────┬─────┘ └─────┬─────┘ └──────┬──────┘ ▼ ▼ Query Result 2 ┌─────────────────────────┐ ┌───────────┐ │ Column_1 │ Column_3 │ │ Column_1 │ └─────────────────────────┘ └───────────┘ 1. The Two Strict Structural Rules for Set Operations For a set operation to succeed, the components being combined must be Union Compatible . The database engine enforces two strict structural constraints at the compiler layer: Identical Column Count: Every SELECT statement in the set chain must project the exact same number of columns . Compatible Data Types: The columns must appear in the exact same order across all queries, and their data types must be compatible (e.g., you cannot stack a VARCHAR column on top of an INT column). 2. Combining Datasets: UNION vs. UNION ALL Both operators merge the rows from multiple queries vertically, but they handle duplicate data rows completely differently. A. UNION (Distinct Set Union: A ∪ B ) UNION merges the datasets and automatically removes all duplicate rows , returning only unique records. To do this, the database engine has to sort the entire combined dataset in system memory to find and strip out duplicates before returning the final result. SQL -- Objective: Compile a clean list of all unique IP addresses across both live networks SELECT assigned_ip FROM infrastructure_nodes WHERE is_active = TRUE UNION SELECT assigned_ip FROM secondary_nodes; B. UNION ALL (Complete Stack Union) UNION ALL takes the rows from all queries and stacks them together directly without checking for or removing duplicates . If a row appears in both datasets, it will appear multiple times in the final output. SQL -- Objective: Gather a raw stream of all logged IPs for capacity analysis SELECT assigned_ip FROM infrastructure_nodes UNION ALL SELECT assigned_ip FROM secondary_nodes; Performance Optimization Standard: Always prefer UNION ALL over standard UNION unless you explicitly need to remove duplicates. Because UNION ALL doesn't have to sort data in memory to strip out duplicates, it executes instantly with near-zero processing overhead, making it highly efficient for large production datasets. 3. Finding Overlaps: INTERSECT ( A ∩ B ) The INTERSECT operator compares the results of two queries and returns only the rows that exist in both datasets . If a row appears in the first query's result but not the second, it is completely excluded from the final output. SQL -- Objective: Identify IP addresses that are actively provisioned on both networks simultaneously SELECT assigned_ip FROM infrastructure_nodes INTERSECT SELECT assigned_ip FROM secondary_nodes; 4. Isolating Differences: EXCEPT / MINUS ( A − B ) The EXCEPT operator compares two datasets and returns rows from the first query that do not exist anywhere in the second query's results . It acts as a data subtraction mechanism, stripping the second dataset's footprint out of the first. SQL -- Objective: Find live primary IPs that haven't been mirrored over to the backup secondary network yet SELECT assigned_ip FROM infrastructure_nodes WHERE is_active = TRUE EXCEPT SELECT assigned_ip FROM secondary_nodes; Engine Differentiation Note: The standard SQL keyword for this operation is EXCEPT (used natively by PostgreSQL , Microsoft SQL Server , and SQLite ). However, Oracle Database handles this exact same set subtraction operation using the alternate keyword MINUS . SQL Set Operators Reference Matrix Set Operator Keyword Mathematical Operation Handle Duplicate Rows Processing Performance Profile UNION Distinct Union ( A ∪ B ) Automatically deletes duplicate entries across datasets. Moderate to Slow. Requires sorting the combined data in memory to locate and drop duplicates. UNION ALL Complete Stack ( A + B ) Retains all duplicate entries exactly as they appear. High Efficiency. Stacks data rows directly into the pipeline with zero sorting or calculation overhead. INTERSECT Intersection ( A ∩ B ) Removes duplicates; returns only rows found in both sets. Moderate. Requires checking and comparing both datasets to find matching records. EXCEPT / MINUS Subtraction ( A − B ) Removes duplicates; isolates unique rows belonging strictly to the first set. Moderate. Requires evaluating the left dataset against the right dataset to filter out overlaps.