Backend interview prep

Start

SQL Subqueries Interview Questions

Master SQL Subqueries with interview-focused questions covering scalar, correlated, nested, EXISTS, NOT EXISTS, IN, ANY, ALL, performance optimization, and enterprise production best practices.

Introduction

A Subquery is a query written inside another SQL query.

Subqueries allow SQL to solve complex problems using multiple steps.

Instead of writing temporary tables,

we can write

  • Nested Queries
  • Correlated Queries
  • EXISTS Queries
  • IN Queries
  • Scalar Queries

Subqueries are heavily used in

  • Banking Applications
  • Reporting Systems
  • Analytics
  • Payroll Systems
  • E-Commerce Applications

They are one of the most frequently asked SQL interview topics.

Subquery Architecture

flowchart LR

OuterQuery["Outer Query"] --> Subquery

Subquery --> Database

Database --> Result

Result --> OuterQuery["Outer Query"]

Sample Tables

Employee

ID Name Department Salary
101 John IT 90000
102 Alice HR 80000
103 David IT 95000
104 Bob Finance 70000

Department

ID Department
1 IT
2 HR
3 Finance

1. What is a Subquery?

Answer

A Subquery is a SQL query written inside another SQL query.

It executes first,

and its result is used by the outer query.

Basic Structure

SELECT *
FROM Employee
WHERE salary >
(
    SELECT AVG(salary)
    FROM Employee
);

2. Why are Subqueries used?

Subqueries help

  • Break complex problems
  • Avoid temporary tables
  • Improve readability
  • Reuse query results

3. Where can Subqueries be used?

Subqueries can appear in

  • SELECT
  • FROM
  • WHERE
  • HAVING
  • INSERT
  • UPDATE
  • DELETE

Subquery Locations

SELECT

FROM

WHERE

HAVING

4. What is a Scalar Subquery?

A Scalar Subquery returns

exactly

one row

and

one column.

Example

SELECT name,
(
SELECT AVG(salary)
FROM Employee
) AS average_salary
FROM Employee;

5. What happens if a Scalar Subquery returns multiple rows?

An error occurs.

Because only

one value

is expected.

6. What is a Multiple Row Subquery?

Returns

multiple rows.

Example

SELECT *
FROM Employee
WHERE department
IN
(
SELECT department
FROM Department
);

7. What is a Correlated Subquery?

A Correlated Subquery

depends on

the outer query.

It executes

once

for every row

returned by the outer query.

Correlated Subquery

SELECT e.name
FROM Employee e
WHERE salary >
(
SELECT AVG(salary)
FROM Employee
WHERE department = e.department
);

Correlated Query Flow

flowchart LR

OuterRow["Outer Row"] --> Subquery

Subquery --> Comparison

Comparison --> NextRow["Next Row"]

8. Why are Correlated Subqueries slower?

Because the subquery

may execute

once for each row

returned by the outer query.

9. What is EXISTS?

EXISTS checks

whether

the subquery

returns at least one row.

EXISTS Example

SELECT *
FROM Department d
WHERE EXISTS
(
SELECT 1
FROM Employee e
WHERE e.department=d.department
);

10. Why use SELECT 1 inside EXISTS?

Only existence

is checked.

The selected value

is ignored.

Using SELECT 1 is a common convention to indicate that only row existence matters.

11. What is NOT EXISTS?

Returns rows

where

the subquery

returns no rows.

NOT EXISTS Example

SELECT *
FROM Department d
WHERE NOT EXISTS
(
SELECT 1
FROM Employee e
WHERE e.department=d.department
);

12. What is IN?

IN compares

one value

against

multiple values.

IN Example

SELECT *
FROM Employee
WHERE department
IN
(
SELECT department
FROM Department
);

13. Difference between IN and EXISTS?

IN EXISTS
Compares Values Checks Existence
Better for Small Lists Better for Large Data
Returns Matching Values Returns Boolean Result

14. What is ANY?

ANY returns TRUE

if

the comparison

matches

at least one value.

ANY Example

SELECT *
FROM Employee
WHERE salary >
ANY
(
SELECT salary
FROM Employee
WHERE department='HR'
);

15. What is ALL?

ALL returns TRUE

only if

the condition

matches

every value.

ALL Example

SELECT *
FROM Employee
WHERE salary >
ALL
(
SELECT salary
FROM Employee
WHERE department='HR'
);

16. What is a Nested Subquery?

A Nested Subquery

contains

another subquery.

Example

SELECT *
FROM Employee
WHERE department IN
(
SELECT department
FROM Department
WHERE id IN
(
SELECT department_id
FROM Projects
)
);

Nested Query

Outer Query

↓

Subquery

↓

Subquery

17. Can Subqueries return tables?

Yes.

Subqueries inside

FROM

return virtual tables.

FROM Subquery

SELECT *
FROM
(
SELECT *
FROM Employee
WHERE salary>80000
) e;

