What is a JOIN?
Learn what a JOIN is in SQL, how it matches rows across tables using keys, the main join types, and how to write one with a real query example.
Expected Interview Answer
A JOIN is a SQL clause that combines rows from two or more tables by matching values in a related column, typically a foreign key linked to a primary key, so you can retrieve connected data from normalized tables in a single query.
The database engine evaluates the join condition, usually equality on a key column, using an execution strategy such as a hash join, merge join, or nested loop join, chosen based on table size and available indexes. Joins let you avoid duplicating data across tables — instead of repeating a customer's full details on every order row, you store a customer_id foreign key and JOIN to the customers table only when you need the name or email. INNER JOIN returns only rows with matches in both tables, while outer joins preserve unmatched rows from one or both sides. Indexing the columns used in the ON clause is critical for join performance on large tables.
- Eliminates data duplication by linking normalized tables
- Lets you query related data across tables in one statement
- Supports multiple join types for different matching needs
- Enables the optimizer to use indexes for fast lookups
AI Mentor Explanation
A JOIN is like a scoreboard operator matching a batsman's name from the team sheet to his live runs on the scoring table, combining two separate records into one readable line. The team sheet holds player identity while the scoring table holds ball-by-ball data, and only the shared player ID lets the operator merge them correctly.
How customers and orders connect via a foreign key
customers
- id (PK)
- name
orders
- id (PK)
- customer_id (FK)
- amount
Step-by-Step Explanation
Step 1
Identify the tables
Pick the two or more tables that hold related data, such as orders and customers.
Step 2
Find the relationship
Locate the foreign key column in one table that references the primary key in the other, e.g. orders.customer_id references customers.id.
Step 3
Write the JOIN clause
Use JOIN table_name ON condition to state exactly how rows should be matched.
Step 4
Choose the join type
Pick INNER, LEFT, RIGHT, or FULL depending on whether unmatched rows should be kept.
Step 5
Select the columns
List columns from either table in the SELECT clause, qualifying names when they collide.
Step 6
Let the optimizer execute
The engine picks a hash, merge, or nested loop strategy based on indexes and table size.
What Interviewer Expects
- Explains that a JOIN matches rows via a related column, usually a key
- Can name at least INNER and LEFT/OUTER joins
- Understands why joins avoid data duplication in normalized schemas
- Mentions that join performance depends on indexing the join columns
- Can write correct basic JOIN syntax
Common Mistakes
- Forgetting the ON condition, causing an accidental cross join
- Confusing INNER JOIN behavior with LEFT JOIN on unmatched rows
- Not qualifying column names when both tables share a column name
- Joining on unindexed columns and then blaming the database for slowness
Best Answer (HR Friendly)
“A JOIN is how SQL connects information stored in separate tables, like matching a customer to their orders, so you can see the full picture in one query instead of storing everything in one giant table.”
Code Example
-- customers: id, name
-- (1, 'Amit'), (2, 'Priya')
-- orders: id, customer_id, amount
-- (101, 1, 250), (102, 1, 90), (103, 2, 500)
SELECT c.name, o.id AS order_id, o.amount
FROM customers c
JOIN orders o ON o.customer_id = c.id;
-- Result:
-- name | order_id | amount
-- Amit | 101 | 250
-- Amit | 102 | 90
-- Priya | 103 | 500Follow-up Questions
- What are the different types of SQL joins?
- What happens if you omit the ON clause in a JOIN?
- How does a JOIN differ from a subquery for combining data?
- How does indexing affect JOIN performance?
- Can you JOIN more than two tables in one query?
MCQ Practice
1. What does a JOIN primarily do in SQL?
A JOIN matches rows across tables using a related column, typically a foreign key referencing a primary key.
2. Which join type returns only rows that match in both tables?
INNER JOIN returns only rows where the join condition is satisfied in both tables.
3. What commonly causes an accidental cross join?
Without an ON condition, the database has no matching rule and can produce a Cartesian product of every row combination.
Flash Cards
What is a JOIN? — A SQL clause that combines rows from two or more tables based on a related column, usually a foreign key to primary key relationship.
Why use JOINs instead of one big table? — To avoid data duplication in normalized schemas — related data stays in separate tables and is combined only when queried.
What determines which rows a JOIN matches? — The ON condition, typically comparing a foreign key in one table to a primary key in another.
What affects JOIN performance most? — Whether the join columns are indexed, and which join algorithm (hash, merge, or nested loop) the optimizer chooses.