Subqueries in SQL

A subquery (also called an inner query or nested query) is a SQL query written inside another SQL query. The result of the subquery is used by the outer (main) query to perform further operations. Subqueries help solve complex problems in a clean and logical way when a query depends on the result of another query.

Subqueries are widely used in filtering data, comparisons, calculations, and existence checks. They are especially useful when you need intermediate results without creating temporary tables.

---

Key Characteristics of Subqueries

• Always written inside parentheses ( )
• Executed before the outer query (except correlated subqueries)
• Can return:
  • A single value (scalar subquery)
  • A list of values
  • A table-like result
• Can be used in:
  • WHERE clause
  • FROM clause
  • SELECT clause
  • HAVING clause

---

1. Subquery in WHERE Clause

Example: Employees earning more than average salary

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

Explanation

• The inner query calculates the average salary of all employees.
• The outer query compares each employee’s salary with that average.
• Only employees earning more than the company average are returned.

Why Use This?

• When filtering data based on calculated values
• Avoids hard-coding numbers
• Makes queries dynamic

---

2. Subquery with IN Operator

Example: Employees in departments located in New York

SELECT first_name, last_name, department
FROM employees
WHERE department IN (
    SELECT department_name 
    FROM departments 
    WHERE location = 'New York'
);

Explanation

• The subquery returns a list of department names located in New York.
• The outer query selects employees whose department matches any value in that list.

When to Use IN

• When the subquery returns multiple values
• Alternative to multiple OR conditions

---

3. Subquery in FROM Clause (Derived Table)

Example: Departments with average salary above company average

SELECT dept_avg.department, dept_avg.avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg
WHERE dept_avg.avg_salary > (
    SELECT AVG(salary) FROM employees
);

Explanation

• The subquery in the FROM clause acts like a temporary table.
• First, average salary is calculated per department.
• Then, only departments with higher-than-company-average salary are shown.

Benefits

• Breaks complex logic into steps
• Improves readability
• Often used in reporting queries

---

4. Correlated Subquery

Example: Employees earning more than their department’s average

SELECT e1.first_name, e1.last_name, e1.department, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

Explanation

• The inner query depends on the outer query (e1.department)
• Executed once for each row of the outer query
• Compares employee salary with department-specific average

Important Note

• Slower than normal subqueries on large datasets
• Often replaced with JOIN + GROUP BY for performance

---

5. EXISTS Operator

Example: Departments that have at least one employee

SELECT d.department_name, d.location
FROM departments d
WHERE EXISTS (
    SELECT 1 
    FROM employees e 
    WHERE e.department = d.department_name
);

Explanation

• EXISTS checks whether the subquery returns any rows
• It does not care about actual data, only existence
• Stops processing as soon as one match is found

EXISTS vs IN

EXISTS	IN
Faster for large data	Slower for large lists
Checks row existence	Compares values
Uses correlation	Uses list

---

6. Scalar Subqueries

A scalar subquery returns exactly one value.

SELECT first_name, salary,
       (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

Use Case

• Displaying calculated values alongside row data
• Reporting dashboards

---

7. Subquery in HAVING Clause

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > (
    SELECT AVG(salary) FROM employees
);

Explanation

• Filters groups using aggregated values
• Useful for comparative analysis

---

Execution Flow of Subqueries

1. Inner query executes first
2. Result is passed to outer query
3. Outer query executes using subquery result

Exception: Correlated subqueries execute row-by-row.

---

Subquery vs JOIN (Important Concept)

Subquery	JOIN
Easier to understand	Better performance
Step-wise logic	More efficient
Slower on large data	Preferred in real-time apps

Rule of Thumb:
Use subqueries for clarity, JOINs for performance.

---

Common Mistakes with Subqueries

• Subquery returns multiple rows when single value expected
• Forgetting parentheses
• Using = instead of IN
• Poor performance with correlated subqueries
• Not using aliases in FROM subqueries