Skip to content
Zeni Study
  • Pages
    • Learn Python In 7 Days
      • Day 1 : Variable + if/else
      • Day 2: Loops
      • Day 3 : Array + String
      • Day 4 — Functions
      • Day 5 — Dictionaries - Hashmap
      • Day 6 — Files & Errors
      • Day 7 — Mini Project & Programmer Thinking
    • Machine Coding:
      • Day 1 — Bank account + Wallet :
      • Day 2 : RateLimiter + seat booking
      • Day 3 : Library Management + Classroom Attendance System
      • Day 4 : Bank account and notification system.
      • Day 5 : Key-value and payment processor.
      • Day 6 : Discount engine + Seat booking( in depth)
      • Day 7: Parking lot System :
    • Machine coding extended:
      • Day 7.5 — Parking Lot (Constraint-Heavy Variant)
      • Day 8 : Order + Payment System
      • Day 8 — Extension Round (Mid-Interview Change)
      • Day 9 — Ticket Booking with Expiry (Simulated Time)
      • Day 9 — Extension Round (Seat Lock Ownership + Forced Unlock)
    • SQL + DB Thinking course
      • Day 1 — Tables, Rows & Thinking in Data
      • Day 2 — Relationships & Foreign Keys (Thinking in Connections)
      • Day 3 — Filtering, Aggregation & Answering Data Questions
      • icon picker
        Day 4 — JOINs (Combining Tables the Right Way)
      • Day 5 — Subqueries, EXISTS & NOT EXISTS (Thinking in Layers)
      • Day 6 — Indexes, Constraints & How Databases Think
      • Day 7 — Full SQL Interview Simulation (SDE-1 Ready)
      • Extended Day 1 — Core Business Systems (Deep Practice)
      • Extended Day 2 — Booking & Platform Systems (High-Depth Practice)
      • Extended Day 3 — Content & Learning Platforms (Capstone Practice)
    • LLD: Java
      • DAY 1 — Core Foundations (SDE-2 Level)
      • DAY 2 — State, Time & Domain Correctness
      • DAY 3 — Extensibility & Failure Handling (SDE-2 Core Signal)
      • DAY 4 — Concurrency, Scheduling & Consistency (SDE-2 Critical)
      • DAY 5 — Workflow Design & Config-Driven Systems (SDE-2+)
      • DAY 6 — Eventing & Usage-Based Systems (Advanced SDE-2)
      • DAY 7 — Interview Simulation (Hard, Production-Grade)
      • Hidden Edge Cases Interviewers Expect (Day-wise)
      • Detailed Testing file:
    • Assignment:
      • If else Practice Set
Zeni Study
/
/
Day 4 — JOINs (Combining Tables the Right Way)
Share
Explore

Day 4 — JOINs (Combining Tables the Right Way)

⁠

🟢 Day 4 — JOINs (Combining Tables the Right Way)

🎯 Day 4 Outcome

By the end of Day 4, the student should be able to:
Understand why JOINs exist
Use INNER JOIN and LEFT JOIN correctly
Answer questions like:
“Users with no orders”
“Orders with user info”
Explain JOIN logic in plain English
Avoid the most common JOIN mistakes
JOINs are not hard. They are just matching rows across tables.
⁠

1️⃣ Why JOINs Exist (Teaching Script)

Start With This Question

“We stored users and orders in separate tables. How do we see user name with order amount?”
Answer:
We combine rows from two tables
Based on a common column
This operation is called a JOIN.
⁠

2️⃣ Tables We’ll Use Today

users (
user_id INT,
name VARCHAR(100),
email VARCHAR(100)
)

orders (
order_id INT,
user_id INT,
amount INT,
order_date DATE
)
⁠

3️⃣ INNER JOIN — Only Matching Rows

Mental Model (Very Important)

INNER JOIN = intersection
Only rows that exist in both tables are returned.
⁠

Syntax

SELECT columns
FROM table1
INNER JOIN table2
ON condition;
⁠

Example 1 — Orders with User Names

