Joins are how SQL combines data from multiple tables. They're one of the most important concepts in relational databases and one of the most commonly misunderstood.

Sample tables

-- users
id | name
1  | Alice
2  | Bob
3  | Carol

-- orders
id | user_id | product
1  | 1       | Laptop
2  | 1       | Mouse
3  | 4       | Keyboard  -- user_id 4 doesn't exist in users

INNER JOIN — only matching rows

Returns rows where there's a match in both tables.

SELECT users.name, orders.product
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Result: Alice/Laptop, Alice/Mouse (Bob and Carol have no orders; order 3 has no matching user)

LEFT JOIN — all rows from the left table

Returns all rows from the left table, with NULL for missing matches on the right.

SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Result: Alice/Laptop, Alice/Mouse, Bob/NULL, Carol/NULL

Use LEFT JOIN when you want all records from the main table regardless of whether they have related records.

RIGHT JOIN — all rows from the right table

The mirror of LEFT JOIN. Less commonly used — you can usually rewrite a RIGHT JOIN as a LEFT JOIN by swapping the tables.

FULL JOIN — all rows from both tables

Returns all rows from both tables, with NULLs where there's no match. Not supported in MySQL (use UNION of LEFT and RIGHT JOIN instead).

The most common mistake

Forgetting that INNER JOIN silently drops rows with no match. If you're trying to list all users with their order count (including users with zero orders), use LEFT JOIN:

SELECT users.name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

With INNER JOIN, users with no orders would disappear from the results.

When to use which

  • •INNER JOIN: you only want records that exist in both tables
  • •LEFT JOIN: you want all records from the main (left) table, whether or not they have related records
  • •FULL JOIN: you want all records from both tables regardless of matches

In practice, INNER JOIN and LEFT JOIN cover 95% of real-world use cases.