Subqueries & Nested Queries

Subquery ও nested query

Read: ~30 min Medium 18 practice problems Live SQLite runner

1. What Is a Subquery?

A subquery (also called a nested query or inner query) is a complete SQL query placed inside another query, usually wrapped in parentheses. The outer query treats the subquery's result as if it were a value, a row, or even a small table — depending on context.

Subquery মানে এক query-র ভিতরে আরেকটি সম্পূর্ণ query — সাধারণত প্যারেনথিসিসের ভিতরে। বাইরের query সেই ভিতরের query-র ফলাফলকে কখনো একটি মান (scalar), কখনো এক row, আবার কখনো একটি ছোট table হিসেবে ব্যবহার করে।

Subqueries shine when a question is naturally two-step: first compute something (the average price, the latest order date, the set of active customers), then use that intermediate fact to filter or annotate. Without subqueries you would either need temp tables or an extra round-trip from the application — both slower and clumsier.

Three places a subquery can live (1) In the SELECT list — produces a scalar column for each outer row.
(2) In the FROM clause — used as a derived table.
(3) In the WHERE/HAVING clause — for filtering with IN, EXISTS, comparisons.

(১) SELECT-এর ভিতর — প্রতিটি row-র জন্য একটি মান।
(২) FROM-এর ভিতর — একটি অস্থায়ী table।
(৩) WHERE বা HAVING-এ — filter করার জন্য।

2. Scalar Subqueries — One Value, Reused

A scalar subquery returns exactly one row with one column — i.e., a single value. You can drop it anywhere SQL expects an expression: in a comparison, in SELECT, even in ORDER BY.

Scalar subquery এক row, এক column — মানে শুধু একটি মান ফেরত দেয়। এটি যেকোনো জায়গায় ব্যবহার করা যায় যেখানে SQL একটি মান আশা করে।

scalar.sql

-- Products priced strictly above the overall average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

The same scalar subquery in the SELECT list lets every row know the global average without repeating it in the application:

scalar_in_select.sql

SELECT name, price,
       (SELECT ROUND(AVG(price),2) FROM products) AS avg_all,
       price - (SELECT AVG(price) FROM products) AS delta
FROM products
ORDER BY delta DESC;

Scalar subquery rule If a scalar subquery accidentally returns more than one row, most engines raise a runtime error. SQLite is lenient and silently uses the first row — which is even more dangerous because the bug hides. Always make sure scalar subqueries either produce one row by construction (e.g., SELECT MAX(...)) or are constrained with LIMIT 1 + an explicit ORDER BY.

Scalar subquery একাধিক row ফিরিয়ে দিলে অনেক database error দেয়, কিন্তু SQLite নীরবে প্রথম row নেয় — যেটি বিপজ্জনক। তাই MAX, MIN, COUNT বা LIMIT 1 ORDER BY ... দিয়ে নিশ্চিত করুন এক-row-ই আসছে।

3. Row Subqueries & `IN` — Sets of Values

A subquery that returns one column with many rows is best paired with the IN operator. The outer row is kept if its value matches any value in the set.

একটি column-এ অনেক row ফেরত দেওয়া subquery-কে IN-এর সাথে ব্যবহার করা হয়। বাইরের row-র মান সেই সেটে থাকলে সেটি রাখা হয়।

in_subq.sql

-- Students who are enrolled in at least one course
SELECT name
FROM students
WHERE id IN (SELECT sid FROM enrolments);

NULL trap with NOT IN If the subquery returns even a single NULL, x NOT IN (subquery) evaluates to UNKNOWN, never TRUE — so the entire result set is empty. Either filter NULLs out (WHERE col IS NOT NULL) or use NOT EXISTS, which is immune.

NOT IN-এর ভিতরে subquery-তে একটি মাত্র NULL থাকলেও সম্পূর্ণ filter ব্যর্থ হয়ে যায় (কোনো row-ই আসে না)। NULL বাদ দিয়ে নিন, অথবা নিরাপদ NOT EXISTS ব্যবহার করুন।

not_in_trap.sql

-- WRONG: returns nothing because the subquery contains NULL
SELECT name FROM students
WHERE id NOT IN (SELECT sid FROM enrolments);

-- RIGHT: filter out NULLs first
SELECT name FROM students
WHERE id NOT IN (SELECT sid FROM enrolments WHERE sid IS NOT NULL);

4. Derived Tables — Subqueries in `FROM`

A subquery in the FROM clause is called a derived table. You give it an alias and treat it like any other table. Derived tables are essential when you need to filter or join the result of an aggregation.

FROM-এর ভিতরে subquery থাকলে তাকে derived table বলে — একটি অস্থায়ী table-এর মতো কাজ করে। Aggregation-এর ফলাফলের ওপর আবার filter বা join করতে এটি অপরিহার্য।

