Data Insights MySQL Masterclass — Part 5

"Subqueries — Queries ke Andar Queries"

SQL ka next level — ek query ke andar dusri query likhna. WHERE mein, FROM mein, SELECT mein subqueries seekho. Correlated subquery, EXISTS vs IN — sab kuch real-world examples aur interview questions ke saath Data Insights par.

📑 Is Part 5 Mein Aap Kya Sikhenge:

Subqueries ka complete guide — har type real examples ke saath:

• Topic 1: Subquery Kya Hai — Introduction & Types
• Topic 2: Subquery in WHERE — Single Row & Multi Row
• Topic 3: Subquery in FROM — Derived Tables
• Topic 4: Subquery in SELECT — Scalar Subqueries
• Topic 5: Correlated Subquery — Row by Row Processing
• Topic 6: EXISTS / NOT EXISTS
• Topic 7: IN vs EXISTS — Difference & When to Use

📋 Note: Is Part 5 mein bhi wahi employees aur departments tables use karenge jo Part 4 mein banaye the. Agar fresh start kar rahe ho toh Part 4 ka setup section dekhlo.

text

🔍 Definition: A subquery (also called an inner query or nested query) is a SQL query written inside another SQL query. The outer query is called the main query or outer query. The inner query executes first, its result is passed to the outer query, and then the outer query runs using that result.

🎯 Samjho Simple Bhasha Mein: Subquery matlab "query ke andar query." Socho tum poochh rahe ho "un sabka naam batao jinki salary company ki average salary se zyada hai." Toh pehle tumhe average salary pata karni hogi (inner query), phir us average se compare karke employees dhundhne honge (outer query). Yeh do alag queries ek saath run hoti hain — inner pehle, outer baad mein.

💡 Subquery ke 4 Main Types — Placement ke hisaab se:

1. WHERE mein: WHERE salary > (SELECT AVG(salary) FROM employees)
2. FROM mein: FROM (SELECT ... FROM ...) AS derived_table
3. SELECT mein: SELECT name, (SELECT COUNT(*) FROM ...) AS total
4. HAVING mein: HAVING AVG(salary) > (SELECT AVG(salary) FROM ...)

📊 Subquery vs JOIN — Kab Kya Use Karein:

Situation	Subquery	JOIN
Single value result chahiye	✅ Best	Complicated
Multiple columns from both tables	Difficult	✅ Best
Readability	✅ Easier to understand	Can get complex
Performance (large data)	Sometimes slow	✅ Usually faster
Existence check	✅ EXISTS is perfect	Overkill

💬 Interview Questions:

text

🔍 Definition: Subquery in WHERE clause filters rows based on the result of an inner query. If the inner query returns a single value, comparison operators (=, >, <) are used. If it returns multiple values, IN / NOT IN operators are used.

🎯 Samjho Simple Bhasha Mein: WHERE clause mein subquery tab use hoti hai jab filter condition dynamic ho — yaani pehle ek value calculate karni ho, phir us value se compare karna ho. Jaise "average salary pehle nikalo, phir jo average se zyada kamate hain unhe dikhao." Dono kaam ek hi query mein!

💡 Single Row vs Multi Row Subquery:
Single Row: Inner query returns 1 value → Use =, >, <, >=, <=
Multi Row: Inner query returns multiple values → Use IN, NOT IN, ANY, ALL

Agar single row expected hai aur multiple aayein → Error! Always verify.

💻 Real-World Code Examples:

Example 1: Average salary se zyada kamane wale employees (Single Row Subquery).

-- Step 1: Inner query average calculate karta hai
-- Step 2: Outer query us average se compare karta hai
SELECT
emp_name,
salary,
ROUND(salary - (SELECT AVG(salary) FROM employees), 2) AS above_avg_by

FROM employees

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

ORDER BY salary DESC;

Example 2: Sabse zyada salary wale employee ka naam (MAX Subquery).

-- Highest paid employee
SELECT emp_name, salary

FROM employees

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

Example 3: IT department ke employees ki average salary se zyada kamane wale (Multi-level).

-- Employees earning more than IT department's average
SELECT emp_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'IT'
)

ORDER BY salary DESC;

Example 4: Employees jinki salary top 3 earners mein hai (Multi Row with IN).

-- Employees in top 3 salary (Multi Row Subquery)
SELECT emp_name, salary

FROM employees

WHERE salary IN (
SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 3
)

