SQL Joins Notes
SQL JOIN is used to combine rows from two tables using a related column.
Simple meaning:
A join connects data from different tables.
Example tables used in this note:
Example Tables
students
| student_id | name |
|---|---|
| 1 | Ram |
| 2 | Sita |
| 3 | Hari |
marks
| mark_id | student_id | subject | score |
|---|---|---|---|
| 101 | 1 | Math | 90 |
| 102 | 2 | Science | 85 |
| 103 | 4 | English | 70 |
Common column:
| Table | Common Column |
|---|---|
students | student_id |
marks | student_id |
Quick Summary
| Join Type | Simple Meaning | Shows Non-Matching Rows? | Most Common Use |
|---|---|---|---|
INNER JOIN | Only matching rows from both tables | No | Get only related data |
LEFT JOIN | All rows from left table plus matching right table rows | Yes, from left table | Show all main records |
RIGHT JOIN | All rows from right table plus matching left table rows | Yes, from right table | Less common; similar to left join |
FULL OUTER JOIN | All rows from both tables | Yes, from both tables | Compare two full datasets |
CROSS JOIN | Every row combines with every row | Not based on matching | Create all combinations |
SELF JOIN | A table joins with itself | Depends on join type | Employee-manager, category-subcategory |
NATURAL JOIN | Automatically joins same column names | Depends on match | Rare; avoid in serious projects |
1. INNER JOIN
Meaning
INNER JOIN returns only rows that match in both tables.
If a row exists in one table but not the other, it will not show.
SQL
SELECT students.name, marks.subject, marks.score
FROM students
INNER JOIN marks
ON students.student_id = marks.student_id;
Result
| name | subject | score |
|---|---|---|
| Ram | Math | 90 |
| Sita | Science | 85 |
Why?
| Student | In students Table? | In marks Table? | Shows? |
|---|---|---|---|
| Ram | Yes | Yes | Yes |
| Sita | Yes | Yes | Yes |
| Hari | Yes | No | No |
| student_id 4 | No | Yes | No |
Real Project Example
Get users who placed orders.
SELECT users.name, orders.order_id
FROM users
INNER JOIN orders
ON users.user_id = orders.user_id;
Use INNER JOIN when you only want matched data.
2. LEFT JOIN
Meaning
LEFT JOIN returns all rows from the left table.
If there is no match in the right table, right table columns become NULL.
SQL
SELECT students.name, marks.subject, marks.score
FROM students
LEFT JOIN marks
ON students.student_id = marks.student_id;
Result
| name | subject | score |
|---|---|---|
| Ram | Math | 90 |
| Sita | Science | 85 |
| Hari | NULL | NULL |
Why?
| Student | Has Marks? | Shows? |
|---|---|---|
| Ram | Yes | Yes |
| Sita | Yes | Yes |
| Hari | No | Yes, but marks are NULL |
Real Project Example
Get all users, even users who have not placed orders.
SELECT users.name, orders.order_id
FROM users
LEFT JOIN orders
ON users.user_id = orders.user_id;
Use LEFT JOIN when the left table is the main table.
3. RIGHT JOIN
Meaning
RIGHT JOIN returns all rows from the right table.
If there is no match in the left table, left table columns become NULL.
SQL
SELECT students.name, marks.subject, marks.score
FROM students
RIGHT JOIN marks
ON students.student_id = marks.student_id;
Result
| name | subject | score |
|---|---|---|
| Ram | Math | 90 |
| Sita | Science | 85 |
| NULL | English | 70 |
Why?
| Mark Record | Has Matching Student? | Shows? |
|---|---|---|
| student_id 1 | Yes | Yes |
| student_id 2 | Yes | Yes |
| student_id 4 | No | Yes, but student name is NULL |
Real Project Example
Get all payments, even if the user record is missing.
SELECT users.name, payments.amount
FROM users
RIGHT JOIN payments
ON users.user_id = payments.user_id;
In real projects, many developers avoid RIGHT JOIN and rewrite it as a LEFT JOIN by switching table order.
4. FULL OUTER JOIN
Meaning
FULL OUTER JOIN returns all rows from both tables.
Matching rows are connected. Non-matching rows still show with NULL.
SQL
SELECT students.name, marks.subject, marks.score
FROM students
FULL OUTER JOIN marks
ON students.student_id = marks.student_id;
Result
| name | subject | score |
|---|---|---|
| Ram | Math | 90 |
| Sita | Science | 85 |
| Hari | NULL | NULL |
| NULL | English | 70 |
Why?
| Record | Match Found? | Shows? |
|---|---|---|
| Ram | Yes | Yes |
| Sita | Yes | Yes |
| Hari | No marks | Yes |
| English mark for student_id 4 | No student | Yes |
MySQL Note
MySQL does not directly support FULL OUTER JOIN.
In MySQL, you can use LEFT JOIN plus RIGHT JOIN with UNION.
SELECT students.name, marks.subject, marks.score
FROM students
LEFT JOIN marks
ON students.student_id = marks.student_id
UNION
SELECT students.name, marks.subject, marks.score
FROM students
RIGHT JOIN marks
ON students.student_id = marks.student_id;
Use FULL OUTER JOIN when you want to compare complete data from both tables.
5. CROSS JOIN
Meaning
CROSS JOIN creates all possible combinations between two tables.
It does not need a matching column.
SQL
SELECT students.name, marks.subject
FROM students
CROSS JOIN marks;
Result Example
If there are 3 students and 3 marks:
| Formula | Total Rows |
|---|---|
| 3 students x 3 marks | 9 rows |
Small Example
| name | subject |
|---|---|
| Ram | Math |
| Ram | Science |
| Ram | English |
| Sita | Math |
| Sita | Science |
| Sita | English |
| Hari | Math |
| Hari | Science |
| Hari | English |
Real Project Example
Create all possible combinations of colors and sizes.
SELECT colors.color_name, sizes.size_name
FROM colors
CROSS JOIN sizes;
Be careful: CROSS JOIN can create a huge number of rows.
6. SELF JOIN
Meaning
SELF JOIN means a table joins with itself.
This is useful when rows in the same table are related to other rows in the same table.
Example Table: employees
| emp_id | name | manager_id |
|---|---|---|
| 1 | John | NULL |
| 2 | Alex | 1 |
| 3 | Maria | 1 |
Here, manager_id points to another employee's emp_id.
SQL
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;
Result
| employee | manager |
|---|---|
| John | NULL |
| Alex | John |
| Maria | John |
Why Use Aliases?
| Alias | Meaning |
|---|---|
e | employee row |
m | manager row |
The table is the same, but aliases make it act like two separate table copies.
Real Project Example
Use SELF JOIN for:
| Case | Example |
|---|---|
| Employee and manager | employees.manager_id points to employees.emp_id |
| Category and parent category | categories.parent_id points to categories.id |
| User referral | users.referred_by points to users.user_id |
7. NATURAL JOIN
Meaning
NATURAL JOIN automatically joins tables using columns with the same name.
SQL
SELECT *
FROM students
NATURAL JOIN marks;
Because both tables have student_id, SQL joins them using student_id.
Problem
NATURAL JOIN can be risky.
If both tables later get another same column name, the query result can change without warning.
Better Way
Use this instead:
SELECT students.name, marks.subject, marks.score
FROM students
JOIN marks
ON students.student_id = marks.student_id;
Clear code is better than magic code.
8. JOIN With Multiple Conditions
Meaning
Sometimes one column is not enough to correctly match rows.
You can join using multiple conditions.
SQL
SELECT *
FROM orders o
JOIN payments p
ON o.order_id = p.order_id
AND o.user_id = p.user_id;
Meaning
Rows match only when both conditions are true:
| Condition | Meaning |
|---|---|
o.order_id = p.order_id | Same order |
o.user_id = p.user_id | Same user |
Real Project Example
This helps avoid connecting a payment to the wrong user's order.
9. Difference Between WHERE and ON
ON
ON tells SQL how two tables are connected.
SELECT students.name, marks.subject
FROM students
LEFT JOIN marks
ON students.student_id = marks.student_id;
WHERE
WHERE filters the final result.
SELECT students.name, marks.subject
FROM students
LEFT JOIN marks
ON students.student_id = marks.student_id
WHERE marks.score > 80;
Important
With LEFT JOIN, using WHERE on the right table can remove NULL rows.
Example:
WHERE marks.score > 80
This removes students who do not have marks.
So be careful with WHERE after a LEFT JOIN.
Interview Memory Table
| Question | Short Answer |
|---|---|
| Which join gives only matching rows? | INNER JOIN |
| Which join gives all rows from left table? | LEFT JOIN |
| Which join gives all rows from right table? | RIGHT JOIN |
| Which join gives all rows from both tables? | FULL OUTER JOIN |
| Which join gives all combinations? | CROSS JOIN |
| Which join uses the same table twice? | SELF JOIN |
| Which join should you usually avoid? | NATURAL JOIN |
Most Used Joins in Real Projects
| Rank | Join Type | Why |
|---|---|---|
| 1 | INNER JOIN | Most common when data must match |
| 2 | LEFT JOIN | Very useful for showing all main records |
| 3 | SELF JOIN | Useful for hierarchy data |
| 4 | CROSS JOIN | Useful only for combinations |
| 5 | RIGHT JOIN | Less common because LEFT JOIN can replace it |
| 6 | FULL OUTER JOIN | Useful but not supported directly in MySQL |
| 7 | NATURAL JOIN | Avoid because it can be unclear |
Final Simple Trick
| Join | Think Like This |
|---|---|
INNER JOIN | Match only |
LEFT JOIN | Keep all left |
RIGHT JOIN | Keep all right |
FULL OUTER JOIN | Keep everything |
CROSS JOIN | Make combinations |
SELF JOIN | Same table twice |
NATURAL JOIN | Auto match same column names |