What are LEFT JOIN and RIGHT JOIN, and how do they differ from INNER JOIN?
Understanding LEFT JOIN, RIGHT JOIN, and INNER JOIN
INNER JOIN, LEFT JOIN, and RIGHT JOIN are used to combine rows from multiple tables, but they differ in how they handle unmatched rows.
- 1
• Returns ONLY the rows that have matching values in both tables.
- 2
• Rows without matches are excluded.
- 3
- 4
Example:
- 5
- 6
SELECT u.id, u.name, o.amount
- 7
FROM users u
- 8
INNER JOIN orders o ON u.id = o.user_id;
- 9
- 10
• Output: Only users who have at least one order.
- 1
• Returns ALL rows from the left table.
- 2
• Matching rows from the right table are included.
- 3
• Non-matching rows from the right table appear as NULL.
- 4
- 5
Example:
- 6
- 7
SELECT u.id, u.name, o.amount
- 8
FROM users u
- 9
LEFT JOIN orders o ON u.id = o.user_id;
- 10
- 11
• Output: All users, even those with NO orders (order fields = NULL).
- 1
• Returns ALL rows from the right table.
- 2
• Matching rows from the left table are included.
- 3
• Non-matching left-table rows appear as NULL.
- 4
- 5
Example:
- 6
- 7
SELECT u.id, u.name, o.amount
- 8
FROM users u
- 9
RIGHT JOIN orders o ON u.id = o.user_id;
- 10
- 11
• Output: All orders, even if the referenced user is missing.
- 1
• INNER JOIN → Only matching rows.
- 2
• LEFT JOIN → All left table rows + matching right table rows.
- 3
• RIGHT JOIN → All right table rows + matching left table rows.
- 4
• LEFT and RIGHT JOIN include unmatched rows as NULL (INNER JOIN does not).
In summary: INNER JOIN shows only related data, while LEFT/RIGHT JOINs preserve all rows from one side, making them useful for detecting missing or optional relationships.