derived.sql

-- Customers whose total spend exceeds 500 BDT (e-commerce-style report)
SELECT c.name, t.total_tk
FROM (
        SELECT cid, SUM(amount) AS total_tk
        FROM orders
        GROUP BY cid
     ) t
JOIN customers c ON c.id = t.cid
WHERE t.total_tk > 500
ORDER BY t.total_tk DESC;

You could write the same query with HAVING SUM(amount) > 500 — and indeed for this exact problem that is cleaner. But derived tables generalize: you can join the aggregate to other tables, filter on multiple aggregates, and reuse the intermediate result.

CTE preview In Module 19 you'll learn the WITH name AS (...) syntax — Common Table Expressions. They are simply named derived tables that keep complex queries readable. You can rewrite the query above as WITH t AS (...) SELECT ....

পরবর্তী module-এ WITH দিয়ে CTE শিখবেন — derived table-এর নামকরণ করার একটি পরিচ্ছন্ন উপায়।

5. Correlated Subqueries — Per-Row Logic

An uncorrelated subquery can be evaluated once, independently of the outer query. A correlated subquery refers to a column from the outer query — so it must be re-evaluated for every outer row.

Uncorrelated subquery বাইরের কিছুর ওপর নির্ভর করে না — একবার চললেই হলো। Correlated subquery বাইরের row-এর কোনো column ব্যবহার করে — তাই প্রতিটি outer row-র জন্য আবার চালাতে হয়। এটি শক্তিশালী, কিন্তু ধীর হতে পারে।

correlated.sql

-- Most-expensive product in each category
SELECT name, cat, price
FROM products p
WHERE price = (
    SELECT MAX(price)
    FROM products
    WHERE cat = p.cat     -- references outer row
)
ORDER BY cat;

Aspect	Uncorrelated	Correlated
References outer row?	No	Yes
Evaluated	Once	Per outer row (logically)
Optimizer can cache	Yes	Sometimes — often rewritten as a JOIN
Typical use	Constant value (avg, max)	Per-row max, EXISTS

6. `EXISTS` & `NOT EXISTS` — The Right Way to Test for Matches

EXISTS (subquery) returns TRUE if the subquery would produce at least one row, FALSE otherwise. The columns the subquery selects are irrelevant — only "is there at least one row?" matters. By convention people write SELECT 1 inside.

EXISTS (subquery) জানায় subquery-তে অন্তত একটি row আসছে কি না। কোন column আসছে সেটা গুরুত্বপূর্ণ নয় — শুধু "অন্তত একটি row আছে কি?" — সেটাই দেখা হয়। তাই অভ্যাসগতভাবে SELECT 1 লেখা হয়।

exists.sql

-- Customers who have at least one order (a.k.a. active customers)
SELECT id, name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.cid = c.id
);

-- Customers with no orders — NULL-safe alternative to NOT IN
SELECT id, name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.cid = c.id
);

✅ `NOT EXISTS` (NULL-নিরাপদ)

Treats NULLs sanely — empty set means TRUE
Optimizer often rewrites as anti-join
Works for multi-column matches naturally

⚠️ `NOT IN` with NULL (সাবধান)

Single NULL in the set wipes out the whole result
Multi-column comparison is awkward
Easy to get wrong on first read

7. `ANY` and `ALL` — Quantifiers in SQL

The keywords ANY (synonym SOME) and ALL are quantifiers that combine a comparison operator with a subquery's set of values:

x > ANY (subquery) — true if x is greater than at least one value (i.e., greater than the minimum).
x > ALL (subquery) — true if x is greater than every value (i.e., greater than the maximum).
x = ANY (subquery) — exactly equivalent to x IN (subquery).
x <> ALL (subquery) — exactly equivalent to x NOT IN (subquery).

ANY মানে "কমপক্ষে একটির সাথে শর্ত পূরণ", আর ALL মানে "প্রত্যেকটির সাথে শর্ত পূরণ"। = ANY মূলত IN, আর <> ALL মূলত NOT IN।

any_all.sql

-- Products whose price exceeds EVERY Phone (i.e., the priciest of all phones)
SELECT name, price
FROM products
WHERE price > ALL (
    SELECT price FROM products WHERE cat = 'Phone'
);

-- Products cheaper than at least one Home product
SELECT name, cat, price
FROM products
WHERE price < ANY (
    SELECT price FROM products WHERE cat = 'Home'
);

Empty-set rules If the subquery returns zero rows: ANY is FALSE (no value satisfies anything), ALL is vacuously TRUE (every member of an empty set satisfies any property). This catches many learners by surprise.

Subquery খালি হলে ANY সর্বদা FALSE, কিন্তু ALL সর্বদা TRUE (vacuous truth)। এটি একটি ক্লাসিক bug-এর উৎস।

