October 1, 2026

MySQL Subqueries, Nested Queries & Advanced SELECT Statements – Complete Guide for Beginners

Introduction

MySQL mein basic SELECT queries seekhne ke baad next important step hai Subqueries, Nested Queries aur Advanced SELECT Statements ko samajhna.

Real-world applications mein humein kai baar aisi query likhni padti hai jahan ek query ka result doosri query ke andar use hota hai.

For example:

• Average salary se zyada salary wale employees find karna.
• Sabse expensive product find karna.
• Aise customers find karna jinhone order place kiya hai.
• Aise products find karna jo kisi specific category ke average price se expensive hain.
• EXISTS ke through check karna ki related record exist karta hai ya nahi.
• Ek query ke result ko temporary table ki tarah use karna.

In sab situations mein Subqueries bahut useful hoti hain.

Is tutorial mein hum beginner-friendly practical examples ke saath seekhenge:

• Subquery kya hoti hai?
• Nested Query kya hoti hai?
• Subquery in WHERE
• Subquery with IN
• Subquery with =
• Subquery in SELECT
• Subquery in FROM
• EXISTS
• NOT EXISTS
• Correlated Subquery
• Multiple-level Nested Queries
• Advanced SELECT
• Real-world e-commerce examples
• Common mistakes
• Best practices
• Practice exercises

---

1. Subquery Kya Hai?

Subquery ek SQL query hoti hai jo kisi doosri SQL query ke andar likhi jaati hai.

Simple words mein:

Query ke andar Query = Subquery

Example:

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

Yahan:

SELECT AVG(salary)
FROM employees

inner query hai.

Aur:

SELECT *
FROM employees
WHERE salary > (...)

outer query hai.

---

2. Subquery Ka Basic Structure

General structure:

SELECT ...
FROM ...
WHERE column operator (
    SELECT ...
    FROM ...
);

Example:

SELECT *
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Pehle inner query average price calculate karegi.

Phir outer query un products ko return karegi jinki price average se zyada hai.

---

3. Subquery Ko Samajhne Ka Easy Example

Suppose products table mein data hai:

id	product_name	price
1	Laptop	60000
2	Mouse	1000
3	Keyboard	2500
4	Monitor	15000
5	Phone	30000

Average price:

SELECT AVG(price)
FROM products;

Suppose result:

21700

Ab humein average se expensive products chahiye:

SELECT *
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

Result:

id	product_name	price
1	Laptop	60000
5	Phone	30000

---

4. Inner Query Aur Outer Query

Subquery ko generally do parts mein samajhna easy hota hai.

Inner Query

SELECT AVG(price)
FROM products;

Ye value calculate karti hai.

Outer Query

SELECT *
FROM products
WHERE price > (...);

Ye inner query ke result ka use karti hai.

Flow:

Inner Query
     ↓
Result
     ↓
Outer Query
     ↓
Final Result

---

5. Subquery With =

Jab subquery exactly ek value return karti hai, to = use kar sakte hain.

Example:

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

Ye highest salary wale employee ko find karega.

Suppose salaries:

id	name	salary
1	Rahul	40000
2	Amit	55000
3	Priya	70000

Query:

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

Result:

id	name	salary
3	Priya	70000

---

6. MAX() Ke Saath Subquery

Sabse expensive product find karna:

SELECT *
FROM products
WHERE price = (
    SELECT MAX(price)
    FROM products
);

Yahan:

SELECT MAX(price)
FROM products

highest price return karegi.

Outer query us price wale product ko find karegi.

---

7. MIN() Ke Saath Subquery

Sabse cheap product:

SELECT *
FROM products
WHERE price = (
    SELECT MIN(price)
    FROM products
);

Ye minimum price wale product ko return karega.

---

8. AVG() Ke Saath Subquery

Average se zyada salary:

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

Average se kam salary:

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

---

9. Subquery With IN

Agar subquery multiple rows return karti hai, to IN bahut useful hota hai.

Example:

