Answer

INNER JOIN returns only those records for which there is a match in both joined tables based on a specified condition. When using OUTER JOIN (LEFT, RIGHT, FULL), the result contains all rows from one or both tables, even if there are no matches in the other table.

• LEFT OUTER JOIN: all rows from the left table + matches from the right, if any (otherwise NULL).
• RIGHT OUTER JOIN: all rows from the right table + matches from the left, if any (otherwise NULL).
• FULL OUTER JOIN: all rows from both tables, where no match — opposite values NULL.

When to use which JOIN:

• Use INNER JOIN if you only need matching rows.
• LEFT JOIN — if all rows from the first (left) table are needed regardless of matches.
• RIGHT JOIN — similarly, but for the right table.
• FULL OUTER JOIN — a complete combined list (useful for finding mismatches).

Examples:

-- INNER JOIN
SELECT u.id, u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT OUTER JOIN
SELECT u.id, u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- FULL OUTER JOIN
SELECT u.id, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

Trick Question

What will be the result of a LEFT JOIN if the right table is empty?

People often answer: "LEFT JOIN will return an empty result because there are no matches". The correct answer: LEFT JOIN will always return all rows from the left table, and the columns from the right will be NULL.

Example:

-- Table users has records, orders is empty
SELECT u.id, u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id=o.user_id;
-- Result: all users, o.amount = NULL

Examples of real mistakes due to lack of knowledge of the topic

---

Story

Sales analytics project. To output a full list of clients and their orders, INNER JOIN was used, skipping clients who had no orders. Important clients were lost in the report due to their absence in the right table. Solution: use LEFT JOIN.

---

Story

Employee and department accounting platform. Using RIGHT JOIN instead of LEFT JOIN led to confusion when adding new departments: some employees stopped appearing in the report. Reason — the order of joining was mixed up.

---

Story

Data schema migration. After changes, one of the JOINs turned into a CROSS JOIN due to a typo in the join condition. The result — a sharp increase in the volume of the query and "inflation" of data. Conclusion: always specify ON in joins and carefully check the type of JOIN.