ORDER BY salary DESC;

Example 5: ANY aur ALL operators — advanced subquery.

-- ANY: salary greater than ANY HR employee salary
-- (matlab: HR ki minimum salary se zyada ho)
SELECT emp_name, salary

FROM employees

WHERE salary > ANY (
SELECT salary FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'HR'
);

-- ALL: salary greater than ALL Sales employees
-- (matlab: Sales ki maximum salary se bhi zyada ho)
SELECT emp_name, salary

FROM employees

WHERE salary > ALL (
SELECT salary FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Sales'
);

📊 Expected Output (Example 1 — Above Average):

-- Company avg salary = approx 66272.73

emp_name	salary	above_avg_by
Anjali Mehta	95000.00	28727.27
Sneha Patel	92000.00	25727.27
Rahul Sharma	85000.00	18727.27
Neha Gupta	71000.00	4727.27
Deepak Rao	68000.00	1727.27

5 rows in set

📊 ANY vs ALL — Quick Comparison:

Operator	Meaning	Equivalent To
> ANY	Kisi ek se bhi zyada	> MIN()
> ALL	Sabse zyada se bhi zyada	> MAX()
= ANY	List mein se kisi ek ke equal	IN
!= ALL	Kisi se bhi equal nahi	NOT IN

⚠️ Common Mistakes:

• Mistake: Single row operator use karna multi-row result ke saath → WHERE salary = (SELECT salary FROM employees) agar multiple rows aayein toh ERROR: Subquery returns more than 1 row.
  Fix: Multi row result ke liye IN use karo.
• Mistake: Subquery mein ORDER BY likhna → MySQL mein subquery ke andar ORDER BY ka koi effect nahi hota (except with LIMIT).
  Fix: Outer query mein ORDER BY lagao.
• Mistake: NOT IN mein NULL values → Agar subquery result mein NULL hai toh NOT IN kabhi bhi rows return nahi karega!
  Fix: WHERE salary IS NOT NULL subquery ke andar add karo.

💬 Interview Questions:

text

🔍 Definition: When a subquery is placed in the FROM clause, it acts as a temporary table (also called a derived table or inline view). The outer query treats this derived table just like a regular table. A derived table must always have an alias.

🎯 Samjho Simple Bhasha Mein: FROM mein subquery matlab "pehle ek temporary table banao, phir us table se data nikalo." Socho pehle department-wise average salary calculate karo, phir us summary table se sirf woh departments dikhao jinki average 60000 se zyada hai. Yeh 2 step process ek query mein karna ho toh FROM subquery use karte hain.

💡 Derived Table Rules:
1. FROM mein subquery ko hamesha alias dena zaroori hai — warna MySQL error dega.
2. Derived table mein columns ka naam access karne ke liye alias use karo.
3. Derived table sirf us query ke execution tak exist karta hai — permanent nahi hota.

💻 Real-World Code Examples:

Example 1: Department wise average salary nikalo phir filter karo.

-- Step 1: Inner query dept_wise avg banata hai (derived table)
-- Step 2: Outer query sirf high avg departments filter karta hai
SELECT
dept_summary.dept_id,
dept_summary.avg_salary,
dept_summary.emp_count

FROM (
SELECT
dept_id,
ROUND(AVG(salary), 2) AS avg_salary,
COUNT(*) AS emp_count
FROM employees
WHERE salary IS NOT NULL
GROUP BY dept_id
) AS dept_summary

WHERE dept_summary.avg_salary > 60000;

Example 2: Derived table ko JOIN karo original table se — employee details with dept summary.

-- Derived table + JOIN: Employee vs Department Average
SELECT
e.emp_name,
e.salary,
d.dept_name,
ds.avg_salary AS dept_avg,
ROUND(e.salary - ds.avg_salary, 2) AS diff_from_avg

FROM employees e
INNER
JOIN departments d
ON e.dept_id = d.dept_id
INNER
JOIN (
SELECT
dept_id,
ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY dept_id
) AS ds
ON e.dept_id = ds.dept_id

ORDER BY d.dept_name, diff_from_avg DESC;

Example 3: Salary ranks — derived table se rank assign karo.

-- Salary rank using derived table (ROW_NUMBER alternative)
SELECT
ranked.emp_name,
ranked.salary,
ranked.salary_rank

