What is a JOIN in SQL?
Learn SQL JOINs — INNER, LEFT, RIGHT and FULL OUTER — how join keys and ON conditions work, with examples and common SQL interview questions and answers.
Expected Interview Answer
A SQL JOIN combines rows from two or more tables based on a related column, letting you query normalized data as if it were a single table.
The main types are INNER JOIN (only matching rows in both tables), LEFT JOIN (all rows from the left table plus matches from the right, NULLs where none), RIGHT JOIN (the mirror of LEFT), and FULL OUTER JOIN (all rows from both, matched where possible). A CROSS JOIN returns the Cartesian product. Joins are how relational databases reassemble data that normalization split across tables.
- Query normalized data across related tables
- INNER vs OUTER control which unmatched rows appear
- Foundation of relational reporting
AI Mentor Explanation
A JOIN is like matching a batting scorecard to a player-details sheet by player ID. An INNER JOIN lists only players who appear in both — batted and are on the roster. A LEFT JOIN keeps every player who batted even if their details are missing (shown as blanks). The related column (player ID) is the link, exactly as a JOIN uses a key to stitch two tables into one view.
INNER JOIN example
Orders
- order_id
- customer_id
Customers
- customer_id
- name
Result (INNER)
- order_id
- name
Step-by-Step Explanation
Step 1
Identify the related column
Find the key shared by both tables (e.g. customer_id).
Step 2
Choose the join type
INNER for matches only; LEFT/RIGHT/FULL to keep unmatched rows.
Step 3
Write the ON clause
Specify the match condition: ON a.customer_id = b.customer_id.
Step 4
Select the columns
Pick fields from both tables for the combined result.
What Interviewer Expects
- The four main join types and what each returns
- INNER vs OUTER (LEFT/RIGHT/FULL) distinction
- The role of the ON condition and join key
- Awareness that CROSS JOIN is a Cartesian product
Common Mistakes
- Confusing LEFT and RIGHT join direction
- Assuming INNER JOIN keeps unmatched rows
- Forgetting the ON clause (accidental cross join)
- Not handling NULLs from outer joins
Best Answer (HR Friendly)
“A JOIN combines rows from two related tables using a shared column. An INNER JOIN returns only matching rows, while a LEFT JOIN keeps all rows from the first table and fills blanks where there’s no match. Joins are how you query data that’s been split across normalized tables.”
Code Example
-- Only orders that have a matching customer
SELECT o.order_id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;
-- All orders, NULL name where no customer matches
SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;Follow-up Questions
- What is the difference between a LEFT JOIN and a FULL OUTER JOIN?
- What does a CROSS JOIN produce?
- What is a self join and when is it useful?
- How do NULLs behave in join conditions?
MCQ Practice
1. An INNER JOIN returns?
INNER JOIN keeps only rows with a match on the join condition in both tables.
2. A LEFT JOIN keeps?
LEFT JOIN returns every left-table row, with NULLs where the right side has no match.
3. A JOIN with no ON condition risks producing a?
Without a join condition you get a CROSS JOIN — every combination of rows.
Flash Cards
INNER JOIN? — Returns only rows that match in both tables.
LEFT JOIN? — All rows from the left table plus matches; NULLs where none.
FULL OUTER JOIN? — All rows from both tables, matched where possible.
CROSS JOIN? — The Cartesian product — every combination of rows.