Explain Types of SQL Joins
A clear guide to SQL join types — INNER, LEFT, RIGHT, FULL OUTER, and CROSS JOIN — with examples showing exactly which rows each one keeps in the result.
Expected Interview Answer
SQL has four core join types — INNER, LEFT (OUTER), RIGHT (OUTER), and FULL (OUTER) — plus CROSS JOIN, and they differ in which unmatched rows from each side they keep or drop when combining two tables.
INNER JOIN returns only rows where the join condition matches in both tables, discarding anything unmatched. LEFT JOIN keeps every row from the left table and fills in NULLs for columns from the right table when there is no match, while RIGHT JOIN does the mirror opposite, keeping every row from the right table. FULL OUTER JOIN keeps all rows from both tables, matching where possible and filling NULLs elsewhere, and CROSS JOIN produces the Cartesian product of both tables with no matching condition at all. MySQL lacks native FULL OUTER JOIN and typically simulates it with a UNION of LEFT and RIGHT joins.
- INNER JOIN gives clean, matched-only result sets
- LEFT/RIGHT JOIN surface rows with no counterpart, useful for gap analysis
- FULL OUTER JOIN reconciles two datasets completely
- CROSS JOIN generates combinations, useful for pairing every option with every other
AI Mentor Explanation
INNER JOIN is like listing only players who appear on both the squad sheet and today's playing eleven, dropping anyone missing from either list. LEFT JOIN instead keeps the full squad sheet and marks non-playing members as unused, showing everyone regardless of whether they took the field.
How INNER, LEFT, RIGHT, and FULL joins differ in kept rows
INNER JOIN
- Matched rows only
LEFT JOIN
- All of left table
- NULLs where right has no match
RIGHT JOIN
- All of right table
- NULLs where left has no match
FULL OUTER JOIN
- All rows from both tables
- NULLs where either side has no match
Step-by-Step Explanation
Step 1
Start with INNER JOIN
Use it when you only want rows that have a match on both sides of the relationship.
Step 2
Switch to LEFT JOIN
Use it when every row from the primary (left) table must appear, even without a match on the right.
Step 3
Use RIGHT JOIN sparingly
Functionally the mirror of LEFT JOIN; most teams just swap table order and use LEFT JOIN instead for consistency.
Step 4
Reach for FULL OUTER JOIN
Use it for reconciliation tasks where you need every row from both tables, matched or not.
Step 5
Reserve CROSS JOIN for combinations
Use it only when you deliberately want every row of one table paired with every row of another.
Step 6
Filter NULLs after outer joins
Add a WHERE clause checking IS NULL on the join key to isolate unmatched rows when needed.
What Interviewer Expects
- Names all four core join types plus CROSS JOIN
- Explains what happens to unmatched rows in each type
- Knows LEFT/RIGHT JOIN preserve one side even without a match
- Can describe a practical use case for each type
- Mentions that MySQL lacks native FULL OUTER JOIN
Common Mistakes
- Confusing LEFT JOIN and RIGHT JOIN direction
- Assuming FULL OUTER JOIN is supported everywhere without checking the dialect
- Using CROSS JOIN unintentionally by forgetting the ON clause
- Not filtering NULLs when trying to isolate only unmatched rows after an outer join
Best Answer (HR Friendly)
“SQL joins decide what happens to rows that don't have a match in the other table. Some joins keep only the matches, while others keep all the rows from one or both tables and just leave blanks where there's no match, which is useful for finding gaps in your data.”
Code Example
-- customers: id, name -> (1,'Amit'), (2,'Priya'), (3,'Neha')
-- orders: id, customer_id, amount -> (101,1,250), (102,2,500)
-- INNER JOIN: Neha (no orders) is excluded
SELECT c.name, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
-- Amit | 250
-- Priya | 500
-- LEFT JOIN: Neha is kept with a NULL amount
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Amit | 250
-- Priya | 500
-- Neha | NULLFollow-up Questions
- How would you simulate a FULL OUTER JOIN in MySQL?
- What is the difference between a self join and a regular join?
- How do you find unmatched rows using a LEFT JOIN?
- When would you deliberately use a CROSS JOIN?
- How does join order affect query performance?
MCQ Practice
1. Which join keeps all rows from the left table, filling NULLs where there's no match on the right?
LEFT JOIN preserves every row from the left table and fills in NULLs for right-table columns when no match exists.
2. Which join type produces a Cartesian product of both tables?
CROSS JOIN pairs every row of one table with every row of the other, with no matching condition.
3. How is a FULL OUTER JOIN commonly simulated in MySQL, which lacks native support?
MySQL does not support FULL OUTER JOIN natively, so it is typically emulated with a UNION of a LEFT JOIN and a RIGHT JOIN.
Flash Cards
What does INNER JOIN return? — Only rows where the join condition matches in both tables.
What does LEFT JOIN return? — All rows from the left table, with NULLs for right-table columns when there's no match.
What does FULL OUTER JOIN return? — All rows from both tables, matched where possible and NULL-filled elsewhere.
What does CROSS JOIN return? — The Cartesian product — every row of one table paired with every row of the other, no condition needed.