FROM (
SELECT
emp_name,
salary,
(SELECT COUNT(*) + 1
FROM employees e2
WHERE e2.salary > e1.salary
AND e2.salary IS NOT NULL
) AS salary_rank
FROM employees e1
WHERE salary IS NOT NULL
) AS ranked

ORDER BY salary_rank;

📊 Expected Output (Example 2 — Employee vs Dept Avg):

emp_name	salary	dept_name	dept_avg	diff_from_avg
Deepak Rao	68000.00	Finance	69500.00	-1500.00
Neha Gupta	71000.00	Finance	69500.00	1500.00
Priya Singh	62000.00	HR	57500.00	4500.00
Ravi Verma	53000.00	HR	57500.00	-4500.00
Anjali Mehta	95000.00	IT	90666.67	4333.33
Rahul Sharma	85000.00	IT	90666.67	-5666.67
Sneha Patel	92000.00	IT	90666.67	1333.33
Suresh Nair	51000.00	Sales	48000.00	3000.00
Amit Kumar	48000.00	Sales	48000.00	.00
Kavita Joshi	45000.00	Sales	48000.00	-3000.00

text

⚠️ Common Mistakes:

• Mistake: Derived table ko alias nahi dena → ERROR 1248: Every derived table must have its own alias
  Fix: Hamesha AS alias_name likho: ) AS dept_summary
• Mistake: Outer query mein derived table ka column directly access karna bina alias prefix ke.
  Fix: dept_summary.avg_salary — table alias prefix zaroori hai.
• Mistake: Complex nested derived tables → Readability khatam ho jaati hai.
  Fix: CTEs (Common Table Expressions — WITH clause) use karo — cleaner syntax.

💬 Interview Questions:

text

🔍 Definition: A scalar subquery in SELECT returns exactly one value (one row, one column) and is used as a column expression. For each row of the outer query, the subquery executes and returns a single calculated value. If the subquery returns more than one row, MySQL throws an error.

🎯 Samjho Simple Bhasha Mein: SELECT mein subquery matlab "har row ke liye ek extra calculated column add karo." Jaise har employee ki salary ke saath company ki overall average bhi dikhani ho — SELECT mein subquery likhoge jo average return karega aur woh har row mein ek column ki tarah dikhega. Yeh ek calculated virtual column hai.

💡 Important Rule: SELECT mein subquery sirf ek hi value return kar sakti hai (scalar). Agar multiple rows aayein toh ERROR: Subquery returns more than 1 row. Isliye SELECT subquery mostly aggregate functions ke saath use hoti hai: COUNT, SUM, AVG, MIN, MAX.

💻 Real-World Code Examples:

Example 1: Har employee ki salary ke saath company average bhi dikhao.

-- SELECT subquery: Company average har row mein
SELECT
emp_name,
salary,
(SELECT ROUND(AVG(salary), 2) FROM employees) AS company_avg,

ROUND(salary - (SELECT AVG(salary) FROM employees), 2) AS diff

FROM employees

WHERE salary IS NOT NULL

ORDER BY salary DESC;

Example 2: Har employee ke saath total employees count dikhao.

-- Total employees + each employee info
SELECT
emp_name,
salary,
(SELECT COUNT(*) FROM employees) AS total_employees,
ROUND(salary * 100.0 / (SELECT SUM(salary) FROM employees), 2) AS salary_percent

FROM employees

WHERE salary IS NOT NULL

ORDER BY salary_percent DESC;

Example 3: Har department ke saath uske employees ki count dikhao (correlated with dept).

-- Department list with employee count per dept
SELECT
d.dept_name,
d.location,
(SELECT COUNT(*)
FROM employees e
WHERE e.dept_id = d.dept_id) AS emp_count,
(SELECT ROUND(AVG(salary), 2)
FROM employees e
WHERE e.dept_id = d.dept_id) AS avg_salary

FROM departments d

ORDER BY emp_count DESC;

📊 Expected Output (Example 1):

emp_name	salary	company_avg	diff
Anjali Mehta	95000.00	66272.73	28727.27
Sneha Patel	92000.00	66272.73	25727.27
Rahul Sharma	85000.00	66272.73	18727.27
Neha Gupta	71000.00	66272.73	4727.27
Deepak Rao	68000.00	66272.73	1727.27
Priya Singh	62000.00	66272.73	-4272.73
Rohit Tiwari	55000.00	66272.73	-11272.73
Ravi Verma	53000.00	66272.73	-13272.73
Suresh Nair	51000.00	66272.73	-15272.73
Amit Kumar	48000.00	66272.73	-18272.73
Kavita Joshi	45000.00	66272.73	-21272.73

