Skip to content
Zeni Study
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
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.