1
OUTER JOIN returns matching rows plus non-matching rows from one or both tables.
2
3
Types of OUTER JOIN:
4
5
a. LEFT OUTER JOIN (LEFT JOIN)
6
• Returns all rows from the left table.
7
• Non-matching rows from the right table are filled with NULL.
8
9
b. RIGHT OUTER JOIN (RIGHT JOIN)
10
• Returns all rows from the right table.
11
• Non-matching rows from the left table are filled with NULL.
12
13
c. FULL OUTER JOIN
14
• Returns all rows from both tables (matching + non-matching).
15
• MySQL does NOT support this directly but can be simulated using UNION.

Simulating FULL OUTER JOIN in MySQL

3. Key Differences

1
• INNER JOIN → Keeps ONLY matching rows.
2
• OUTER JOIN → Includes matching rows + non-matching rows.
3
• LEFT JOIN → Preserves all rows from the left table.
4
• RIGHT JOIN → Preserves all rows from the right table.
5
• FULL OUTER JOIN → Preserves all rows from both tables (not directly supported in MySQL).

Difficulty: 3/10

Topics: JOIN types, NULL handling, result set cardinality

0-2 years experience

1
You're writing a query to list all customers and their orders, but some customers haven't placed orders yet. You used INNER JOIN and now those customers are missing — what’s the fix?
2
Your report shows 500 rows after joining users and profiles, but you know there are 600 users. What’s the most likely cause and how would you check?
3
You need to find all products that have no sales records. How would you write that query in MySQL and why can't you use INNER JOIN?

2-5 years experience

1
A feature that shows user activity with their last payment broke after a data migration — now it’s showing fewer users than before. You suspect the JOIN changed. How do you debug this?
2
Your team switched from LEFT JOIN to INNER JOIN to improve performance, but now support is flooded with tickets about missing user data. How do you explain the tradeoff and what would you do differently?
3
A dashboard shows inconsistent counts between two reports — one uses LEFT JOIN, the other INNER JOIN on the same tables. How do you investigate and resolve the discrepancy?

5-8 years experience

1
You’re optimizing a reporting query that joins 5 large tables and uses multiple OUTER JOINs — it’s taking 12 seconds. How would you approach reducing latency without losing data integrity?
2
A legacy system uses FULL OUTER JOINs across sharded tables, but MySQL doesn’t support them natively. How would you redesign this to scale while preserving data completeness?
3
An analytics pipeline uses LEFT JOINs to preserve all events, but the result set is exploding in size. How do you balance completeness with performance and storage costs?

8+ years experience

1
We’re migrating from PostgreSQL to MySQL and have dozens of queries using FULL OUTER JOIN — what’s your strategy to preserve business logic without breaking downstream systems?
2
Two teams rely on the same user-order view: one needs all users (LEFT JOIN), the other only active ones (INNER JOIN). How do you design the data layer to avoid duplication and maintain consistency long-term?
3
A critical report uses OUTER JOINs across 10+ microservice tables, causing slow ETLs and inconsistent snapshots. How would you architect a sustainable solution that decouples data consumption from source schema changes?

❓What happens if you join on a column with NULL values?
❓How would you verify you didn't accidentally lose data after switching from LEFT to INNER JOIN?
❓Can you explain why a FULL OUTER JOIN might be expensive in MySQL?