SELECT u.name, o.amount
FROM users u
INNER JOIN orders o
ON u.user_id = o.user_id;
Read in English:
“For each order, find the user whose user_id matches, and show name + amount”
⁠

What Happens Internally

For each row in orders:
Database looks for matching user_id in users
If found → row is returned
If not → row is skipped
⁠

4️⃣ LEFT JOIN — Keep Everything from Left Table

Mental Model

LEFT JOIN = everything from left table + matches from right
Even if there is no match, left table row stays.
⁠

Syntax

SELECT columns
FROM table1
LEFT JOIN table2
ON condition;
⁠

Example 2 — Users with Their Orders (If Any)

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id;
What this returns:
Users with orders → normal rows
Users without orders → amount = NULL
⁠

5️⃣ Most Asked Interview Question

❓ “Find users who have NOT placed any orders”

This is where LEFT JOIN shines.
⁠

Solution

SELECT u.user_id, u.name
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id
WHERE o.order_id IS NULL;
Read carefully:
LEFT JOIN keeps all users
Users without orders → order_id becomes NULL
We filter those rows
This question appears in almost every SQL interview.
⁠

6️⃣ INNER vs LEFT JOIN (Clear Comparison)

Case
INNER JOIN
LEFT JOIN
Users with orders
✅
✅
Users without orders
❌
✅
Orders without user
❌
❌
There are no rows in this table
⁠
⁠

7️⃣ Aliases (u, o) — Not Optional in Interviews

Bad:
users.user_id = orders.user_id
Good:
u.user_id = o.user_id
Why interviewers expect aliases:
Cleaner queries
Easier to read
Required in multi-join queries
⁠

8️⃣ JOIN + GROUP BY (Very Common)

Example — Total Spend Per User (Including Zero)

SELECT u.user_id, u.name, COALESCE(SUM(o.amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id
GROUP BY u.user_id, u.name;
Explain:
LEFT JOIN keeps all users
SUM(NULL) becomes NULL
COALESCE converts NULL → 0
⁠

9️⃣ Common JOIN Mistakes (Read Carefully)

❌ Forgetting ON condition ❌ Using WHERE instead of ON incorrectly ❌ Using INNER JOIN when LEFT JOIN needed ❌ Filtering NULLs incorrectly ❌ Forgetting GROUP BY columns
⁠

🔟 Practice Problems (DO THESE)

Problem 1 — Basic JOIN

Write SQL to get:
user name
order amount
order date
⁠

Problem 2 — Users with No Orders

Write SQL to find:
user_id
name
For users who never placed an order.
⁠

Problem 3 — Total Orders Per User

Include users with zero orders.
⁠

Problem 4 — High Spenders

Find users whose total order amount > 1000.
⁠

🔍 HINTS (Read Only If Stuck)

Combine tables using matching IDs
LEFT JOIN keeps all rows from left table
Use IS NULL to detect missing matches
Use GROUP BY when aggregating
⁠

✅ Solutions (Read After Attempting)

Solution — Problem 1

SELECT u.name, o.amount, o.order_date
FROM users u
INNER JOIN orders o
ON u.user_id = o.user_id;
⁠

Solution — Problem 2

SELECT u.user_id, u.name
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id
WHERE o.order_id IS NULL;
⁠

Solution — Problem 3

SELECT u.user_id, u.name, COUNT(o.order_id) AS order_count
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id
GROUP BY u.user_id, u.name;
⁠

Solution — Problem 4

SELECT u.user_id, u.name, SUM(o.amount) AS total_spent
FROM users u
INNER JOIN orders o
ON u.user_id = o.user_id
GROUP BY u.user_id, u.name
HAVING SUM(o.amount) > 1000;
⁠

🧠 Reflection Questions (Must Answer)

Why does LEFT JOIN return more rows than INNER JOIN?
Why is IS NULL used instead of = NULL?
When would INNER JOIN be wrong?
Why does interviewers love “users with no orders” question?
⁠

⭐ Day 4 Golden Rule

JOINs are just matching IDs, not magic.
⁠


Gallery
 
Want to print your doc?
This is not the way.
Try clicking the ··· in the right corner or using a keyboard shortcut (
CtrlP
) instead.