Suppose:

users

table:

id	name
1	Rahul
2	Amit
3	Priya

Aur:

orders

table:

order_id	user_id	amount
101	1	5000
102	3	3000

Humein un users ko find karna hai jinhone order kiya hai.

SELECT *
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
);

Inner query:

SELECT user_id
FROM orders;

Result:

1
3

Outer query effectively:

SELECT *
FROM users
WHERE id IN (1, 3);

Result:

id	name
1	Rahul
3	Priya

---

10. NOT IN With Subquery

Aise users find karein jinhone koi order nahi kiya:

SELECT *
FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM orders
);

Ye conceptually un users ko return karega jinke IDs orders mein present nahi hain.

Important Note

NOT IN ke saath NULL values unexpected results de sakti hain.

Isliye real applications mein anti-join logic ke liye NOT EXISTS often safer and clearer choice ho sakta hai.

---

11. Subquery With > Operator

Average salary se zyada salary:

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

Similarly:

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

Aur:

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

---

12. Subquery With BETWEEN

Suppose humein average salary aur maximum salary ke beech employees chahiye.

SELECT *
FROM employees
WHERE salary BETWEEN
(
    SELECT AVG(salary)
    FROM employees
)
AND
(
    SELECT MAX(salary)
    FROM employees
);

Yahan do scalar subqueries use ho rahi hain.

---

13. Subquery in WHERE Clause

Ye subquery ka sabse common use case hai.

Example:

SELECT *
FROM products
WHERE category_id = (
    SELECT id
    FROM categories
    WHERE category_name = 'Electronics'
);

Yahan inner query category ka ID find karegi.

Outer query us category ke products find karegi.

---

14. Real-World E-Commerce Example

Suppose categories:

id	category_name
1	Electronics
2	Clothing
3	Books

Products:

id	product_name	category_id	price
1	Laptop	1	60000
2	T-Shirt	2	1000
3	Phone	1	30000
4	Book	3	500

Electronics products:

SELECT *
FROM products
WHERE category_id = (
    SELECT id
    FROM categories
    WHERE category_name = 'Electronics'
);

Result:

id	product_name	category_id	price
1	Laptop	1	60000
3	Phone	1	30000

---

15. Subquery in SELECT

Subquery sirf WHERE mein hi nahi, SELECT clause ke andar bhi use ho sakti hai.

Example:

SELECT
    name,
    salary,
    (
        SELECT AVG(salary)
        FROM employees
    ) AS average_salary
FROM employees;

Possible result:

name	salary	average_salary
Rahul	40000	55000
Amit	55000	55000
Priya	70000	55000

Yahan average salary har row ke saath show ho rahi hai.

---

16. Employee Salary vs Average Salary

Ab hum individual salary aur average salary dono compare kar sakte hain:

SELECT
    name,
    salary,
    (
        SELECT AVG(salary)
        FROM employees
    ) AS average_salary,
    salary - (
        SELECT AVG(salary)
        FROM employees
    ) AS difference
FROM employees;

Possible result:

name	salary	average_salary	difference
Rahul	40000	55000	-15000
Amit	55000	55000	0
Priya	70000	55000	15000

---

17. Subquery in FROM

Subquery ko FROM clause mein bhi use kar sakte hain.

Isse derived table kaha jaata hai.

Example:

SELECT *
FROM (
    SELECT
        name,
        salary
    FROM employees
    WHERE salary > 50000
) AS high_salary_employees;

Inner query:

SELECT
    name,
    salary
FROM employees
WHERE salary > 50000;

Ye result ek temporary result set ki tarah outer query ko diya jaata hai.

---

18. Derived Table Example

Suppose:

name	salary
Rahul	40000
Amit	55000
Priya	70000
Neha	80000

Query:

SELECT *
FROM (
    SELECT name, salary
    FROM employees
    WHERE salary > 50000
) AS high_salary;

Result:

name	salary
Amit	55000
Priya	70000
Neha	80000

---

