Subqueries (Nested Queries)

A subquery is a query inside another SQL query. It can be used in SELECT, FROM, or WHERE clauses to make your main query more powerful and dynamic.

🔹 Example 1: Subquery in WHERE

Get employees who earn more than the average salary:

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

👉 Shows employees earning more than the company’s average salary.

🔹 Example 2: Subquery in FROM (Derived Table)

Find the highest paid employee in each department:

SELECT dept_id, MAX(salary) AS max_salary
FROM (SELECT dept_id, salary FROM employees) AS temp
GROUP BY dept_id;

👉 The inner query creates a temporary table used by the outer query.

🔹 Example 3: Subquery in SELECT

Show employee name and their department name without using JOIN:

SELECT name,
       (SELECT department_name 
        FROM departments d 
        WHERE d.id = e.dept_id) AS dept_name
FROM employees e;

4. Correlated Subquery

A correlated subquery depends on the outer query for its values.

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

👉 Compares each employee’s salary with the average salary of their department.

5. EXISTS with Subquery

SELECT name
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.dept_id);

👉 Returns employees only if their department exists in the departments table.

🔹 Types of Subqueries

• Single-row → Returns one value (e.g., =, <, >).
• Multi-row → Returns multiple values (e.g., IN, ANY, ALL).
• Correlated → Subquery depends on the outer query (runs row by row).