This SQL tutorial provides an introduction to Subqueries in SQL Server. In this tutorial you’ll find many explanations, examples and exercises. For this lesson’s exercises use this link.
In general, a subquery is a query within another query, the subquery is used to return data that will be used in the main query. Subqueries can be used in various places within a query (such as: SELECT, FROM, WHERE), this tutorial explains how to use subqueries in the SQL Server WHERE clause.
Using SQL Server Subqueries
Subqueries are widely used to answer a question within another question. For example, who are the employees whose salary is greater than that of employee no. 54?
To solve this question, you need to answer two questions, each in a separate query :
- What is employee no. 54’s salary?
SELECT salary FROM employees WHERE employee_id = 54 -- Let's assume this is the result : salary ------ 7200
- Who earns more than employee no. 54?
SELECT first_name, last_name , salary FROM employees WHERE salary > 7200
Instead of executing each query separately, you can combine the two queries, placing one query inside the other.
Basic SQL Server Subquery Syntax
SELECT … FROM table WHERE condition (SELECT … FROM table)
Guidelines
SELECT first_name , last_name , salary
FROM employees
WHERE salary > ( SELECT salary
FROM employees
WHERE employee_id = 54)
- The subquery is executed once before the main query, then the result returned by the subquery is submitted to the main query (in the SQL Server example provided above, the subquery determines the salary of employee no. 54, then the main query takes the result of that subquery and uses this information to display all the employees who earn more than this amount).
- The subquery must be enclosed by round brackets.
- Place subqueries on the right side of the comparison condition.
- A subquery cannot be placed in the SQL Server GROUP BY Clause.
- Sub-queries can be divided into two main categories :
- Single Row Subqueries – subqueries that return zero or one row to the outer SQL statement.
- Multiple Row Subqueries – subqueries that return more than one row to the outer SQL statement.
SQL Server Single Row Subquery
You may use comparisson operators (also referred as single-row operators) in the outer query to handle a subquery that returns a single value. This SQL Server example would retrieve all employees whose department number is the same as that of employee 64.
SELECT last_name , first_name , salary , department_id
FROM employees
WHERE department_id = ( SELECT department_id
FROM employees
WHERE employee_id = 64)
- The subquery must return a single row; a subquery written without a SQL Server WHERE Clause (hence usually returns more than one row) will generate an error.
- The subquery must return a single column; writing more than one column in the subquery’s SELECT clause will result in an error.
- If you need to display all employees who work at the same department with employee number 54, not including employee 54, simply add this condition: “AND employee_id 54”
SELECT last_name , first_name , salary , department_id
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
WHERE employee_id = 54)
AND employee_id <> 54
- You can use group functions in a subquery to return a single row. For example: the employees who earn more than the average salary in department no. 60:
SELECT first_name , last_name , salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department_id = 60)
- A subquery also can be used in the SQL Server HAVING Clause. This SQL Server example would retrieve the average summary of all departments whose average salary is greater than the average salary in department 90:
SELECT department_id , AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary)
FROM employees
WHERE department_id = 90)
- The next SQL Server example would retrieve all employees who earn more than employee no. 54 and work at the same department as employee no. 42, not including employee no. 42. This SQL Server example consists of three queries, main query and two subqueries. The subqueries are executed first, generating the query results. Then the main query is processed and uses the values returned by these subqueries.
SELECT first_name , last_name , salary
FROM employees
WHERE salary > (SELECT salary
FROM employees
WHERE employee_id = 54 )
AND department_id = (SELECT department_id
FROM employees
WHERE employee_id = 42)
AND employee_id <> 42
SQL Server Multiple Row Subquery
You may use the IN, ANY, or ALL operators (multiple row operators) in the outer query to handle a subquery that returns multiple rows, the multiple row operators expect one or more values.
The column below represents the salaries of different employees whose department number equals 80, the following examples will use these values as the multiple row subquery result.
SELECT salary FROM employees WHERE department_id = 80
| Salary |
|
4300 |
|
5200 |
|
6700 |
|
8200 |
|
12500 |
SQL Server IN Operator
The SQL Server IN operator allows comparing a column with a list of values returned from the subquery. This SQL Server example would retrieve all employees whose salary is equal to one of the salaries of employees in department 80:
SELECT first_name , last_name , salary
FROM employees
WHERE salary IN (SELECT salary
FROM employees
WHERE department_id = 80)
- In fact, the main query would look like the following to the Database Server :
SELECT first_name , last_name , salary FROM employees WHERE salary IN (4300,5200,6700,8200,12500)
- To display all employees whose salary is equal to one of the salaries of employees in department 80, excluding the employees in department 80, simply use this condition : “AND department_id 80”.
SELECT first_name , last_name , salary
FROM employees
WHERE salary IN (SELECT salary
FROM employees
WHERE department_id = 80)
AND department_id <> 80
SQL Server ANY Operator
The SQL Server ANY operator allows comparing a column with at least one of the values returned from the subequry. When using this operator, it is possible to work with the following comparison methods : >ANY, <ANY, =ANY
> ANY
The following SQL Server example would retrieve all employees whose salary is higher than at least one of the salaries of the employees in department 80.
When seeking to know which value is greater than at least one of the values in a specific list, you actually seek to find the value that is higher than the minimum (because the requested value must be greater than at least one of the values in the list, no matter which value).
SELECT first_name , last_name , salary
FROM employees
WHERE salary > ANY (SELECT salary
FROM employees
WHERE department_id = 80)
< ANY
The following SQL Server example would retrieve all employees whose salary is lower than at least one of the salaries of the employees in department 80.
When seeking to know which value is less than at least one of the values in a specific list, you actually seek to find the value that is less than the maximum (because the requested value must be less than at least one of the values in the list, no matter which value).
SELECT first_name , last_name , salary
FROM employees
WHERE salary < ANY (SELECT salary
FROM employees
WHERE department_id = 80)
= ANY
The following SQL Server example would retrieve all employees whose salary is equal to at least one of the salaries of employees in department 80. “=ANY” is equivalent to “IN”.
SELECT first_name , last_name , salary
FROM employees
WHERE salary = ANY (SELECT salary
FROM employees
WHERE department_id = 80)
ALL Operator
The SQL Server ALL operator allows comparing a column with all of the values returned from the subquery. When using this operator, it is possible to work with the following comparison methods: ALL.
> ALL
The followng SQL Server example would retrieve all employees whose salary is higher than the salaries of all employees in department 80.
When seeking to find the value that is greater than all values in a certain list, you actually look for a value that is greater than the maximum (for a value to be greater than all of the values, it must necessarily be greater than the maximum value in the list of values).
SELECT first_name , last_name , salary
FROM employees
WHERE salary > ALL (SELECT salary
FROM employees
WHERE department_id = 80)
< ALL
The following SQL Server example would retrieve all employees whose salary is lower than all of the salaries of all employees in department 80.
When seeking to find the value that is less than all values in a certain list, you actually look for a value that is less than the minimum (for a value to be less than all of the values, it must necessarily be less than the minimum value in the list of values).
SELECT first_name , last_name , salary
FROM employees
WHERE salary < ALL (SELECT salary
FROM employees
WHERE department_id = 80)
- The SQL Server = ALL operator attempts to retrieve a value that equals to all of the values returned from the subquery (the employee whose salary is worth 4300, and also 5200, and also 6700, and also 8200 and also 12500). This condition seeks to carry out an illogical operation, and will therefore usually not be used.