19. FROM Subquery Mein Alias Important Hai

MySQL mein derived table ko alias dena important hai.

Correct:

SELECT *
FROM (
    SELECT *
    FROM employees
) AS temp;

Better practice:

SELECT *
FROM (
    SELECT name, salary
    FROM employees
) AS employee_data;

Readable aliases use karna recommended hai.

---

20. EXISTS Kya Hai?

EXISTS check karta hai ki subquery at least one row return karti hai ya nahi.

Basic syntax:

SELECT ...
FROM table1
WHERE EXISTS (
    SELECT ...
    FROM table2
    WHERE ...
);

Simple meaning:

"Kya related record exist karta hai?"

---

21. EXISTS Ka Real-World Example

Aise users find karein jinhone at least ek order place kiya hai:

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

Agar user ke liye order exist karta hai, user result mein aayega.

---

22. SELECT 1 Kyu Use Karte Hain?

EXISTS ke andar humein actual columns return nahi karne hote.

Isliye:

SELECT 1

common pattern hai.

Example:

WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
)

Yahan important part ye hai ki matching row exist karti hai ya nahi.

---

23. EXISTS vs IN

Dono similar problems solve kar sakte hain, but conceptually different hain.

IN

SELECT *
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
);

Meaning:

User ID returned values ke set mein hai ya nahi?

EXISTS

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

Meaning:

Kya is user ke liye related order exist karta hai?

Performance automatically IN ya EXISTS mein se kisi ek ke favour mein nahi hoti; MySQL optimizer query ko transform bhi kar sakta hai. Actual workload par EXPLAIN se verify karna best hai.

---

24. NOT EXISTS

Aise users find karein jinhone koi order nahi kiya:

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

Ye anti-match logic ke liye very useful pattern hai.

---

25. EXISTS vs NOT EXISTS

EXISTS

WHERE EXISTS (...)

Meaning:

Related record available hai.

NOT EXISTS

WHERE NOT EXISTS (...)

Meaning:

Related record available nahi hai.

Real-world examples:

EXISTS
→ Customers who placed orders

NOT EXISTS
→ Customers who never placed orders

---

26. Correlated Subquery Kya Hai?

Correlated subquery wo subquery hoti hai jo outer query ki current row ko reference karti hai.

Example:

SELECT
    e.name,
    e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

Yahan inner query outer query ke:

e.department_id

ko reference kar rahi hai.

Isliye ye correlated subquery hai.

---

27. Correlated Subquery Ko Easy Way Mein Samjhein

Suppose departments:

IT
HR
Sales

Har department ka average salary alag ho sakta hai.

Humein chahiye:

Har department ke average se zyada salary wale employees.

Query:

SELECT
    e.name,
    e.department_id,
    e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

Inner query har outer employee ke department ke according average calculate karne ke concept ko represent karti hai.

---

28. Correlated Subquery vs Normal Subquery

Normal Subquery

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

Inner query outer row par depend nahi karti.

Correlated Subquery

SELECT *
FROM employees e
WHERE salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

Inner query outer row ke department_id par depend karti hai.

---

29. Multiple-Level Nested Query

Ek subquery ke andar doosri subquery bhi ho sakti hai.

Example:

SELECT *
FROM employees
WHERE department_id = (
    SELECT id
    FROM departments
    WHERE location_id = (
        SELECT id
        FROM locations
        WHERE city = 'Delhi'
    )
);

Flow:

Outer Query
     ↓
Department Query
     ↓
Location Query
     ↓
Final Result

Ye nested subqueries ka example hai.

---

30. Nested Query Ka Real-World Example

Suppose company structure:

locations
    ↓
departments
    ↓
employees

Agar humein Delhi location ke departments ke employees find karne hain:

SELECT *
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE location_id IN (
        SELECT id
        FROM locations
        WHERE city = 'Delhi'
    )
);

Ye multiple levels par data filter kar raha hai.

---

31. Subquery With ALL

