This is where SQL starts feeling like a programming language. Many interview questions can be solved in two ways:

1. Using JOIN + GROUP BY
2. Using Subqueries

Don't worry if these seem confusing at first. Once you understand the execution order, everything clicks.

Intermediate SQL

We'll cover:

1. Subqueries
2. Correlated Subqueries
3. EXISTS
4. ANY
5. ALL
6. UNION
7. UNION ALL
8. INTERSECT
9. EXCEPT

We'll use this table throughout.

Employees

1. Subquery

A Subquery is simply a query inside another query.

Think of it like calling one function from another.

int max = getMaxSalary();
print(max);

SQL does something similar.

SELECT ...
WHERE Salary > (SELECT ...);

The inner query runs first. Its result is passed to the outer query.

Example

Question:

Find employees earning more than the average salary.

Can we write this?

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

No.

Why?

Because AVG(Salary) is an aggregate function, and WHERE cannot directly use it this way.

Instead:

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

Step 1: the inner query executes first.

SELECT AVG(Salary)
FROM Employees;

Returns:

60000

Step 2: the outer query becomes:

SELECT *
FROM Employees
WHERE Salary > 60000;

Output:

Easy Memory Trick

Outer Query
    |
    v
Needs a value
    |
    v
Runs Inner Query
    |
    v
Gets value
    |
    v
Continues

Subquery Types

For interviews, scalar and multiple-row subqueries are the most important.

2. Multiple Row Subquery

Example: find employees belonging to departments that have IT or HR.

SELECT *
FROM Employees
WHERE Department IN
(
    SELECT Department
    FROM Employees
    WHERE Department IN ('IT','HR')
);

The inner query returns multiple values:

IT
HR

Then the outer query checks whether each employee's department is in that list.

3. Correlated Subquery

This is where many people get confused.

Normal Subquery

Runs once.

Inner Query
    |
    v
Returns result
    |
    v
Outer Query uses it

Correlated Subquery

Runs once for every row of the outer query.

Think of it as a loop.

For each employee
    |
    v
Run inner query
    |
    v
Compare result

Example: find employees earning above their department's average salary.

SELECT *
FROM Employees e1
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees e2
    WHERE e2.Department = e1.Department
);

Notice:

e1.Department

The inner query is using a value from the outer query. That's why it is called correlated.

For Rahul, the inner query calculates the IT average salary. Rahul's salary is not greater than that average.

For Amit, the IT average salary is lower than Amit's salary, so Amit is returned.

Memory Trick

Normal Subquery:

Runs once.

Correlated Subquery:

Runs once per outer row.

4. EXISTS

Checks whether at least one row exists.

Returns:

• TRUE
• FALSE

Example: show users who placed orders.

SELECT *
FROM Users u
WHERE EXISTS
(
    SELECT *
    FROM Orders o
    WHERE o.User_ID = u.ID
);

For Rahul, an order exists, so Rahul is returned.

For Amit, no order exists, so Amit is not returned.

Why EXISTS?

It stops searching as soon as it finds the first matching row, so it can be more efficient than counting all matches when you only care whether a match exists.

EXISTS vs IN

WHERE ID IN (...)

Checks whether a value belongs to a list.

WHERE EXISTS (...)

Checks whether a matching row exists.

EXISTS is often preferred for correlated checks, while IN is useful when comparing against a known list or the result of a subquery.

5. ANY

WHERE Salary > ANY
(
   50000,
   60000,
   70000
)

Meaning:

Greater than at least one value.

So 55000 > ANY (...) is true because it is greater than 50000.

6. ALL

WHERE Salary > ALL
(
   50000,
   60000,
   70000
)

Meaning:

Greater than every value.

Only salaries above 70000 qualify.

Easy Memory Trick

ANY:

At least one

ALL:

Every one

7. UNION

Combines results from multiple queries and removes duplicates.

SELECT Name FROM A

UNION

SELECT Name FROM B;

If table A has Rahul, Priya and table B has Priya, Amit, the result is:

Rahul
Priya
Amit

Duplicate Priya is removed.

8. UNION ALL

Same as UNION, but keeps duplicates.

SELECT Name FROM A

UNION ALL

SELECT Name FROM B;

Result:

Rahul
Priya
Priya
Amit

UNION vs UNION ALL

9. INTERSECT

Returns only common rows.

If table A has:

Rahul
Priya

and table B has:

Priya
Amit

Result:

Priya

MySQL does not support INTERSECT directly. PostgreSQL, SQL Server, and Oracle do.

10. EXCEPT

Returns rows from the first query that are not present in the second.

If table A has:

Rahul
Priya

and table B has:

Priya

Result:

Rahul

MySQL commonly uses alternatives such as LEFT JOIN or NOT EXISTS because it does not support EXCEPT directly.

Summary Table

Interview Questions

1. What is the difference between a subquery and a correlated subquery?

• A subquery executes independently, typically once.
• A correlated subquery depends on the outer query and executes once for each row processed by the outer query.

2. Difference between UNION and UNION ALL?

• UNION removes duplicate rows.
• UNION ALL keeps duplicates and is generally faster because it does not perform duplicate elimination.

3. Difference between EXISTS and IN?

• IN checks whether a value exists in a list or subquery result.
• EXISTS checks whether a matching row exists and is commonly used with correlated subqueries.

4. Difference between ANY and ALL?

• ANY requires the condition to be true for at least one value.
• ALL requires the condition to be true for every value.

Should You Memorize ANY, ALL, INTERSECT, and EXCEPT?

For most backend interviews:

• Master Subqueries
• Master Correlated Subqueries
• Master EXISTS
• Master UNION and UNION ALL

Know what ANY, ALL, INTERSECT, and EXCEPT do, but don't spend much time memorizing their syntax. They are asked less frequently, and some are not supported by every SQL database.

The next topic is Window Functions, which helps solve ranking, running total, and "Nth highest" problems without complex subqueries.