Subqueries in SQL

Excellent 👍
Now SUBQUERIES (IN, ANY, ALL) in the same crystal-clear pattern you want:

• Base tables

• Question

• SQL

• Actual OUTPUT table

• ✔ Key explanation

---

📘 Base Tables (Used for ALL Subquery Examples)

customers

customer_id	name	city
1	Amit	Patna
2	Neha	Delhi
3	Ravi	Mumbai
4	Pooja	Kolkata

---

suppliers

supplier_id	supplier_name	city
101	ABC Ltd	Delhi
102	XYZ Pvt	Mumbai

---

products

product_id	product_name	price
1	Pen	10
2	Book	50
3	Bag	300
4	Laptop	50000

---

supplier_products

sp_id	unit_price
1	20
2	100
3	1000

---

orders

order_id	order_amount
1	500
2	2000
3	7000

---

previous_orders

prev_id	total_amount
1	1000
2	3000
3	6000

---

🧾 Summary Table (Subquery Questions)

No.	Keyword	Question
1	IN	Customers from supplier cities
2	ANY	Products cheaper than any supplier product
3	ALL	Orders greater than all previous orders

---

1️⃣ IN (Subquery)

Question: Show customers who live in cities where suppliers are located.

SELECT *
FROM customers
WHERE city IN (
  SELECT city
  FROM suppliers
);

✅ Output

customer_id	name	city
2	Neha	Delhi
3	Ravi	Mumbai

✔ Subquery returns list: Delhi, Mumbai
✔ IN checks if city matches any value in list

---

2️⃣ ANY (Subquery)

Question: Show products whose price is less than any supplier product price.

SELECT *
FROM products
WHERE price < ANY (
  SELECT unit_price
  FROM supplier_products
);

🔍 Subquery Result

unit_price = {20, 100, 1000}
Minimum = 20

✅ Output

product_id	product_name	price
1	Pen	10

✔ < ANY means less than at least one value
✔ Equivalent to < MAX() logic

---

3️⃣ ALL (Subquery)

Question: Show orders whose amount is greater than all previous order amounts.

SELECT *
FROM orders
WHERE order_amount > ALL (
  SELECT total_amount
  FROM previous_orders
);

🔍 Subquery Result

total_amount = {1000, 3000, 6000}
Maximum = 6000

✅ Output

order_id	order_amount
3	7000

✔ > ALL means greater than every value
✔ Equivalent to > MAX() logic

---

🔥 IN vs ANY vs ALL (Exam Gold Table)

Keyword	Meaning	Easy Logic
IN	Matches any value	= OR OR OR
ANY	Compare with at least one	< MAX()
ALL	Compare with all values	> MAX() or < MIN()

---

🧠 One-Line Memory Trick

• IN → is it in the list?

• ANY → compare with one value

• ALL → compare with every value

---

⚠️ Exam Traps

• = ANY behaves like IN

• > ALL means greater than maximum

• ANY ≠ ALL

---

If you want next:

• Correlated subqueries

• EXISTS vs IN

• Subqueries in SELECT / FROM

• MCQs with output prediction

Just say 👍