Chapter 6: Joins ⭐
🎯 Goal
By the end of this chapter, you'll understand:
What a JOIN is
Why JOINs are needed
Why splitting data into multiple tables is a good idea
INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN (concept)
CROSS JOIN
Self Join (basic idea)
Real-life examples
Common mistakes beginners make
Part 1 — The Story (Learn Like a Movie)
🎬 The World's Smartest Restaurant
Imagine a huge restaurant called SQL Paradise.
The owner, Mr. Database, wanted everything to be organized.
Instead of writing everything in one giant notebook, he made separate registers.
Register 1 — Customers
| Customer ID | Name |
|---|---|
| 1 | Rahul |
| 2 | Priya |
| 3 | Aman |
Register 2 — Orders
| Order ID | Customer ID | Food |
|---|---|---|
| 101 | 1 | Pizza |
| 102 | 2 | Burger |
| 103 | 1 | Pasta |
One day a waiter asked:
"Who ordered Pizza?"
The chef looked at the Orders register.
Order 101
Customer ID = 1
Pizza
Chef:
"Okay... but who is Customer 1?"
Now he had to open the Customers register.
Customer ID = 1
Rahul
Now he knew:
Rahul ordered Pizza.
The waiter asked another question.
"Show everyone's name with the food they ordered."
Chef sighed.
He had to compare both registers line by line.
Customer Register
↓
Order Register
↓
Customer Register
↓
Order Register
↓
Customer Register
↓
Order Register
It took forever.
Then entered...
Captain JOIN 🦸
Captain JOIN said,
"Why are you searching manually?"
"I'll combine both registers."
Suddenly...
He created a magical report.
| Name | Food |
|---|---|
| Rahul | Pizza |
| Priya | Burger |
| Rahul | Pasta |
Everyone clapped.
The owner asked:
"How did you do that?"
Captain JOIN smiled.
"Both registers have Customer ID."
That common value lets us match the rows.
Imagine Customer ID is like an Aadhaar number.
Even if there are two Rahuls,
Customer IDs remain unique.
So matching becomes easy.
Now the restaurant became very big.
There were new tables.
Customers
| ID | Name |
|---|---|
| 1 | Rahul |
| 2 | Priya |
| 3 | Aman |
| 4 | John |
Orders
| Order | Customer ID | Food |
|---|---|---|
| 101 | 1 | Pizza |
| 102 | 2 | Burger |
| 103 | 1 | Pasta |
Notice something?
John never ordered anything.
Now the owner asked,
"Show ALL customers."
Captain JOIN replied,
"I can."
Result:
| Customer | Food |
|---|---|
| Rahul | Pizza |
| Rahul | Pasta |
| Priya | Burger |
| Aman | NULL |
| John | NULL |
Even customers without orders appeared.
That's another kind of JOIN.
Next day...
Someone placed an order.
But due to a bug...
Customer ID = 99
There was no Customer 99.
Order table:
| Order | Customer |
|---|---|
| 104 | 99 |
Now the owner asked:
"Show ALL orders."
Captain JOIN answered:
| Order | Customer |
|---|---|
| 101 | Rahul |
| 102 | Priya |
| 103 | Rahul |
| 104 | NULL |
The order still appeared.
Even though customer didn't exist.
That's another JOIN.
One day...
The owner said,
"I want EVERY customer matched with EVERY food."
Captain JOIN:
"Seriously?"
Owner:
"Yes."
Result:
Rahul → Pizza
Rahul → Burger
Rahul → Pasta
Priya → Pizza
Priya → Burger
Priya → Pasta
Aman → Pizza
Aman → Burger
...
Everyone got paired with everything.
That's another JOIN.
Finally...
The owner asked,
"Who referred whom?"
Customer table:
| ID | Name | Referred By |
|---|---|---|
| 1 | Rahul | NULL |
| 2 | Priya | 1 |
| 3 | Aman | 2 |
Now Captain JOIN joined the table...
with...
ITSELF.
| Customer | Referred By |
|---|---|
| Priya | Rahul |
| Aman | Priya |
Mind blown.
Moral of the Story
JOIN means:
Combining data from two (or more) tables using a common column.
Usually,
that common column is
Customer ID
User ID
Product ID
Order ID
Why Do We Split Data?
Imagine storing everything in ONE table.
| Order | Customer | Phone | Address | Food |
|---|---|---|---|---|
| 101 | Rahul | 9999 | Delhi | Pizza |
| 102 | Rahul | 9999 | Delhi | Burger |
| 103 | Rahul | 9999 | Delhi | Pasta |
Rahul's information repeats again and again.
Problems:
❌ Wasted space
❌ Hard to update
❌ More errors
Instead:
Customers
Orders
Products
Payments
Different tables.
Much cleaner.
Then JOIN connects them whenever needed.
What is a JOIN?
A JOIN combines rows from two or more tables based on a related column.
Example:
Customers
| ID | Name |
|---|---|
| 1 | Rahul |
| 2 | Priya |
Orders
| Order | Customer ID |
|---|---|
| 101 | 1 |
| 102 | 2 |
JOIN Result
| Order | Customer |
|---|---|
| 101 | Rahul |
| 102 | Priya |
Types of JOINs
1. INNER JOIN ⭐⭐⭐
Returns only matching rows.
Customers
| ID | Name |
|---|---|
| 1 | Rahul |
| 2 | Priya |
| 3 | John |
Orders
| Order | Customer |
|---|---|
| 101 | 1 |
| 102 | 2 |
Result
| Customer | Order |
|---|---|
| Rahul | 101 |
| Priya | 102 |
John disappears because he has no order.
Think of it as:
"Show only people who have both customer details and orders."
2. LEFT JOIN ⭐⭐⭐
Returns:
Everything from the LEFT table
Matching rows from the RIGHT table
Customers
| ID | Name |
|---|---|
| 1 | Rahul |
| 2 | Priya |
| 3 | John |
Orders
| Order | Customer |
|---|---|
| 101 | 1 |
| 102 | 2 |
Result
| Customer | Order |
|---|---|
| Rahul | 101 |
| Priya | 102 |
| John | NULL |
John stays because the left table is Customers.
3. RIGHT JOIN
Opposite of LEFT JOIN.
Returns:
Everything from the RIGHT table
Matching rows from the LEFT table
Useful when you care more about the second table.
4. FULL JOIN (Concept)
Returns:
Everything from both tables.
Matches where possible.
Otherwise fills with NULL.
Some databases (like MySQL) don't support FULL JOIN directly.
5. CROSS JOIN
Every row matches every other row.
Customers
Rahul
Priya
Products
Pizza
Burger
Result
Rahul → Pizza
Rahul → Burger
Priya → Pizza
Priya → Burger
This creates all possible combinations.
6. SELF JOIN
A table joins with itself.
Example:
Employees
| ID | Name | Manager ID |
|---|---|---|
| 1 | CEO | NULL |
| 2 | Alice | 1 |
| 3 | Bob | 2 |
Result:
| Employee | Manager |
|---|---|
| Alice | CEO |
| Bob | Alice |
Useful for hierarchies like managers, referrals, or family trees.
Real-Life Examples
Amazon
Tables:
Customers
Orders
Products
Payments
Addresses
JOINs answer questions like:
Which customer bought which product?
Which orders are unpaid?
Which address belongs to this order?
Hospital
Tables:
Patients
Doctors
Appointments
JOIN:
Patient + Doctor + Appointment
School
Tables:
Students
Subjects
Marks
JOIN:
Student + Subject + Marks
Banking
Tables:
Customers
Accounts
Transactions
JOIN:
Customer + Account + Transaction
Beginner Mistakes
❌ Joining using the wrong columns (e.g., Name instead of ID)
❌ Forgetting the JOIN condition
❌ Expecting unmatched rows in an INNER JOIN
❌ Confusing LEFT JOIN and RIGHT JOIN
❌ Assuming NULL means "0" or an empty string—it means "no matching value."
Part 2 — Question & Answer (Progressive Learning)
Q1. What is a JOIN?
Answer: A JOIN combines related data from two or more tables using a common column.
Q2. Why do we use JOINs?
Answer: Because databases store related information in separate tables to reduce duplication and improve organization. JOINs bring that data together when needed.
Q3. What is usually used to connect two tables?
Answer: A common column, such as Customer ID, User ID, or Product ID.
Q4. Why not store everything in one table?
Answer: It causes repeated data, wastes storage, makes updates difficult, and increases the chance of inconsistencies.
Q5. What does an INNER JOIN return?
Answer: Only the rows where matching values exist in both tables.
Q6. What does a LEFT JOIN return?
Answer: All rows from the left table, plus matching rows from the right table. If no match exists, the right-side columns contain NULL.
Q7. What does a RIGHT JOIN return?
Answer: All rows from the right table, plus matching rows from the left table. If no match exists, the left-side columns contain NULL.
Q8. What is a CROSS JOIN?
Answer: It combines every row from the first table with every row from the second table, producing all possible combinations.
Q9. What is a SELF JOIN?
Answer: A SELF JOIN joins a table with itself, often to represent relationships such as employees and managers or customers and referrals.
Q10. Give some real-life examples where JOINs are used.
Answer: E-commerce (customers + orders), hospitals (patients + doctors), schools (students + marks), banking (customers + transactions), and social media (users + posts).
Part 3 — Top 5 MCQs
1. What is the primary purpose of a JOIN?
A. Delete rows
B. Combine related data from multiple tables
C. Create a database
D. Rename columns
✅ Answer: B
2. Which JOIN returns only matching rows from both tables?
A. LEFT JOIN
B. RIGHT JOIN
C. INNER JOIN
D. CROSS JOIN
✅ Answer: C
3. Which JOIN returns all rows from the left table?
A. INNER JOIN
B. LEFT JOIN
C. RIGHT JOIN
D. CROSS JOIN
✅ Answer: B
4. Which JOIN produces every possible combination of rows?
A. INNER JOIN
B. LEFT JOIN
C. CROSS JOIN
D. SELF JOIN
✅ Answer: C
5. A SELF JOIN is mainly used when:
A. Joining three different tables
B. Joining a table with itself
C. Deleting duplicate rows
D. Creating a new database
✅ Answer: B
🧠 Chapter 6 Summary
JOINs combine related data from multiple tables.
Tables are split to avoid duplicate information and improve organization.
INNER JOIN → only matching rows.
LEFT JOIN → all rows from the left table, matching rows from the right.
RIGHT JOIN → all rows from the right table, matching rows from the left.
FULL JOIN → all rows from both tables (where supported).
CROSS JOIN → every row paired with every other row.
SELF JOIN → a table joined with itself.
In real-world databases, JOINs are among the most frequently used SQL operations because business data is almost always spread across multiple related tables.
➡️ Next Chapter: Chapter 7 — GROUP BY, where you'll learn how databases summarize and group data, such as finding total sales per customer, average salary per department, or number of orders per city.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.