ALL ka use tab hota hai jab comparison ko subquery ke har returned value ke against satisfy karna ho.

Example:

SELECT *
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = 2
);

Meaning:

Aise employees find karo jinki salary department 2 ke har employee ki salary se greater hai.

Practical cases mein MAX() ke saath equivalent logic kabhi-kabhi simpler ho sakta hai.

---

32. Subquery With ANY

ANY ka meaning hota hai ki comparison subquery ke at least one value ke liye true ho.

Example:

SELECT *
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department_id = 2
);

Meaning:

Salary department 2 ke kam se kam ek employee ki salary se zyada ho.

---

33. ANY vs ALL

Operator	Meaning
ANY	At least one value ke against condition true
ALL	Every returned value ke against condition true

Simple memory trick:

ANY
↓
At least ONE

ALL
↓
EVERY ONE

---

34. Subquery With UPDATE

Subqueries sirf SELECT ke saath nahi, UPDATE mein bhi use ho sakti hain.

Example:

Suppose average salary se kam salary wale employees ko salary increase deni hai.

Conceptually:

UPDATE employees
SET salary = salary * 1.10
WHERE salary < (
    SELECT AVG(salary)
    FROM employees
);

Important: MySQL mein target table ko directly subquery mein reference karne wali kuch UPDATE patterns restrictions/errors de sakti hain. Production use mein derived-table rewrite ya alternative approach ki zarurat pad sakti hai.

For learning, core idea ye hai ki subquery ka result UPDATE ke filter ko drive kar sakta hai.

---

35. Subquery With DELETE

Example:

Aise users delete karne hain jinhone kabhi order nahi kiya.

Conceptually:

DELETE FROM users
WHERE id NOT IN (
    SELECT user_id
    FROM orders
);

Lekin real database mein foreign keys aur NULL handling ko carefully consider karna chahiye.

NOT EXISTS pattern often safer:

DELETE FROM users
WHERE NOT EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.user_id = users.id
);

Production database mein destructive query execute karne se pehle backup/transaction strategy aur affected rows verify karna important hai.

---

36. Advanced SELECT With CASE

Subqueries ke saath CASE bhi powerful combination bana sakta hai.

Example:

SELECT
    name,
    salary,
    CASE
        WHEN salary > (
            SELECT AVG(salary)
            FROM employees
        )
        THEN 'Above Average'
        ELSE 'Average or Below'
    END AS salary_status
FROM employees;

Possible result:

name	salary	salary_status
Rahul	40000	Average or Below
Amit	55000	Average or Below
Priya	70000	Above Average

---

37. Advanced SELECT With Calculated Columns

Example:

SELECT
    product_name,
    price,
    price * 1.18 AS price_with_tax
FROM products;

Yahan price_with_tax calculated column hai.

---

38. Advanced SELECT With CONCAT

User ka full name:

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

Result:

full_name
Rahul Sharma
Amit Verma
Priya Singh

---

39. Advanced SELECT With COALESCE

COALESCE() ka use NULL ke liye fallback value dene mein kiya ja sakta hai.

Example:

SELECT
    name,
    COALESCE(phone, 'Not Available') AS phone
FROM users;

Agar phone NULL hai, output:

Not Available

hoga.

---

40. Advanced SELECT With DISTINCT

Duplicate values remove karne ke liye:

SELECT DISTINCT city
FROM users;

Result:

city
Delhi
Mumbai
Raipur

---

41. Advanced SELECT With GROUP BY

Example:

SELECT
    department_id,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

Ye har department ka average salary return karega.

---

42. GROUP BY + HAVING + Subquery

Ab ek advanced example:

SELECT
    department_id,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary)
    FROM employees
);

Meaning:

Sirf un departments ko show karo jinka average salary company ke overall average salary se greater hai.

Ye real-world reporting queries mein useful concept hai.

---

43. Subquery With ORDER BY and LIMIT

Suppose highest-paid employee find karna hai:

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 1;

Is problem ko subquery se bhi solve kar sakte hain:

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