text

📊 Expected Output (Example 3 — Dept with Counts):

dept_name	location	emp_count	avg_salary
IT	Bangalore	3	90666.67
Sales	Delhi	3	48000.00
HR	Mumbai	2	57500.00
Finance	Chennai	2	69500.00
Marketing	Pune	0	NULL

text

⚠️ Common Mistakes:

• Mistake: SELECT subquery se multiple rows return hona → Immediate ERROR.
  Fix: Hamesha ensure karo inner query sirf 1 value return kare — aggregate function use karo.
• Mistake: Performance — agar SELECT mein subquery hai toh woh har row ke liye execute hoti hai.
  Fix: Agar subquery outer table se linked nahi hai toh derived table ya JOIN use karo — ek baar calculate hoga.

💬 Interview Questions:

text

🔍 Definition: A correlated subquery references a column from the outer query inside the inner query. Unlike a regular subquery that runs once, a correlated subquery runs once for EVERY row of the outer query — making it dependent on (correlated with) the outer query's current row.

🎯 Samjho Simple Bhasha Mein: Normal subquery ek baar run hoti hai — ek fixed result deti hai. Correlated subquery har row ke liye alag alag run hoti hai. Socho: "Har employee ke liye — unke department mein unse zyada salary wale kitne hain?" — yeh Rahul ke liye IT department check karega, phir Priya ke liye HR check karega. Har row ke liye alag calculation!

💡 Regular vs Correlated Subquery:

Regular: Inner query outer query se independent hai — ek baar run hoti hai.
WHERE salary > (SELECT AVG(salary) FROM employees)

Correlated: Inner query outer query ke current row ka column use karti hai — har row ke liye run hoti hai.
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id)

e1 outer query ka alias hai — inner query isse refer karti hai!

💻 Real-World Code Examples:

Example 1: Employees jo apne department ki average salary se zyada kamate hain.

-- Correlated Subquery: Dept-wise above average
-- e1 = outer query ka employee
-- e2 = inner query same table ka reference
SELECT
e1.emp_name,
e1.salary,
e1.dept_id,
(SELECT ROUND(AVG(e2.salary), 2)
FROM employees e2
WHERE e2.dept_id = e1.dept_id) AS dept_avg

FROM employees e1

WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id -- e1.dept_id outer query se aata hai!
)

ORDER BY e1.dept_id, e1.salary DESC;

Example 2: Har employee ke liye — unse zyada salary wale kitne employees hain (salary rank).

-- Correlated: Salary rank for each employee
SELECT
e1.emp_name,
e1.salary,
(SELECT COUNT(*) + 1
FROM employees e2
WHERE e2.salary > e1.salary
AND e2.salary IS NOT NULL
) AS salary_rank

FROM employees e1

WHERE e1.salary IS NOT NULL

ORDER BY salary_rank;

Example 3: Employees jo apne department ke senior hain (same dept mein pehle join kiya).

