A join combines rows from two tables using a condition, usually matching keys. INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every row from the left table and fills missing right columns with NULL, RIGHT JOIN does the reverse, FULL JOIN keeps both, and CROSS JOIN pairs every row with every row.
| Join | Matched rows | Unmatched left rows | Unmatched right rows |
|---|---|---|---|
| INNER JOIN | Yes | No | No |
| LEFT JOIN | Yes | Yes, right side NULL | No |
| RIGHT JOIN | Yes | No | Yes, left side NULL |
| FULL JOIN | Yes | Yes | Yes |
| CROSS JOIN | Every pair, no condition | n/a | n/a |
Updated
Venn diagrams are the usual way to teach joins, but they hide the part that actually bites: duplicated rows and NULLs. Below are two tiny tables, customers and orders, with a customer who never ordered and an order whose customer was deleted. Pick a join and the result is computed in your browser, so you can see exactly which rows appear and why.
| id | name | referred_by |
|---|---|---|
| 1 | Ana | NULL |
| 2 | Ben | 1 |
| 3 | Cleo | 2 |
| id | customer_id | total |
|---|---|---|
| 101 | 1 | 40 |
| 102 | 1 | 15 |
| 103 | 2 | 60 |
| 104 | 9 | 25 |
Paste this into psql or any Postgres SQL editor to recreate the sample tables, then run the queries from the demo.
Order 104 deliberately points at customer 9, which does not exist, so the outer joins have something to show.
CREATE TABLE customers (id int PRIMARY KEY, name text, referred_by int);
CREATE TABLE orders (id int PRIMARY KEY, customer_id int, total numeric);
INSERT INTO customers VALUES (1, 'Ana', NULL), (2, 'Ben', 1), (3, 'Cleo', 2);
INSERT INTO orders VALUES (101, 1, 40), (102, 1, 15), (103, 2, 60), (104, 9, 25);The LEFT JOIN keeps Cleo with NULL order columns, and the IS NULL filter keeps only those rows.
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;ON takes any condition, so it handles different column names and extra rules such as a date range. USING (customer_id) is shorthand when both tables name the column the same, and it outputs that column once instead of twice.
In PostgreSQL, FROM a, b is the same as a CROSS JOIN, and a WHERE clause turns it into an inner join. Explicit JOIN syntax is clearer and binds more tightly when you mix the two styles.
Conditions in ON are applied while rows are being matched, before any NULL padding. Conditions in WHERE run on the finished result. For inner joins the two give the same answer; for outer joins they can give very different ones.
INNER JOIN drops rows that have no match. LEFT JOIN keeps every row of the first table and puts NULL in the second table's columns when nothing matches. In the demo, Cleo appears in the LEFT JOIN with an empty order and is missing from the INNER JOIN.
Yes. In PostgreSQL the word INNER is optional and a bare JOIN is an inner join. The word OUTER is also optional, so LEFT JOIN and LEFT OUTER JOIN mean the same thing.
Each left row appears once per matching right row. A customer with three orders shows up three times. That is correct behaviour; aggregate with GROUP BY or join a pre-grouped subquery if you need one row per customer.
A table joined to itself under two aliases, used when rows point at other rows in the same table, such as employees and their managers or customers and who referred them.
PostgreSQL does, and that is the syntax shown here. MySQL has no FULL JOIN keyword; the usual workaround is a LEFT JOIN combined with a RIGHT JOIN using UNION.
Related: SQL formatter, Supabase vs Firebase, Supabase authentication, Express.js tutorial.
Start for free or see all free tools. We.Inc is free to start, with no card needed. AI building is on paid plans from $20 a month.
We.Inc is an AI-powered website builder you can resell under your own brand. Launch a branded client dashboard, bill on Stripe Connect, and deliver AI-generated websites in minutes. White-label plans start at $99 a month for 25 client sites, with a 7-day free trial and no per-site fees.