Dono approaches useful hain, but semantics differ kar sakti hain.

For example, MAX() approach same maximum salary wale multiple employees ko return kar sakti hai, while LIMIT 1 sirf ek row return karega.

---

44. Second Highest Salary

Second highest distinct salary find karne ka common approach:

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
);

Example salaries:

80000
70000
70000
50000

Result:

70000

Yahan DISTINCT ki zarurat nahi because MAX() already duplicate 70000 values ko ek scalar result mein reduce kar deta hai.

---

45. Third Highest Salary

Third highest distinct salary ka ek approach:

SELECT MAX(salary) AS third_highest_salary
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
    WHERE salary < (
        SELECT MAX(salary)
        FROM employees
    )
);

Ye nested subquery ka example hai.

---

46. Top N Records

Agar simply top 5 highest salaries chahiye:

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 5;

Is case mein unnecessary subquery likhne ki zarurat nahi.

Important lesson:

Har problem ke liye subquery use karna zaroori nahi hai.

---

47. Subquery vs JOIN

Many beginners confuse:

SUBQUERY

and:

JOIN

Suppose users aur orders related tables hain.

Subquery:

SELECT *
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
);

JOIN:

SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o
    ON u.id = o.user_id;

Dono approaches related users ko find kar sakti hain.

Which one is better depends on the specific query, readability, data shape and execution plan.

---

48. JOIN Kab Use Karein?

Agar humein multiple tables ke columns ek saath chahiye:

SELECT
    u.name,
    o.order_id,
    o.total_amount
FROM users u
INNER JOIN orders o
    ON u.id = o.user_id;

JOIN natural choice hai.

---

49. Subquery Kab Useful Hai?

Subquery useful hoti hai jab:

• Ek query ka result doosri query ko filter kare.
• Comparison kisi calculated value ke saath karna ho.
• EXISTS se related record check karna ho.
• Derived result set banana ho.
• Query ko logical steps mein break karna ho.

Example:

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

---

50. Subquery vs JOIN – Simple Difference

Requirement	Common Approach
Related tables ka data ek saath chahiye	JOIN
Ek query ka result filter ke liye chahiye	Subquery
Related record exist karta hai ya nahi	EXISTS
Multiple returned values se compare karna	IN
Intermediate result ko table ki tarah use karna	Derived table
Complex relationship traversal	JOIN / Subquery, depending on case

Ye hard rule nahi hai. Same problem ko multiple SQL techniques se solve kiya ja sakta hai.

---

51. Common Beginner Mistake: Multiple Rows With =

Ye query problem create kar sakti hai:

SELECT *
FROM employees
WHERE department_id = (
    SELECT id
    FROM departments
);

Agar inner query multiple IDs return karti hai, = appropriate nahi hoga.

Aise case mein:

WHERE department_id IN (
    SELECT id
    FROM departments
);

use karna chahiye.

---

52. = vs IN

Single value

WHERE id = (
    SELECT id
    FROM users
    WHERE email = 'rahul@gmail.com'
);

Inner query ideally one value return kare.

Multiple values

WHERE id IN (
    SELECT user_id
    FROM orders
);

Inner query multiple values return kar sakti hai.

Easy rule:

One value
↓
=

Multiple values
↓
IN

---

53. Common Beginner Mistake: NULL With NOT IN

Suppose subquery:

SELECT user_id
FROM orders;

mein NULL bhi return kar sakta hai.

Then:

WHERE id NOT IN (...)

unexpected results de sakta hai because SQL uses three-valued logic.

Safer anti-match pattern:

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

---

54. Common Beginner Mistake: Correlated Subquery Ko Overuse Karna

Correlated subquery powerful hai, but large datasets par repeatedly evaluated logic expensive ho sakta hai.

Example:

SELECT *
FROM employees e
WHERE salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

Ye logically correct ho sakti hai, lekin performance actual dataset aur optimizer plan par depend karegi.

Complex query ke liye:

