Syntax Example
SELECT order_items.id, orders.order_date, order_items.price
FROM order_items
LEFT OUTER JOIN orders
ON order_items.order_id = orders.id;
Joins combine rows from two or more tables based on a related column between them.
Visualizing Joins
SELECT * FROM `A` INNER JOIN `B`
┌────────┐
│ A ┌───┼────┐
│ │ ∩ │ │
└────┼───┘ B │
└────────┘
| Join Type | Included Data | Description |
|---|---|---|
INNER JOIN |
∩ |
Returns records that have matching values in both tables |
LEFT OUTER JOIN |
A + ∩ |
Returns all records from table A, and matched records from table B |
RIGHT OUTER JOIN |
∩ + B |
Returns all records from table B, and matched records from table A |
FULL OUTER JOIN |
A + ∩ + B |
Returns all records when there is a match in either left or right table |
Join Variants and Filters
Left Join Excluding Intersection
SELECT * FROM A
LEFT JOIN B ON A.key = B.key
WHERE B.key IS NULL;
Right Join Excluding Intersection
SELECT * FROM A
RIGHT JOIN B ON A.key = B.key
WHERE A.key IS NULL;
Full Outer Join Excluding Intersection
SELECT * FROM A
FULL OUTER JOIN B ON A.key = B.key
WHERE A.key IS NULL OR B.key IS NULL;
```\n