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
      • Day 4 — JOINs (Combining Tables the Right Way)
      • icon picker
        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 5 — Subqueries, EXISTS & NOT EXISTS (Thinking in Layers)
Share
Explore

Day 5 — Subqueries, EXISTS & NOT EXISTS (Thinking in Layers)

⁠

🟢 Day 5 — Subqueries, EXISTS & NOT EXISTS (Thinking in Layers)

🎯 Day 5 Outcome

By the end of Day 5, the student should be able to:
Understand why subqueries exist
Use subqueries without fear
Know when to use JOIN vs subquery
Correctly use EXISTS and NOT EXISTS
Solve classic interview SQL problems
Most SQL interviews are not hard. They are just nested thinking problems.
⁠

1️⃣ What Is a Subquery? (Teaching Script)

Simple Definition

A subquery is a query inside another query.
Think of it like:
“First answer a small question, then use that answer to solve a bigger one.”
⁠

Real-Life Analogy

“Find employees whose salary is greater than the average salary.”
Steps:
Find average salary
Compare each employee salary with it
That’s exactly what a subquery does.
⁠

2️⃣ Dataset Used Today

employees (
emp_id INT,
name VARCHAR(100),
salary INT,
department_id INT
)

departments (
department_id INT,
department_name VARCHAR(100)
)
⁠

3️⃣ Subquery in WHERE Clause (Most Common)

Example — Salary Above Average

SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
Read in English:
“Give me employees whose salary is greater than the average salary”
⁠

Why This Is Interview-Favorite

Shows thinking
Shows layering
Shows correctness
⁠

4️⃣ Subquery Returning Multiple Rows

Example — Employees in Engineering Department

SELECT name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE department_name = 'Engineering'
);
Inner query returns department IDs Outer query filters employees using those IDs
⁠

5️⃣ Subquery vs JOIN (Interview Question)

JOIN Version

SELECT e.name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'Engineering';

Which Is Better?

Answer:
Both are correct
JOIN is usually faster
Subquery is sometimes clearer
Interviewers care more about correctness & clarity than micro-optimizations.
⁠

6️⃣ EXISTS — Checking Presence (Very Important)

Key Idea

EXISTS checks whether at least one row exists It does NOT care about values.
⁠

Example — Users Who Placed At Least One Order

SELECT u.user_id, u.name
FROM users u
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.user_id
);
Read:
“Keep user if there exists at least one matching order”
⁠

Why SELECT 1?

Value doesn’t matter
EXISTS stops at first match
Very efficient
⁠

7️⃣ NOT EXISTS — Opposite Logic

Example — Users with No Orders

SELECT u.user_id, u.name
FROM users u
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.user_id
);
This is often cleaner than LEFT JOIN + IS NULL.
⁠

8️⃣ Classic Interview Question — Second Highest Salary

Query

SELECT MAX(salary)
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);
Explain:
Inner query finds highest salary
Outer query finds highest salary less than that
⁠

9️⃣ Correlated Subqueries (Important Concept)

A correlated subquery refers to outer query.

Example — Employees Earning More Than Dept Average

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);
Inner query runs per row of outer query.
⁠

🔟 Common Mistakes (Read Carefully)

❌ Using = instead of IN ❌ Forgetting correlation condition ❌ Using subquery when JOIN is simpler ❌ Assuming subqueries are always slow
⁠

📝 Practice Problems (DO THESE)

Problem 1 — Salary Problems

Employees earning more than average
Employees earning highest salary
Employees earning second highest salary
⁠

Problem 2 — User & Orders

Users with at least one order
Users with no orders
Users whose total order amount > 2000
⁠

Problem 3 — Departments

Departments with no employees
Employees in departments with more than 5 employees
⁠

🔍 HINTS (Read Only If Stuck)

Use EXISTS for presence checks
Use subquery when logic feels “two-step”
EXISTS ignores actual values
Correlated subquery references outer table
⁠

✅ Solutions (Read After Attempting)

Solution — Problem 1

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
⁠

Solution — Problem 2

SELECT u.*
FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);

SELECT u.*
FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);

SELECT user_id
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 2000;
⁠

Solution — Problem 3

SELECT d.department_name
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);

SELECT department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;
⁠

🧠 Reflection Questions (Must Answer)

When is EXISTS better than JOIN?
Why does EXISTS use SELECT 1?
Why correlated subqueries can be slower?
How would you explain subquery to a beginner?
⁠

⭐ Day 5 Golden Rule

If a question sounds like two questions, you probably need a subquery.
⁠


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.