EXPLAIN

use karein.

---

55. Common Beginner Mistake: SELECT * Everywhere

Beginners commonly likhte hain:

SELECT *
FROM users;

Learning ke liye okay hai, but production code mein required columns specify karna often better hai.

Example:

SELECT
    id,
    name,
    email
FROM users;

Benefits:

• Less data transfer
• Clear intent
• Better maintainability
• Application/API responses more controlled

---

56. Common Beginner Mistake: Subquery Ko Needlessly Complex Banana

Agar simple query kaam kar rahi hai:

SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 5;

to unnecessary nested queries avoid karein.

Readable SQL generally easier to maintain aur debug hota hai.

---

57. Common Beginner Mistake: Alias Na Dena

Bad:

SELECT *
FROM (
    SELECT name, salary
    FROM employees
);

Better:

SELECT *
FROM (
    SELECT name, salary
    FROM employees
) AS employee_data;

Meaningful alias use karein.

---

58. Best Practices for Subqueries

1. Query Ko Logical Rakhein

Complexity ke liye subquery use karein, sirf complexity badhane ke liye nahi.

2. Correct Operator Choose Karein

=      → one value
IN     → multiple values
EXISTS → existence check

3. Correlated Subquery Carefully Use Karein

Large datasets par EXPLAIN se query plan inspect karein.

4. JOIN Ko Ignore Na Karein

Agar multiple tables ka data combine karna hai, JOIN often clearer ho sakta hai.

5. NULL Handling Samjhein

Especially:

NOT IN

ke saath.

6. Meaningful Aliases Use Karein

Example:

e
o
u
employee_data

7. Production Queries Test Karein

Real dataset par:

EXPLAIN

use karein.

---

59. Real-World E-Commerce Query

Suppose humare paas:

Users

id	name
1	Rahul
2	Amit
3	Priya

Orders

order_id	user_id	total_amount
101	1	5000
102	1	3000
103	3	10000

Humein un users ko find karna hai jinhone 5000 se zyada total order amount wala at least one order kiya hai.

Using EXISTS:

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
      AND o.total_amount > 5000
);

Result:

id	name
3	Priya

---

60. Customers With Orders Above Average

Suppose humein aise customers find karne hain jinka koi order company ke average order amount se greater hai.

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
      AND o.total_amount > (
          SELECT AVG(total_amount)
          FROM orders
      )
);

Yahan nested logic hai:

Users
 ↓
Orders
 ↓
Average Order Amount

---

61. Products Above Category Average

Suppose products table:

product_id
product_name
category_id
price

Humein har category ke average se expensive products find karne hain:

SELECT
    p.product_id,
    p.product_name,
    p.category_id,
    p.price
FROM products p
WHERE p.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category_id = p.category_id
);

Ye correlated subquery ka real-world example hai.

---

62. Users With No Orders – Recommended Pattern

SELECT
    u.id,
    u.name,
    u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Ye query:

Un users ko find karegi jinke liye koi matching order record exist nahi karta.

---

63. Advanced SELECT – Multiple Conditions

Example:

SELECT
    name,
    salary,
    department_id
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
)
AND department_id IN (
    SELECT id
    FROM departments
    WHERE status = 'active'
);

Yahan:

• First subquery average salary calculate karti hai.
• Second subquery active departments find karti hai.
• Outer query dono conditions apply karti hai.

---

64. Advanced SELECT – ORDER BY Calculated Value

Example:

SELECT
    product_name,
    price,
    price * 0.90 AS discounted_price
FROM products
ORDER BY discounted_price ASC;

Ye calculated price ke according products sort karega.

---

65. Advanced SELECT – CASE + Aggregate

Example:

SELECT
    department_id,
    AVG(salary) AS avg_salary,
    CASE
        WHEN AVG(salary) >= 70000 THEN 'High'
        WHEN AVG(salary) >= 50000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level
FROM employees
GROUP BY department_id;

Ye report-style SQL ka useful example hai.

---

