What are LEFT JOIN and RIGHT JOIN, and how do they differ from INNER JOIN?

Difficulty: 5/10

joins, SQL query design, performance

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. INNER JOIN

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.

2. LEFT JOIN (LEFT OUTER JOIN)

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

3. RIGHT JOIN (RIGHT OUTER JOIN)

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

4. Key Differences

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.

Scenario Questions

0-2 years experience

What are LEFT JOIN and RIGHT JOIN, and how do they differ from INNER JOIN?

What are LEFT JOIN and RIGHT JOIN, and how do they differ from INNER JOIN?

Understanding LEFT JOIN, RIGHT JOIN, and INNER JOIN

Scenario Questions

Follow-up Questions