8. Pattern Cheat Sheet (এক নজরে)

Need	Pattern
Compare against a global aggregate	`WHERE x > (SELECT AVG(x) FROM t)`
Per-row max in a group	Correlated `WHERE x = (SELECT MAX(x) FROM t WHERE g = outer.g)`
Filter aggregated rows by another table	Derived table in `FROM`
Active / linked rows	`WHERE EXISTS (SELECT 1 FROM child WHERE child.fk = parent.id)`
Orphan / missing rows	`WHERE NOT EXISTS (...)`
Membership in a set	`WHERE x IN (SELECT k FROM t)`
Stronger than every member	`x > ALL (...)`

9. Practice Problems

18 problems building from scalar subqueries to correlated EXISTS patterns.

১৮টি অনুশীলনী — সহজ scalar থেকে শুরু করে correlated EXISTS পর্যন্ত। নিজে চেষ্টা করুন, তারপর প্রতিটি উত্তর সরাসরি ব্রাউজারে চালিয়ে দেখুন।

Find products priced strictly above the average price.

গড় দামের চেয়ে বেশি দামের পণ্য খুঁজুন।
✨ Show Answer
a1.sql
```
SELECT name,price FROM products
WHERE price > (SELECT AVG(price) FROM products);
```
Find customers who placed at least one order using IN.

IN দিয়ে যেসব customer অন্তত একটি order করেছে।
✨ Show Answer
a2.sql
```
SELECT name FROM customers
WHERE id IN (SELECT cid FROM orders);
```
Same query as #2, but with EXISTS.

২ নম্বর প্রশ্ন EXISTS দিয়ে।
✨ Show Answer
a3.sql
```
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.cid=c.id);
```
Find customers with NO orders using NOT EXISTS.

NOT EXISTS দিয়ে order-হীন customer।
✨ Show Answer
a4.sql
```
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.cid=c.id);
```
For each category, list the cheapest product (use a correlated subquery).

প্রতিটি category-এর সবচেয়ে সস্তা পণ্য — correlated subquery দিয়ে।
✨ Show Answer
a5.sql
```
SELECT name,cat,price FROM products p
WHERE price = (SELECT MIN(price) FROM products WHERE cat=p.cat);
```
Show each customer with their order count using a scalar subquery in SELECT.

SELECT-এ scalar subquery দিয়ে প্রতিটি customer-এর order সংখ্যা দেখান।
✨ Show Answer
a6.sql
```
SELECT name,
       (SELECT COUNT(*) FROM orders o WHERE o.cid=c.id) AS n
FROM customers c;
```
Use a derived table to list customers with total spend > 500.

Derived table দিয়ে ৫০০-এর বেশি ব্যয় করা customer।
✨ Show Answer
a7.sql
```
SELECT c.name, t.total
FROM (SELECT cid, SUM(amount) AS total FROM orders GROUP BY cid) t
JOIN customers c ON c.id=t.cid
WHERE t.total>500;
```
Find products priced higher than every 'Home' product.

প্রতিটি Home পণ্যের চেয়ে বেশি দামের পণ্য।
✨ Show Answer
a8.sql
```
SELECT name,cat,price FROM products
WHERE price > ALL (SELECT price FROM products WHERE cat='Home');
```

Why does NOT IN with NULL fail? Demonstrate.

NULL সহ NOT IN কেন ভুল হয় — দেখান।

✨ Show Answer

a9.sql

-- Returns 0 rows because NULL contaminates NOT IN
SELECT x FROM a WHERE x NOT IN (SELECT y FROM b);

-- Correct version
SELECT x FROM a WHERE x NOT IN (SELECT y FROM b WHERE y IS NOT NULL);

Find the product with the highest price (a single row).

সবচেয়ে দামি পণ্যটি (একটিই row)।
✨ Show Answer
a10.sql
```
SELECT name,price FROM products
WHERE price = (SELECT MAX(price) FROM products);
```
Note: ties are kept (here both B and C). To get exactly one row, switch to ORDER BY price DESC LIMIT 1.
Find departments where every employee earns more than 30000 BDT.

যেসব বিভাগে প্রতিটি কর্মচারী ৩০০০০ টাকার বেশি পায়।
✨ Show Answer
a11.sql
```
SELECT DISTINCT dept FROM emp e1
WHERE NOT EXISTS (
    SELECT 1 FROM emp e2
    WHERE e2.dept=e1.dept AND e2.salary<=30000
);
```
List students whose grade in CSE101 equals the best grade in that course.