18. Can Subqueries be used in UPDATE?

Yes.

Example

UPDATE Employee
SET salary =
(
SELECT AVG(salary)
FROM Employee
)
WHERE id=101;

19. Can Subqueries be used in DELETE?

Yes.

DELETE
FROM Employee
WHERE department IN
(
SELECT department
FROM ClosedDepartments
);

20. Banking Example

Accounts above average balance

SELECT *
FROM Accounts
WHERE balance >
(
SELECT AVG(balance)
FROM Accounts
);

21. HR Example

Employees earning

above department average

SELECT e.*
FROM Employee e
WHERE salary >
(
SELECT AVG(salary)
FROM Employee
WHERE department=e.department
);

22. E-Commerce Example

Products

never ordered

SELECT *
FROM Product p
WHERE NOT EXISTS
(
SELECT 1
FROM Orders o
WHERE o.product_id=p.id
);

23. Healthcare Example

Doctors

without appointments

SELECT *
FROM Doctor d
WHERE NOT EXISTS
(
SELECT 1
FROM Appointment a
WHERE a.doctor_id=d.id
);

24. Social Media Example

Users

without posts

SELECT *
FROM Users u
WHERE NOT EXISTS
(
SELECT 1
FROM Posts p
WHERE p.user_id=u.id
);

25. Production Example

Top-performing employees

above company average

SELECT *
FROM Employee
WHERE salary >
(
SELECT AVG(salary)
FROM Employee
);

26. Common Mistakes

  • Returning multiple rows from scalar subqueries
  • Using correlated subqueries unnecessarily
  • Confusing EXISTS with IN
  • Ignoring indexes
  • Writing deeply nested queries

27. Performance Tips

  • Prefer JOINs when appropriate.
  • Use EXISTS for existence checks.
  • Index columns referenced by subqueries.
  • Avoid unnecessary correlated subqueries.
  • Review execution plans.
  • Consider CTEs for improved readability.

28. Subquery vs JOIN

Subquery JOIN
Nested Query Combines Tables
Easy to Read Often Faster
Good for Simple Logic Better for Large Data
May Execute Multiple Times Optimizer Can Improve Execution

29. Best Practices

  • Use EXISTS for existence checks.
  • Avoid unnecessary nesting.
  • Use aliases.
  • Keep subqueries readable.
  • Use JOIN when more efficient.
  • Test execution plans.
  • Index filter columns.
  • Avoid returning unnecessary columns.
  • Keep scalar subqueries truly scalar.
  • Use CTEs for very complex logic.

30. Query Workflow

flowchart LR

OuterQuery["Outer Query"] --> Subquery

Subquery --> Result

Result --> OuterQuery["Outer Query"]

Enterprise Best Practices

  • Use scalar subqueries only when a single value is guaranteed.
  • Prefer EXISTS over IN for large datasets when checking existence.
  • Replace expensive correlated subqueries with JOINs or window functions where appropriate.
  • Keep subqueries simple and maintainable.
  • Use indexes on referenced columns.
  • Analyze execution plans regularly.
  • Use CTEs for multi-step business logic.
  • Avoid deeply nested subqueries.
  • Benchmark different query approaches.
  • Document complex SQL for future maintenance.

Quick Revision

Topic Key Point
Subquery Query Inside Query
Scalar Subquery One Row, One Column
Multiple Row Subquery Returns Multiple Rows
Correlated Subquery Depends on Outer Query
EXISTS Checks Existence
NOT EXISTS Checks Absence
IN Compare Multiple Values
ANY Match At Least One
ALL Match Every Value
FROM Subquery Virtual Table

Interview Tips

Interviewers frequently ask

  • What is a Subquery?
  • Types of Subqueries.
  • Scalar vs Correlated Subquery.
  • EXISTS vs IN.
  • ANY vs ALL.
  • Correlated Subquery vs JOIN.
  • Why are correlated subqueries slower?
  • Can subqueries be used in UPDATE?
  • Can subqueries be used in DELETE?
  • How do you optimize subqueries?

A strong interview explanation is:

"A subquery is a query nested inside another SQL statement. The inner query executes first, and its result is used by the outer query. SQL supports scalar, multiple-row, correlated, and nested subqueries. EXISTS is generally preferred for existence checks on large datasets, while JOINs or window functions may outperform correlated subqueries in many production scenarios. Proper indexing and execution plan analysis are essential for optimizing subquery performance."

Summary

Subqueries are a powerful SQL feature that enables complex data retrieval through nested queries. They support use cases such as filtering, aggregation, existence checks, updates, and deletes. Understanding scalar, correlated, EXISTS, NOT EXISTS, IN, ANY, and ALL queries, along with their performance implications, is essential for writing efficient enterprise SQL.

Mastering subqueries prepares you for advanced SQL topics such as CTEs, Window Functions, Query Optimization, Execution Plans, and Performance Tuning.