-- Correlated: Employees senior in their department
SELECT
e1.emp_name,
e1.dept_id,
e1.hire_date,
(SELECT COUNT(*)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
AND e2.hire_date AS seniors_in_dept
FROM employees e1
ORDER BY e1.dept_id, e1.hire_date;

📊 Expected Output (Example 2 — Salary Rank):

emp_name	salary	salary_rank
Anjali Mehta	95000.00	1
Sneha Patel	92000.00	2
Rahul Sharma	85000.00	3
Neha Gupta	71000.00	4
Deepak Rao	68000.00	5
Priya Singh	62000.00	6
Rohit Tiwari	55000.00	7
Ravi Verma	53000.00	8
Suresh Nair	51000.00	9
Amit Kumar	48000.00	10
Kavita Joshi	45000.00	11

text

⚠️ Common Mistakes:

• Mistake: Correlated subquery large tables par use karna → Bahut slow hoti hai (O(N²) complexity) — har row ke liye full inner scan.
  Fix: Jab possible ho JOIN ya Window Functions use karo (Part 6 mein).
• Mistake: Outer aur inner alias confuse karna → e1 aur e2 same table ke 2 alag instances hain.
  Fix: Hamesha clear aliases use karo aur mentally track karo kaunsa outer hai, kaunsa inner.
• Mistake: Non-correlated subquery ko correlated samajhna → Agar inner query outer column reference nahi karta, woh correlated nahi hai.

💬 Interview Questions:

text

🔍 Definition: EXISTS checks whether the subquery returns any rows at all — it returns TRUE if the subquery returns one or more rows, FALSE if it returns no rows. It does NOT care about the actual values — just whether rows exist. NOT EXISTS is the opposite — TRUE when subquery returns no rows.

🎯 Samjho Simple Bhasha Mein: EXISTS ka matlab sirf "hai ya nahi hai?" — values se koi matlab nahi. Socho tumhe check karna hai ki kisi department mein koi employee hai ya nahi. EXISTS ek baar match milte hi ruk jaata hai — poori table scan nahi karta. Yeh IN se zyada efficient hota hai large datasets ke liye.

💡 EXISTS ka Secret:
EXISTS subquery mein tum kuch bhi likh sakte ho — SELECT 1, SELECT *, SELECT 'abc' — koi fark nahi padta! EXISTS sirf yeh dekh raha hai ki koi row aayi ya nahi. Isliye convention hai SELECT 1 likhna — clearest intent batata hai.

💻 Real-World Code Examples:

Example 1: Departments jinmein kam se kam ek employee hai (EXISTS).

-- Departments THAT HAVE at least one employee
SELECT d.dept_name, d.location

FROM departments d

WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.dept_id = d.dept_id -- d.dept_id = outer query reference
);

Example 2: Departments jinmein koi employee NAHI hai (NOT EXISTS).

-- Departments with NO employees at all
SELECT d.dept_name, d.location

FROM departments d

WHERE NOT EXISTS (
SELECT 1
FROM employees e
WHERE e.dept_id = d.dept_id
);

Example 3: Employees jo kisi project mein kaam nahi karte (NOT EXISTS).

-- Employees whose department has NO project assigned
SELECT e.emp_name, e.salary

FROM employees e

WHERE NOT EXISTS (
SELECT 1
FROM projects p
WHERE p.dept_id = e.dept_id
);

-- EXISTS with condition: Departments with high salary employees
SELECT d.dept_name

FROM departments d

WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.dept_id = d.dept_id
AND e.salary > 80000 -- Extra condition inside EXISTS
);

📊 Expected Output:

-- Example 1 (Departments WITH employees):

dept_name	location
IT	Bangalore
HR	Mumbai
Sales	Delhi
Finance	Chennai
dept_name	location
Marketing	Pune
dept_name	IT

-- Example 2 (Departments WITHOUT employees): -- EXISTS with salary > 80000: (Only IT has employees with salary > 80000)

text

⚠️ Common Mistakes:

• Mistake: EXISTS mein SELECT * likhna aur assume karna ki values matter karte hain → EXISTS values ignore karta hai.
  Fix: SELECT 1 use karo — clearly batata hai ki sirf existence check hai.
• Mistake: EXISTS ko IN ki jagah use karna jab actual values chahiye → EXISTS sirf TRUE/FALSE deta hai.
  Fix: Values chahiye → IN use karo. Sirf existence check → EXISTS use karo.
• Mistake: EXISTS mein outer query reference bhool jaana → Bina correlation ke EXISTS hamesha TRUE ya FALSE same return karega.
  Fix: Hamesha outer table ka column reference karo inner WHERE mein.

💬 Interview Questions:

text

🔍 Definition: Both IN and EXISTS are used to filter rows based on subquery results, but they work differently internally. IN evaluates the complete subquery and creates a list to check membership. EXISTS stops as soon as the first match is found and uses a correlated approach.

🎯 Samjho Simple Bhasha Mein: IN aur EXISTS dono kaam ek jaisa karte hain — "yeh row include karo ya nahi." Lekin tarika alag hai. IN pehle poori list bana leta hai phir har row compare karta hai. EXISTS har outer row ke liye seedha check karta hai ki match milti hai ya nahi, aur pehle match pe ruk jaata hai. Samjhane ke liye — IN ek compiled list se check karta hai, EXISTS real-time search karta hai.

💡 Performance Rule of Thumb:
Subquery result LARGE hai (many rows): EXISTS better (short-circuits early)
Subquery result SMALL hai (few rows): IN can be faster (cached list comparison)
NULL values ho sakti hain: EXISTS always safer (no NULL issue)
Modern MySQL optimizer: Often converts both to same execution plan!

