What are the different types of SQL JOIN?
SQL joins combine rows from two tables based on a condition. An INNER JOIN keeps only matching rows. A LEFT (OUTER) JOIN keeps every row of the left table, with NULLs where the right table has no match; a RIGHT JOIN is the mirror image. A FULL OUTER JOIN keeps all rows from both sides. A CROSS JOIN returns every combination (Cartesian product). A self-join joins a table to itself. Semi-joins (EXISTS) and anti-joins (NOT EXISTS) filter by the presence or absence of matches, and PostgreSQL’s LATERAL lets the right side reference the left row.
Read full answerHide full answer
Applies toPostgreSQL 16 (standard SQL)
Join Types
| Join | Rows returned | ML use |
|---|---|---|
| INNER | Matches only | Facts with valid dimensions |
| LEFT | All left rows + matches | Keep every training example, add optional features |
| RIGHT | All right rows + matches | Rare; rewrite as LEFT |
| FULL OUTER | All rows from both | Reconciling two sources |
| CROSS | Every pair | Entity × date grids, candidate generation |
| Self | Table with itself | Pairs of related events, graph edges |
Semi (EXISTS) |
Left rows with a match, no duplicates | Filter to active users |
Anti (NOT EXISTS) |
Left rows without a match | Users who never converted |
Code Example
WITH a(id, a_val) AS (VALUES (1, 'a1'), (2, 'a2'), (3, 'a3')),
b(id, b_val) AS (VALUES (2, 'b2'), (3, 'b3'), (4, 'b4'))
SELECT 'inner' AS join_type, count(*) AS rows FROM a JOIN b USING (id)
UNION ALL
SELECT 'left', count(*) FROM a LEFT JOIN b USING (id)
UNION ALL
SELECT 'right', count(*) FROM a RIGHT JOIN b USING (id)
UNION ALL
SELECT 'full', count(*) FROM a FULL JOIN b USING (id)
UNION ALL
SELECT 'cross', count(*) FROM a CROSS JOIN b
UNION ALL
SELECT 'anti (a not in b)', count(*) FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id);
-- Expected:
-- inner | 2
-- left | 3
-- right | 3
-- full | 4
-- cross | 9
-- anti (a not in b) | 1
WITH a(id, a_val) AS (VALUES (1, 'a1'), (2, 'a2'), (3, 'a3')),
b(id, b_val) AS (VALUES (2, 'b2'), (3, 'b3'), (4, 'b4'))
SELECT id, a_val, b_val FROM a FULL JOIN b USING (id) ORDER BY id;
-- Expected:
-- 1 | a1 | NULL
-- 2 | a2 | b2
-- 3 | a3 | b3
-- 4 | NULL | b4
Common Pitfalls
-
Conditions on the right table in
WHEREafter aLEFT JOIN(turns it into an inner join); put them inON. -
Accidental cross joins from missing join conditions.
-
Duplicate keys multiplying rows.