📌 SQL Subqueries CTEs

1️⃣ What is a Subquery?
A subquery is a query inside another query. It runs first and passes its result to the outer query.

Think like this 👇
> “First find something → then use it to filter or calculate something else”

Why Subqueries exist (business thinking)
Real questions like:
• Find customers who spent more than average
• Find products with highest sales
• Find employees earning more than their manager

These need one query’s result inside another query.

✅ Basic Subquery Structure
SELECT column
FROM text
WHERE column OPERATOR (
SELECT column
FROM text
);

Example Tables: orders
order_id | customer_id | amount
1 | 101 | 5000
2 | 102 | 8000
3 | 103 | 3000

2️⃣ Subquery in WHERE clause (Most Common)

🔹 Scenario: Find orders with amount greater than average order value

SELECT *
FROM orders
WHERE amount > (
SELECT AVG(amount)
FROM orders
);

What this query does
1. Inner query calculates average order amount
2. Outer query keeps only orders above that average

✅ Very common interview question

3️⃣ Subquery with IN
🔹 Scenario: Find customers who have placed at least one order

Tables: customers(customer_id, name) orders(customer_id)

SELECT name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
);

What this query does
• Inner query gets customers who ordered
• Outer query fetches their names

4️⃣ Subquery in SELECT clause

🔹 Scenario: Show each order with total number of orders
SELECT order_id, amount, (
SELECT COUNT(*)
FROM orders
) AS total_orders
FROM orders;

What this query does
• Inner query runs once
• Adds total order count to every row

⚠️ Use carefully — can be inefficient

5️⃣ Correlated Subquery (Important)

A correlated subquery depends on the outer query. It runs once per row.

🔹 Scenario: Find customers who spent more than their city’s average

Tables: customers(customer_id, city) orders(customer_id, amount)

SELECT c.customer_id
FROM customers c
WHERE (
SELECT AVG(o.amount)
FROM orders o
WHERE o.customer_id = c.customer_id
) > 5000;

What this query does
• For each customer
• Calculates their average spend
• Filters based on condition
⚠️ Powerful but slower on large data

6️⃣ Problems with Subqueries
❌ Hard to read
❌ Hard to debug
❌ Performance issues
❌ Nested logic becomes messy
👉 That’s why CTEs exist

7️⃣ What is a CTE (Common Table Expression)?

A CTE is a named temporary result.
It makes complex queries readable and reusable.

CTE Syntax
WITH cte_name AS (
SELECT ...
)
SELECT *
FROM cte_name;

8️⃣ Same Problem Solved Using CTE (Cleaner)

🔹 Find customers with total spend > 10,000

WITH customer_spend AS (
SELECT customer_id, SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id
)
SELECT *
FROM customer_spend
WHERE total_spend > 10000;

What this does
• First block calculates spend
• Second block filters results
• Very readable

9️⃣ CTE vs Subquery

• Readability: CTE is excellent, Subquery is poor
• Reusability: CTE is yes, Subquery is no
• Debugging: CTE is easy, Subquery is hard
• Performance: Both depend on usage

🔟 When to Use What?
Use Subquery when:
✔ Logic is small
✔ Used only once

Use CTE when:
✔ Logic is complex
✔ Multiple steps
✔ Interview or production query

Common Beginner Mistakes
❌ Writing very deep nested subqueries
❌ Using correlated subqueries unnecessarily
❌ Forgetting CTE scope (only valid for one query)

Interview Tip 💡
> Subqueries solve problems inside queries, while CTEs solve readability and maintainability.

Double Tap ♥️ For More