📊 IN vs EXISTS — Complete Comparison:

Feature	IN	EXISTS
How it works	Subquery result list banata hai, phir membership check	Har outer row ke liye match dhundhta hai, first match pe ruk jaata hai
NULL Handling	❌ NOT IN fails with NULLs	✅ NOT EXISTS handles NULLs correctly
Performance — Small subquery	✅ Faster (cached list)	Slightly slower
Performance — Large subquery	Slow (entire list in memory)	✅ Faster (short-circuits)
Correlated?	Usually non-correlated	Always correlated
Use Case	Value list comparison	Existence check
Returns	Matches from the list	TRUE / FALSE only

💻 Same Query — IN vs EXISTS Side by Side:

Same Result — Two Different Approaches:

-- ===== USING IN =====
-- Employees jinke department mein IT ya Finance hai
SELECT emp_name, salary

FROM employees

WHERE dept_id IN (
SELECT dept_id
FROM departments
WHERE dept_name IN ('IT', 'Finance')
);

-- ===== USING EXISTS (same result) =====
SELECT e.emp_name, e.salary

FROM employees e

WHERE EXISTS (
SELECT 1
FROM departments d
WHERE d.dept_id = e.dept_id
AND d.dept_name IN ('IT', 'Finance')
);

NULL Problem — NOT IN vs NOT EXISTS:

-- ❌ WRONG: NOT IN fails if subquery has NULL
SELECT emp_name

FROM employees

WHERE dept_id NOT IN (
SELECT dept_id FROM departments
-- Agar koi dept_id NULL hai toh 0 rows return hongi!
);

-- ✅ SAFE: NOT IN with NULL protection
SELECT emp_name

FROM employees

WHERE dept_id NOT IN (
SELECT dept_id FROM departments
WHERE dept_id IS NOT NULL
);

-- ✅ BEST: NOT EXISTS (no NULL issue at all)
SELECT e.emp_name

FROM employees e

WHERE NOT EXISTS (
SELECT 1
FROM departments d
WHERE d.dept_id = e.dept_id
);

⚠️ Common Mistakes:

• Mistake: Hamesha IN use karna bina NULL check ke → Production mein NULL aate hain aur NOT IN silently 0 rows return karta hai.
  Fix: NOT IN ke saath hamesha WHERE col IS NOT NULL add karo, ya NOT EXISTS prefer karo.
• Mistake: EXISTS ke andar SELECT * likhna performance issue samajhna → EXISTS values fetch nahi karta, koi difference nahi.
  Fix: SELECT 1 use karo convention ke liye.
• Mistake: IN aur EXISTS ka performance blindly assume karna → Modern MySQL optimizer mostly dono ko same plan mein convert kar deta hai. EXPLAIN se verify karo.

💬 Interview Questions:

Type	Placement	Returns	Use Case
Single Row Subquery	WHERE	1 value	Compare with =, >, <
Multi Row Subquery	WHERE	Multiple values	Use with IN, NOT IN, ANY, ALL
Derived Table	FROM	Temp table	Complex aggregations
Scalar Subquery	SELECT	1 value per row	Computed columns
Correlated Subquery	WHERE/SELECT	Varies per row	Row-specific calculations
EXISTS	WHERE	TRUE / FALSE	Existence check (NULL safe)
NOT EXISTS	WHERE	TRUE / FALSE	Absence check (better than NOT IN)

🎯 Top Subquery Interview Questions — Rapid Fire

Q1: 2nd highest salary kaise nikalna hai? → WHERE salary = (SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp))

Q2: Dept average se zyada earners? → Correlated subquery — WHERE salary > (SELECT AVG(salary) FROM emp e2 WHERE e2.dept_id = e1.dept_id)

Q3: NOT IN vs NOT EXISTS? → NULL hone par NOT IN fails, NOT EXISTS safely works

Q4: Derived table ko alias kyu dena hai? → MySQL rule — every derived table must have alias, otherwise ERROR 1248

Q5: EXISTS mein SELECT 1 kyu likhte hain? → EXISTS values ignore karta hai, sirf row existence check karta hai — SELECT 1 convention hai

Next: Data Insights MySQL Masterclass — Part 6

Part 6 mein hum cover karenge: Advanced MySQL — Views, Indexes, Stored Procedures, Functions, Triggers aur Window Functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD) — real-world production-level SQL Data Insights par.

Happy Querying & Keep Learning SQL! 🚀