SQL Join Types Explained with One Small Dataset

SQL join types: predict the rows before writing the query

A join forms rows from combinations of input rows. The condition decides which pairs match; the join type decides which unmatched inputs survive. This PostgreSQL 16 lesson uses three people and two teams so every result can be checked by hand. It is the starting reference for join semantics; use multi-table joins and composite keys for larger query design and join interview exercises for practice.

A small fixture exposes the important cases

Ada and Ben both belong to team 10. Cara has no team ID. Team 20 has no people. People IDs and team IDs are unique within their own inputs. A non-NULL team_id can match one team here, while one team can match several people. Those cardinalities explain the output; a join does not automatically promise one result per person or per team.

Matching is a condition, not a set intersection

For an equality join, NULL = NULL is unknown rather than true, so two missing keys do not match each other. Other join conditions are possible, including ranges. A foreign key can constrain valid references, but a query can join inputs even when no foreign key was declared. Use the actual stable keys, not a name that merely looks unique.

INNER JOIN returns each matching pair

WITH people(id,name,team_id) AS (
 VALUES (1,'Ada',10),(2,'Ben',10),(3,'Cara',NULL)
), teams(id,name) AS (VALUES (10,'Data'),(20,'Support'))
SELECT p.id AS person_id,p.name,t.id AS team_id,t.name AS team_name
FROM people p JOIN teams t ON t.id=p.team_id ORDER BY p.id;

person_id | name | team_id | team_name
1 | Ada | 10 | Data
2 | Ben | 10 | Data

There are two result rows because team 10 matches two people. Cara and Support are absent because neither participates in a matching pair. Returning the team name twice is expected relationship data, not evidence that DISTINCT is required.

Outer joins preserve the chosen unmatched side

LEFT JOIN preserves every person. A missing match is represented by NULLs in the team columns. This is different from saying the right table itself contained a NULL team name: retain the matched team key to distinguish the two cases.

WITH people(id,name,team_id) AS (
 VALUES (1,'Ada',10),(2,'Ben',10),(3,'Cara',NULL)
), teams(id,name) AS (VALUES (10,'Data'),(20,'Support'))
SELECT p.id AS person_id,t.id AS team_id,t.name AS team_name
FROM people p LEFT JOIN teams t ON t.id=p.team_id ORDER BY p.id;

person_id | team_id | team_name
1 | 10 | Data
2 | 10 | Data
3 | NULL | NULL

To preserve every team instead, put teams on the preserved side. The following RIGHT JOIN keeps Support; it does not keep Cara, because people is the non-preserved side. Rewriting it as teams LEFT JOIN people with the same condition gives the same pairs.

WITH people(id,name,team_id) AS (
 VALUES (1,'Ada',10),(2,'Ben',10),(3,'Cara',NULL)
), teams(id,name) AS (VALUES (10,'Data'),(20,'Support'))
SELECT t.id AS team_id,t.name,p.id AS person_id
FROM people p RIGHT JOIN teams t ON t.id=p.team_id
ORDER BY t.id,p.id;

team_id | name | person_id
10 | Data | 1
10 | Data | 2
20 | Support | NULL

FULL OUTER JOIN preserves unmatched inputs from both sides and matching pairs once. PostgreSQL supports it directly; do not assume the syntax exists in every engine. The two NULL markers below refer to different missing sides.

WITH people(id,name,team_id) AS (
 VALUES (1,'Ada',10),(2,'Ben',10),(3,'Cara',NULL)
), teams(id,name) AS (VALUES (10,'Data'),(20,'Support'))
SELECT p.id AS person_id,t.id AS team_id
FROM people p FULL OUTER JOIN teams t ON t.id=p.team_id
ORDER BY p.id NULLS LAST,t.id;

person_id | team_id
1 | 10
2 | 10
3 | NULL
NULL | 20

CROSS JOIN creates every combination

WITH people(id,name,team_id) AS (
 VALUES (1,'Ada',10),(2,'Ben',10),(3,'Cara',NULL)
), teams(id,name) AS (VALUES (10,'Data'),(20,'Support'))
SELECT p.id AS person_id,t.id AS team_id
FROM people p CROSS JOIN teams t ORDER BY p.id,t.id;

person_id | team_id
1 | 10
1 | 20
2 | 10
2 | 20
3 | 10
3 | 20

Three times two produces six combinations. If either input is empty, the cross join is empty. This is useful for a deliberately bounded reporting grid, but it does not prove every combination occurred in the source. Before generating customer-by-product matrices, estimate the product of the input sizes and decide whether that output is useful.

A self-join is a table used twice, not another preservation rule

WITH employees(id,name,manager_id) AS (
 VALUES (1,'Ada',NULL),(2,'Ben',1),(3,'Cara',1)
)
SELECT e.id,e.name,m.name AS manager_name
FROM employees e LEFT JOIN employees m ON m.id=e.manager_id ORDER BY e.id;

id | name | manager_name
1 | Ada | NULL
2 | Ben | Ada
3 | Cara | Ada

The aliases describe employee and manager roles for the same input. LEFT JOIN retains the top-level employee. This finds only the direct manager; arbitrary-depth ancestry needs recursive logic. Naming something a self-join does not change INNER versus LEFT semantics.

Check filtering and row counts next

A final WHERE condition on a nullable right-side column can remove an unmatched LEFT JOIN row. Count a non-NULL matched key when counting matches, and choose the population before aggregating. Do not switch to INNER JOIN merely because someone claims it is always faster; that can change the answer. Inspect the plan after correctness is established.

FAQ

Does a LEFT JOIN keep the same row count? At least the left population survives before later filters, but multiple matches can increase the count. Does DISTINCT repair any join? No; it can hide legitimate detail without repairing an incorrect key. What determines result order? The final ORDER BY, not join type or physical input order. See PostgreSQL table expressions for the underlying rules.