CSE101-এর সর্বোচ্চ grade পাওয়া student-রা।
✨ Show Answer
a12.sql
```
SELECT sid,gpa FROM enrolments
WHERE ccode='CSE101'
  AND gpa = (SELECT MAX(gpa) FROM enrolments WHERE ccode='CSE101');
```
Find books that have been borrowed at least once (NOT EXISTS / EXISTS practice).

যে বইগুলো অন্তত একবার ধার নেওয়া হয়েছে।
✨ Show Answer
a13.sql
```
SELECT title FROM books b
WHERE EXISTS (SELECT 1 FROM loans l WHERE l.book_id=b.id);
```
Show each order with the percentage it contributes to the day's revenue.

প্রতিটি order সেদিনের আয়ের কত শতাংশ — তা দেখান।
✨ Show Answer
a14.sql
```
SELECT id,d,amount,
       ROUND(100.0*amount/(SELECT SUM(amount) FROM orders o2 WHERE o2.d=o.d),1) AS pct
FROM orders o
ORDER BY d,pct DESC;
```
Find orders larger than the customer's own average order amount.

যে order সেই customer-এর গড়ের চেয়েও বেশি — সেগুলো খুঁজুন।
✨ Show Answer
a15.sql
```
SELECT id,cid,amount FROM orders o
WHERE amount > (SELECT AVG(amount) FROM orders WHERE cid=o.cid);
```
Show the second-highest distinct price (use a subquery).

দ্বিতীয় সর্বোচ্চ ভিন্ন দাম খুঁজুন।
✨ Show Answer
a16.sql
```
SELECT MAX(price) AS second_max
FROM products
WHERE price < (SELECT MAX(price) FROM products);
```
List students who have taken EVERY course offered (relational division).

প্রতিটি course-ই নিয়েছে এমন student খুঁজুন (relational division)।
✨ Show Answer
a17.sql
```
SELECT DISTINCT sid FROM enrolments e1
WHERE NOT EXISTS (
    SELECT 1 FROM courses c
    WHERE NOT EXISTS (
        SELECT 1 FROM enrolments e2
        WHERE e2.sid=e1.sid AND e2.ccode=c.code
    )
);
```
The classic "double NOT EXISTS" trick — read it as: "no course exists that the student hasn't taken."
Why does SELECT * FROM t WHERE x > ALL (SELECT y FROM empty_table) return everything? Explain in 2-3 sentences.

খালি set-এ > ALL কেন সব row রাখে — ব্যাখ্যা।

✨ Show Answer

Answer: x > ALL S means "for every element y in S, x > y." When S is empty, that "for every" condition is vacuously true — there are no elements to fail it. So the comparison is TRUE for every outer row, and you get everything.

ALL-এর শর্ত হলো "সবার সাথে শর্ত সত্য হবে।" set খালি হলে কাউকেই পরীক্ষা করতে হয় না — তাই শর্ত তখন vacuously TRUE।

Summary — Module 17

A subquery is a query inside a query. Scalar subqueries return one value; row/multi-row subqueries pair with IN, ANY, ALL; derived tables live in FROM; correlated subqueries reference the outer row and run per-row. For "does a match exist?" questions, prefer EXISTS/NOT EXISTS — they are NULL-safe and play nicely with the optimizer. Watch out for the NOT IN + NULL trap, and the vacuous-truth behaviour of ALL over an empty set.

Subquery মানে এক query-র ভিতরে আরেকটি query — ফলাফল হিসেবে একটি মান, একটি column বা একটি table হতে পারে। Correlated subquery বাইরের প্রতিটি row-র জন্য চলে; EXISTS/NOT EXISTS NULL-নিরাপদ ও দ্রুত। NOT IN-এ NULL ধোঁকা দেয়, আর খালি set-এর ওপর ALL সর্বদা TRUE — মনে রাখবেন।

Next Module → Set Operations — UNION, INTERSECT, EXCEPT।

Previous Next: Set Operations

1. What Is a Subquery?

2. Scalar Subqueries — One Value, Reused

3. Row Subqueries & IN — Sets of Values

4. Derived Tables — Subqueries in FROM

5. Correlated Subqueries — Per-Row Logic

6. EXISTS & NOT EXISTS — The Right Way to Test for Matches

✅ NOT EXISTS (NULL-নিরাপদ)

⚠️ NOT IN with NULL (সাবধান)

7. ANY and ALL — Quantifiers in SQL

8. Pattern Cheat Sheet (এক নজরে)

9. Practice Problems

Summary — Module 17

3. Row Subqueries & `IN` — Sets of Values

4. Derived Tables — Subqueries in `FROM`

6. `EXISTS` & `NOT EXISTS` — The Right Way to Test for Matches

✅ `NOT EXISTS` (NULL-নিরাপদ)

⚠️ `NOT IN` with NULL (সাবধান)

7. `ANY` and `ALL` — Quantifiers in SQL