66. Advanced SELECT – LIMIT and OFFSET

Pagination ke liye:

SELECT *
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;

Next page:

SELECT *
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;

Concept:

Page 1 → OFFSET 0
Page 2 → OFFSET 10
Page 3 → OFFSET 20

Large datasets par deep OFFSET pagination expensive ho sakti hai; production systems mein keyset/cursor pagination bhi consider ki ja sakti hai.

---

67. Advanced SELECT – DISTINCT + ORDER BY

Example:

SELECT DISTINCT city
FROM users
ORDER BY city ASC;

Result:

city
Delhi
Mumbai
Raipur

---

68. Advanced SELECT – Multiple Sorting

Example:

SELECT *
FROM employees
ORDER BY department_id ASC, salary DESC;

Meaning:

1. Department ke according sort.
2. Same department ke andar highest salary first.

---

69. Advanced SELECT – NULL Sorting

Example:

SELECT
    name,
    phone
FROM users
ORDER BY phone IS NULL, phone;

Ye non-NULL phone values ko pehle lane ke liye useful pattern ho sakta hai.

---

70. Subquery Performance Ko Kaise Check Karein?

Complex query ke liye:

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

MySQL ka execution plan inspect karein.

Agar query slow hai, possible alternatives:

• JOIN
• Derived table
• CTE, where supported by your MySQL version
• Better indexes
• Query rewrite

consider kiye ja sakte hain.

---

71. Subquery vs CTE

Modern MySQL versions mein CTE (Common Table Expression) bhi available hai.

Example:

WITH average_salary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT
    e.name,
    e.salary
FROM employees e
CROSS JOIN average_salary a
WHERE e.salary > a.avg_salary;

CTE complex queries ko readable banane mein help kar sakta hai.

Lekin beginner level par pehle subqueries ko properly samajhna important hai.

---

72. Subquery vs CTE – Simple Difference

Feature	Subquery	CTE
Query ke andar query	Yes	Yes
Readability	Small queries mein good	Complex queries mein often better
Reuse	Limited	Same CTE ko query ke multiple parts mein reference kiya ja sakta hai
Syntax	Direct	WITH se start
Beginner friendly	Yes	Moderate

---

73. Complete Practical Example

Ab ek complete employee example dekhte hain.

Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    salary DECIMAL(10,2) NOT NULL
);

Sample data:

INSERT INTO employees
(name, department_id, salary)
VALUES
('Rahul', 1, 40000),
('Amit', 1, 60000),
('Priya', 2, 70000),
('Neha', 2, 50000),
('Ravi', 3, 45000);

---

74. Average Salary Se Above Employees

SELECT
    name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

---

75. Highest Salary Employee

SELECT
    name,
    salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

---

76. Lowest Salary Employee

SELECT
    name,
    salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees
);

---

77. Department-Wise Average Se Above Employees

SELECT
    e.name,
    e.department_id,
    e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

---

78. Important Subquery Types

Ab tak jo concepts humne seekhe unhe categorize kar sakte hain:

Type	Example
Scalar Subquery	= (SELECT AVG(...))
Multi-row Subquery	IN (SELECT ...)
Correlated Subquery	Outer query reference
EXISTS Subquery	EXISTS (SELECT ...)
NOT EXISTS	NOT EXISTS (SELECT ...)
Derived Table	Subquery in FROM
Nested Subquery	Subquery inside another subquery

---

79. Quick Revision

Subquery

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

IN

SELECT *
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
);

EXISTS

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

NOT EXISTS

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

Derived Table

SELECT *
FROM (
    SELECT *
    FROM employees
    WHERE salary > 50000
) AS high_salary;

Correlated Subquery

SELECT *
FROM employees e
WHERE salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

---

80. Important Differences

