Reference
SQL JOIN Types
How to combine rows from two tables, and which rows each join keeps.
A join matches rows from two tables using an ON condition, usually a key in one table equals a key in the other. The join type decides what happens to rows that have no match.
Shape
select o.order_id, c.first_name, c.last_name
from orders as o
join customers as c on c.customer_id = o.customer_idThe four common joins
| Join | Keeps |
|---|---|
INNER JOIN | Only rows that match in both tables. |
LEFT JOIN | All left rows; unmatched right columns are NULL. |
RIGHT JOIN | All right rows; unmatched left columns are NULL. |
FULL JOIN | All rows from both sides; non-matches filled with NULL. |
CROSS JOIN | Every left row paired with every right row (no ON). |
JOIN on its own means INNER JOIN. The wordOUTER is optional: LEFT JOIN andLEFT OUTER JOIN are identical.
Finding rows with no match (anti-join)
A LEFT JOIN plus an IS NULL filter is the standard way to find rows that have no partner, such as products that were never ordered.
Anti-join
select p.product_name
from products as p
left join order_items as oi on oi.product_id = p.product_id
where oi.product_id is nullWatch out
Joining a one-to-many relationship multiplies rows: one order with three items becomes three rows. If you then SUM the order total you will over-count. Aggregate the "many" side first, or count withCOUNT(DISTINCT ...).