SQL Subqueries and Advanced Commands

A subquery is a query nested inside another query. Subqueries enable operations that cannot be done with simple joins or aggregations.

This chapter covers subqueries, set operations (UNION, INTERSECT, EXCEPT), Common Table Expressions (CTEs), and window functions.

---

Learning Objectives

After completing this chapter, you will be able to:

• Write scalar, row, and table subqueries.
• Differentiate between correlated and non-correlated subqueries.
• Use EXISTS, IN, ANY, and ALL with subqueries.
• Write CTEs with the WITH clause.
• Use UNION, INTERSECT, and EXCEPT.
• Write window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD).
• Answer advanced SQL interview questions.

---

Sample Data

CREATE TABLE Employees (
    Emp_ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Dept_ID INT,
    Salary DECIMAL(10,2)
);

CREATE TABLE Departments (
    Dept_ID INT PRIMARY KEY,
    Dept_Name VARCHAR(50)
);

Employees

Emp_ID	Name	Dept_ID	Salary
1	Rahul	10	60000
2	Priya	10	75000
3	Amit	20	50000
4	Sneha	20	55000
5	Vikram	30	80000
6	Neha	NULL	45000

Departments

Dept_ID	Dept_Name
10	Engineering
20	Marketing
40	Finance

---

Types of Subqueries

Subqueries can return different types of results.

Type	Returns	Used In
Scalar	Single value (one column, one row)	SELECT, WHERE, SET
Row	Multiple columns, one row	WHERE comparisons
Table	Multiple rows and columns	FROM, IN, EXISTS, joins
Column	Single column, multiple rows	IN, ANY, ALL

---

Scalar Subquery

Returns a single value. Used anywhere a single value is needed.

-- Get employees earning more than the average salary
SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Name	Salary
Rahul	60000
Priya	75000
Vikram	80000

The subquery (SELECT AVG(Salary) FROM Employees) returns 60833.33 (a single value).

-- Show each employee's salary as a percentage of the total
SELECT Name, Salary,
       ROUND(Salary * 100.0 / (SELECT SUM(Salary) FROM Employees), 2) AS Pct
FROM Employees;

---

Row Subquery

Returns one row with multiple columns.

-- Find the employee(s) with the highest salary in each department
-- This requires a correlated subquery (covered next)

Row subqueries are less common but useful for comparing multiple columns at once.

-- Find employee whose salary matches the max in dept 10
SELECT Name, Salary
FROM Employees
WHERE (Dept_ID, Salary) = (SELECT Dept_ID, MAX(Salary)
                            FROM Employees
                            WHERE Dept_ID = 10
                            GROUP BY Dept_ID);

---

Table Subquery

Returns a full result set. Used in the FROM clause (derived table) or with IN/EXISTS.

-- Find departments with above-average salaries
SELECT d.Dept_Name, avg_data.Avg_Salary
FROM Departments d
JOIN (
    SELECT Dept_ID, AVG(Salary) AS Avg_Salary
    FROM Employees
    GROUP BY Dept_ID
) avg_data ON d.Dept_ID = avg_data.Dept_ID
WHERE avg_data.Avg_Salary > (SELECT AVG(Salary) FROM Employees);

Dept_Name	Avg_Salary
Engineering	67500

---

Correlated vs Non-Correlated Subqueries

Non-Correlated

The inner query runs independently of the outer query. It runs once.

SELECT Name FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

The subquery runs once → gets the average → the outer query uses that value.

Correlated

The inner query references a column from the outer query. It runs once for each row of the outer query.

-- Find employees who earn more than the average of their OWN department
SELECT e1.Name, e1.Salary, e1.Dept_ID
FROM Employees e1
WHERE e1.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.Dept_ID = e1.Dept_ID  -- Reference to outer query
);

Name	Salary	Dept_ID
Priya	75000	10
Sneha	55000	20
Vikram	80000	30

Dept 10 average: 67500 → Priya (75000) is above. Dept 20 average: 52500 → Sneha (55000) is above. Dept 30 average: 80000 → Vikram (80000) is equal (not above). Neha (Dept_ID NULL): ignored.

Performance

Correlated subqueries are slower — they execute once per outer row. For large tables, consider rewriting as a JOIN or using window functions.

---

EXISTS

EXISTS returns TRUE if the subquery returns at least one row.

-- Find departments that have at least one employee
SELECT d.Dept_Name
FROM Departments d
WHERE EXISTS (
    SELECT 1 FROM Employees e WHERE e.Dept_ID = d.Dept_ID
);

Dept_Name
Engineering
Marketing

NOT EXISTS

-- Find departments with NO employees
SELECT d.Dept_Name
FROM Departments d
WHERE NOT EXISTS (
    SELECT 1 FROM Employees e WHERE e.Dept_ID = d.Dept_ID
);

Dept_Name
Finance

EXISTS vs IN

Aspect	EXISTS	IN
NULL handling	Not affected by NULLs	Affected — IN (NULL, 1, 2) has unexpected behavior
Performance	Better for large subquery results	Better for small, static lists
Readability	Clear for “exists” semantics	Clear for “value is in list”

---

IN, ANY, ALL

IN

Checks if a value matches any value in a list.

SELECT Name FROM Employees
WHERE Dept_ID IN (10, 20);

With subquery:

SELECT Name FROM Employees
WHERE Dept_ID IN (SELECT Dept_ID FROM Departments);

ANY

Compares a value to each value returned by the subquery.

-- Find employees earning more than any employee in dept 20
SELECT Name, Salary
FROM Employees
WHERE Salary > ANY (SELECT Salary FROM Employees WHERE Dept_ID = 20);

Dept 20 salaries: 50000, 55000. ANY means “greater than at least one” — so employees with salary > 50000.

ALL

Compares a value to all values returned by the subquery.

-- Find employees earning more than ALL employees in dept 20
SELECT Name, Salary
FROM Employees
WHERE Salary > ALL (SELECT Salary FROM Employees WHERE Dept_ID = 20);

Dept 20 salaries: 50000, 55000. ALL means “greater than every one” — so employees with salary > 55000.

---

CTE (Common Table Expression)

A CTE (WITH clause) creates a temporary named result set that you can reference in the main query.

WITH DeptAvg AS (
    SELECT Dept_ID, AVG(Salary) AS Avg_Salary
    FROM Employees
    GROUP BY Dept_ID
)
SELECT d.Dept_Name, da.Avg_Salary
FROM Departments d
JOIN DeptAvg da ON d.Dept_ID = da.Dept_ID
WHERE da.Avg_Salary > 50000;

Recursive CTE

Used for hierarchical data (org charts, category trees).

WITH RECURSIVE OrgChart AS (
    -- Anchor: top-level managers
    SELECT Emp_ID, Name, Manager_ID, 1 AS Level
    FROM Employees WHERE Manager_ID IS NULL

    UNION ALL

    -- Recursive: employees reporting to those in the CTE
    SELECT e.Emp_ID, e.Name, e.Manager_ID, oc.Level + 1
    FROM Employees e
    JOIN OrgChart oc ON e.Manager_ID = oc.Emp_ID
)
SELECT * FROM OrgChart;

CTE vs Subquery

Aspect	CTE	Subquery
Readability	Better (named, separate)	Can get nested and confusing
Reusability	Can reference the CTE multiple times	Must repeat the subquery
Recursion	Supports recursive queries	Cannot do recursion

---

UNION, INTERSECT, EXCEPT

Set operations combine results from two queries.

UNION

Combines results, removing duplicates.

SELECT Name FROM Employees
UNION
SELECT Dept_Name FROM Departments;

UNION ALL keeps duplicates (faster).

SELECT Dept_ID FROM Employees
UNION ALL
SELECT Dept_ID FROM Departments;

INTERSECT

Returns rows that appear in both queries.

SELECT Dept_ID FROM Employees
INTERSECT
SELECT Dept_ID FROM Departments;

Dept_IDs present in both tables: 10, 20.

EXCEPT (MINUS in Oracle)

Returns rows from the first query that do NOT appear in the second.

SELECT Dept_ID FROM Departments
EXCEPT
SELECT Dept_ID FROM Employees;

Dept_IDs in Departments but not in Employees: 40.

Rules

• Both queries must have the same number of columns.
• Corresponding columns must have compatible data types.
• ORDER BY applies to the final result (at the end).

---

Window Functions

Window functions perform calculations across a set of rows related to the current row, without collapsing them into a single output row.

ROW_NUMBER

Assigns a unique sequential number to each row within a partition.

SELECT Name, Dept_ID, Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

RANK

Assigns ranks with gaps for ties.

SELECT Name, Dept_ID, Salary,
       RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

DENSE_RANK

Assigns ranks without gaps for ties.

SELECT Name, Dept_ID, Salary,
       DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

Name	Salary	ROW_NUMBER	RANK	DENSE_RANK
Vikram	80000	1	1	1
Priya	75000	2	2	2
Rahul	60000	3	3	3
Sneha	55000	4	4	4
Amit	50000	5	5	5

(No ties in this dataset, but if multiple employees had the same salary, RANK would skip numbers while DENSE_RANK would not.)

Rank Within Each Department (PARTITION BY)

SELECT Name, Dept_ID, Salary,
       RANK() OVER (PARTITION BY Dept_ID ORDER BY Salary DESC) AS Dept_Rank
FROM Employees
WHERE Dept_ID IS NOT NULL;

LAG and LEAD

Access data from previous or next rows.

SELECT Name, Salary,
       LAG(Salary, 1) OVER (ORDER BY Salary) AS Previous_Salary,
       LEAD(Salary, 1) OVER (ORDER BY Salary) AS Next_Salary
FROM Employees;

Name	Salary	Previous_Salary	Next_Salary
Amit	50000	NULL	55000
Sneha	55000	50000	60000
Rahul	60000	55000	75000
Priya	75000	60000	80000
Vikram	80000	75000	NULL

LAG/LEAD are useful for calculating differences between consecutive rows.

---

Interview Deep Dive

---

Key Takeaways

• Scalar subqueries return a single value; table subqueries return a result set.
• Correlated subqueries reference the outer query and run once per row.
• EXISTS checks for existence (faster than IN for large subqueries).
• IN, ANY, ALL compare values against subquery results.
• CTEs make complex queries readable and support recursion.
• UNION, INTERSECT, EXCEPT operate on result sets (not individual rows).
• Window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD) compute across rows without collapsing them.
• PARTITION BY creates groups within window functions.