Concept	Main Purpose
Subquery	Ek query ke result ko doosri query mein use karna
Nested Query	Multiple levels of queries
IN	Multiple returned values ke set ke against match
EXISTS	Related row existence check
NOT EXISTS	Related row absence check
Correlated Subquery	Outer row par dependent subquery
Derived Table	FROM mein subquery ka result
JOIN	Multiple tables ke data ko combine karna
CTE	Complex query ko readable/reusable blocks mein organize karna

---

81. Common Mistakes – Quick Checklist

Before running a subquery, check:

• Kya inner query one value return kar rahi hai?
• Agar multiple rows hain to kya IN use karna chahiye?
• Kya NULL values involved hain?
• Kya NOT IN ke badle NOT EXISTS better rahega?
• Kya correlated subquery necessary hai?
• Kya JOIN query ko simpler bana sakta hai?
• Kya derived table/CTE readability improve karega?
• Kya query ke liye index available hai?
• Kya EXPLAIN se execution plan check kiya hai?

---

82. Practice Exercises

Ab kuch practical exercises khud solve karein.

Exercise 1 – Average Salary

Employees table se un employees ko find karein jinki salary overall average salary se greater hai.

Hint:

AVG()

---

Exercise 2 – Highest Salary

Subquery ka use karke highest salary wale employee ko find karein.

Hint:

MAX()

---

Exercise 3 – Lowest Salary

Subquery use karke lowest salary wale employee ko find karein.

Hint:

MIN()

---

Exercise 4 – Customers With Orders

users aur orders tables se un users ko find karein jinhone at least one order place kiya hai.

Try both:

IN

and:

EXISTS

---

Exercise 5 – Customers Without Orders

Un users ko find karein jinhone koi order place nahi kiya.

Try:

NOT EXISTS

---

Exercise 6 – Category Average

Aise products find karein jinki price apni category ke average price se greater hai.

Hint:

Correlated Subquery use karein.

---

Exercise 7 – Second Highest Salary

Subquery ka use karke second highest distinct salary find karein.

---

Exercise 8 – Active Departments

Aise employees find karein jo active departments mein belong karte hain.

Try:

IN (
    SELECT ...
)

---

Exercise 9 – Salary Report

CASE aur subquery use karke employees ko classify karein:

Above Average
Average or Below

---

Exercise 10 – Advanced Report

Department-wise average salary calculate karein aur sirf un departments ko show karein jinka average company-wide average se zyada hai.

Hint:

GROUP BY
HAVING
Subquery

---

83. Easy Way To Remember

Subqueries ko yaad rakhne ka simple formula:

ONE VALUE
    ↓
=

MULTIPLE VALUES
    ↓
IN

RECORD EXISTS?
    ↓
EXISTS

RECORD DOES NOT EXIST?
    ↓
NOT EXISTS

OUTER ROW SE CONNECTED
    ↓
CORRELATED SUBQUERY

SUBQUERY IN FROM
    ↓
DERIVED TABLE

---

Conclusion

MySQL mein basic SELECT queries ke baad Subqueries aur Advanced SELECT Statements database querying ko next level par le jaate hain.

Aaj humne seekha:

• Subquery ek query ke andar doosri query hoti hai.
• = single-value subqueries ke liye useful hai.
• IN multiple returned values ke saath useful hai.
• EXISTS related record ki existence check karta hai.
• NOT EXISTS related record ki absence check karta hai.
• FROM ke andar subquery ko derived table ki tarah use kar sakte hain.
• Correlated Subquery outer query ki current row ko reference karti hai.
• Nested subqueries multiple levels tak ho sakti hain.
• CASE, GROUP BY, HAVING, ORDER BY, LIMIT jaise features ke saath advanced queries build ki ja sakti hain.
• JOIN aur subquery dono powerful hain; correct choice query ke purpose aur execution plan par depend karti hai.
• Complex queries ki performance check karne ke liye EXPLAIN important tool hai.

Agar aap PHP, CodeIgniter 4, Laravel ya kisi backend application ke saath MySQL use kar rahe hain, to subqueries ko samajhna complex reports, dashboards, filtering aur business logic implement karne ke liye bahut useful hai.