A subquery is a query inside another query.
It is used to obtain values that are then compared or filtered in the main query.
The subquery can appear in different parts of the SQL statement: in WHERE, HAVING, FROM, or SELECT.
WHERE
Used to filter results according to the value returned by a subquery.
SELECT Name, Salary
FROM Employees
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
);
Returns employees with a salary higher than the average.
HAVING
Allows applying conditions on aggregate functions using subqueries.
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > (
SELECT AVG(Salary)
FROM Employees
);
Shows departments with an average salary above the overall average.
SELECT Name, Salary
FROM Employees
WHERE Salary BETWEEN (
SELECT MIN(Salary) FROM Employees WHERE Department = 'Sales'
)
AND (
SELECT MAX(Salary) FROM Employees WHERE Department = 'Sales'
);
BETWEEN
Checks if a value is within a range returned by a subquery.
Returns employees whose salaries fall within the range of the Sales department.
SELECT Name
FROM Employees
WHERE Department IN (
SELECT DepartmentName
FROM Departments
WHERE City = 'Barcelona'
);
IN
Checks whether a value belongs to a set of results returned by a subquery.
Selects employees working in departments located in Barcelona.
SELECT Name
FROM Clients
WHERE Name LIKE (
SELECT CONCAT(Prefix, '%')
FROM Settings
WHERE Key = 'client_prefix'
);
LIKE
Although commonly used with text patterns, it can also be combined with subqueries.
Returns clients whose names start with the prefix defined in the Settings table.
SELECT Name
FROM Employees
WHERE Salary > ALL (
SELECT Salary
FROM Employees
WHERE Department = 'Administration'
);
ANY, SOME, ALL
Allow comparing a value with all or some of the results of a subquery.
Returns employees who earn more than all employees in Administration.
SELECT Name
FROM Employees
WHERE Salary > ANY (
SELECT Salary
FROM Employees
WHERE Department = 'Sales'
);
Returns employees who earn more than at least one worker in Sales.
SELECT Name
FROM Clients c
WHERE EXISTS (
SELECT *
FROM Orders o
WHERE o.Client_ID = c.Client_ID
);
EXISTS
Checks if the subquery returns at least one row.
Shows clients who have at least one registered order.
| Operator | Main use | Returns when… |
|---|---|---|
| WHERE | Filter by condition | Matches the subquery value |
| HAVING | Filter aggregate groups | The condition on the aggregation is true |
| BETWEEN | Value range | Value is within the returned range |
| IN | Set inclusion | Value belongs to the list |
| LIKE | Text pattern | Matches the returned pattern |
| IS NULL | Null values | Result is null or unassigned |
| ANY / SOME | Partial comparison | Condition is true for at least one value |
| ALL | Total comparison | Condition is true for all values |
| EXISTS | Existence | Subquery returns one or more rows |
