Skip to main content

Where can subqueries be used?

SQLJunior~1 min read
Suggest an edit

Subqueries in SQL can be used in several places inside the main query:

1. In WHERE

  • To filter records based on the result of another query. Example:
sql
SELECT Name, Age FROM Employees WHERE DepartmentID = (SELECT ID FROM Departments WHERE Name = 'Sales');

2. In FROM

  • The subquery creates a temporary table, which you can then work with. Example:
sql
SELECT AVG(Age) AS AverageAge FROM (SELECT Age FROM Employees WHERE DepartmentID = 101) AS Subtable;

3. In SELECT

  • To compute a value right inside the list of selected columns. Example:
sql
SELECT Name, (SELECT Name FROM Departments WHERE Departments.ID = Employees.DepartmentID) AS Department FROM Employees;

4. In HAVING

  • To filter groups after aggregate functions. Example:
sql
SELECT DepartmentID, COUNT(*) AS EmployeeCount FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > (SELECT AVG(EmployeeCount) FROM Employees GROUP BY DepartmentID);

Put simply, subqueries can be used anywhere SQL lets you insert an expression, to dynamically get data for filtering, calculations, or aggregation.

Short Answer

Interview ready
Premium

A concise answer to help you respond confidently on this topic during an interview.

Suggest an edit