What Is a Join?
A join combines rows from tables using a condition. The condition matters: joining customers to orders by customer_id returns one row for each matching order, not necessarily one row per customer.
Common Types
•
INNER JOIN: return matching pairs only.
•
LEFT JOIN: keep every row from the left table. Unmatched right-side columns become NULL.
•
FULL JOIN: keep unmatched rows from both sides as well.
•
CROSS JOIN: return every possible pair. Two tables with 10 and 20 rows produce 200 rows.
Example: Customers Without Orders
Assume customers(id, name) and orders(id, customer_id). This query keeps customers even when they have no order.
SELECT c.id, c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;
Plain Text
복사
Common Mistakes
A condition on the right table in WHERE can remove the NULL rows preserved by a LEFT JOIN. Put a condition in ON when it should control matching while retaining all left-side rows.
Several orders for one customer repeat that customer’s details. This is expected. Before adding DISTINCT, check whether the join condition and intended result are correct. Use an aggregate when the question is “how many orders?” rather than “which orders?”
