Introduction
CROSS JOIN produces the Cartesian product of two tables: every row from the first table paired with every row from the second, with no join condition at all. Set operations — UNION, UNION ALL, INTERSECT, and EXCEPT (called MINUS in Oracle) — instead combine the row sets returned by two separate SELECT statements vertically, based on column-position compatibility rather than a join key.
Cricket analogy: CROSS JOIN is like pairing every batsman with every possible batting position to generate all theoretical lineups, no matching condition needed; UNION and friends instead stack two separate scorecards vertically, like combining this innings' and last innings' batting list into one column of names.
Syntax
-- CROSS JOIN: every customer paired with every possible discount tier
SELECT c.customer_name, t.tier_name
FROM customers AS c
CROSS JOIN discount_tiers AS t;
-- Set operations combine two SELECTs with the same column count/types
SELECT customer_id FROM customers
UNION
SELECT customer_id FROM orders;
SELECT customer_id FROM customers
UNION ALL
SELECT customer_id FROM orders;
SELECT customer_id FROM customers
INTERSECT
SELECT customer_id FROM orders;
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;Explanation
CROSS JOIN has no ON clause; if customers has 3 rows and discount_tiers has 4 rows, the result has 12 rows (3 x 4). It is useful for generating combinations, such as pairing every product with every size/color option. Set operations require both SELECTs to return the same number of columns with compatible data types, and the result's column names come from the first SELECT. UNION removes duplicate rows across the combined result (an implicit DISTINCT), while UNION ALL keeps every row including duplicates and is faster since it skips deduplication. INTERSECT returns only rows present in both result sets, and EXCEPT (MINUS in Oracle) returns rows present in the first result set but not the second; both INTERSECT and EXCEPT are dedup by default. Note that customer_id here plays different semantic roles in each table, so this pairing is illustrative of syntax, not necessarily meaningful business logic.
Cricket analogy: With 3 opening batsmen and 4 possible bowling attacks, CROSS JOIN generates all 12 pairings for matchup analysis, no ON clause needed; UNION merges two innings' scorer lists and removes duplicate names, UNION ALL keeps every mention including repeats and is faster, INTERSECT finds scorers common to both innings, and EXCEPT finds scorers in the first innings but not the second.
Example
-- customers: customer_id 1, 2, 3
-- orders: customer_id values 1, 1, 2, 9 (across 4 order rows)
SELECT customer_id FROM customers -- {1, 2, 3}
UNION
SELECT customer_id FROM orders; -- {1, 1, 2, 9}
SELECT customer_id FROM customers
UNION ALL
SELECT customer_id FROM orders;
SELECT customer_id FROM customers
INTERSECT
SELECT customer_id FROM orders;
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;Output
UNION returns the distinct combined set: 1, 2, 3, 9 (4 rows, duplicates like the repeated 1 collapsed). UNION ALL returns all 7 rows with no deduplication: 1, 2, 3, 1, 1, 2, 9. INTERSECT returns values present in both sets: 1, 2 (2 rows). EXCEPT returns values in customers but not in orders: 3 (1 row) — note EXCEPT is order-sensitive, so swapping the operands would instead return 9. A CROSS JOIN of 3 customers and 4 discount_tiers rows would return 12 rows, one for every possible pairing.
Cricket analogy: Merging two scorer lists with UNION collapses repeated names down to 4 distinct scorers, while UNION ALL keeps all 7 mentions including repeats; INTERSECT shows the 2 scorers who appear on both lists, EXCEPT shows the 1 scorer unique to the first list only, and swapping the lists would change that answer; a 3x4 CROSS JOIN of openers and bowlers would yield 12 pairings.
Key Takeaways
- CROSS JOIN has no join condition and returns rows_left x rows_right, the full Cartesian product.
- UNION deduplicates combined rows; UNION ALL keeps all rows including duplicates and is faster.
- INTERSECT returns rows common to both SELECTs; EXCEPT (MINUS in Oracle) returns rows in the first SELECT but not the second.
- All set operations require the same number of columns with compatible types in both SELECTs; result column names come from the first SELECT.
- EXCEPT and INTERSECT are dedup by default and are order-sensitive for EXCEPT (A EXCEPT B differs from B EXCEPT A).
Practice what you learned
1. How many rows does CROSS JOIN produce for tables with 3 and 4 rows respectively?
2. What is the key difference between UNION and UNION ALL?
3. What does INTERSECT return?
4. What is required for two SELECT statements to be combined with a set operation?
Was this page helpful?
You May Also Like
INNER JOIN
Combine rows from two tables where the join condition matches in both, discarding unmatched rows.
FULL OUTER JOIN
Combine LEFT and RIGHT JOIN behavior, keeping all rows from both tables and NULL-padding unmatched sides.
SQL vs NoSQL
A balanced comparison of relational and NoSQL databases, covering when each model fits and the major NoSQL categories.