SQL Subquery
Summary: in this tutorial, you will learn about the SQL subquery and how to use the subqueries to form flexible SQL statements.
SQL subquery basic
Consider the following employees
and departments
tables from the sample database:
Suppose you have to find all employees who locate in the location with the id 1700. You might come up with the following solution.
First, find all departments located at the location whose id is 1700:
SELECT
*
FROM
departments
WHERE
location_id = 1700;
Second, find all employees that belong to location 1700 by using the department id list of the previous query:
SELECT
employee_id, first_name, last_name
FROM
employees
WHERE
department_id IN (1 , 3, 8, 10, 11)
ORDER BY first_name , last_name;
This solution has two problems. To start with, you have looked at the departments
table to check which department belongs to the location in 1700. However, the original question was not referring to any specific departments; it referred to location 1700.
Because of the small data volume, you can get a list of departments easily. However, in a real system with high volume data, it might be problematic.
Another problem was that you have to revise the queries whenever you want to find employees locate in a different location.
A much better solution to this problem is to use a subquery. By definition, a subquery is a query nested inside another query such as SELECT
, INSERT
, UPDATE
, or DELETE
statement. In this tutorial, we are focusing on the subquery used with the SELECT
statement.
In this example, you can rewrite combine the two queries above as follows:
SELECT
employee_id, first_name, last_name
FROM
employees
WHERE
department_id IN (SELECT
department_id
FROM
departments
WHERE
location_id = 1700)
ORDER BY first_name , last_name;
The query placed within the parentheses is called a subquery. It is also known as an inner query or inner select. The query that contains the subquery is called an outer query or an outer select.
To execute the query, first, the database system has to execute the subquery and substitute the subquery between the parentheses with its result – a number of department id located at the location 1700 – and then executes the outer query.
You can use a subquery in many places such as:
- With the
IN
orNOT IN
operator - With comparison operators
- With the
EXISTS
orNOT EXISTS
operator - With the
ANY
orALL
operator - In the
FROM
clause - In the
SELECT
clause
SQL subquery examples
Let’s take some examples of using the subqueries to understand how they work.
SQL subquery with the IN or NOT IN operator
In the previous example, you have seen how the subquery was used with the IN
operator. The following example uses a subquery with the NOT IN
operator to find all employees who do not locate at the location 1700:
SELECT
employee_id, first_name, last_name
FROM
employees
WHERE
department_id NOT IN (SELECT
department_id
FROM
departments
WHERE
location_id = 1700)
ORDER BY first_name , last_name;
SQL subquery with the comparison operator
The following syntax illustrates how a subquery is used with a comparison operator:
comparison_operator (subquery)
where the comparison operator is one of these operators:
- Equal (=)
- Greater than (>)
- Less than (<)
- Greater than or equal ( >=)
- Less than or equal (<=)
- Not equal ( !=) or (<>)
The following example finds the employees who have the highest salary:
SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary = (SELECT
MAX(salary)
FROM
employees)
ORDER BY first_name , last_name;
In this example, the subquery returns the highest salary of all employees and the outer query finds the employees whose salary is equal to the highest one.
The following statement finds all employees whose salaries are greater than the average salary of all employees:
SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary > (SELECT
AVG(salary)
FROM
employees);
In this example, first, the subquery returns the average salary of all employees. Then, the outer query uses the greater than the operator to find all employees whose salaries are greater than the average.
SQL subquery with the EXISTS or NOT EXISTS operator
The EXISTS
operator checks for the existence of rows returned from the subquery. It returns true if the subquery contains any rows. Otherwise, it returns false.
The syntax of the EXISTS
the operator is as follows:
EXISTS (subquery )
The NOT EXISTS
the operator is opposite to the EXISTS
operator.
NOT EXISTS (subquery)
The following example finds all departments which have at least one employee with a salary is greater than 10,000:
SELECT
department_name
FROM
departments d
WHERE
EXISTS( SELECT
1
FROM
employees e
WHERE
salary > 10000
AND e.department_id = d.department_id)
ORDER BY department_name;
Similarly, the following statement finds all departments that do not have any employee with a salary greater than 10,000:
SELECT
department_name
FROM
departments d
WHERE
NOT EXISTS( SELECT
1
FROM
employees e
WHERE
salary > 10000
AND e.department_id = d.department_id)
ORDER BY department_name;
SQL subquery with the ALL operator
The syntax of the subquery when it is used with the ALL
the operator is as follows:
comparison_operator ALL (subquery)
The following condition evaluates to true if x
is greater than every value returned by the subquery.
x > ALL (subquery)
For example, suppose the subquery returns three values one, two, and three. The following condition evaluates to true if x
is greater than 3.
x > ALL (1,2,3)
The following query uses the GROUP BY
clause and MIN()
function to find the lowest salary by department:
SELECT
MIN(salary)
FROM
employees
GROUP BY department_id
ORDER BY MIN(salary) DESC;
The following example finds all employees whose salaries are greater than the lowest salary of every department:
SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary >= ALL (SELECT
MIN(salary)
FROM
employees
GROUP BY department_id)
ORDER BY first_name , last_name;
SQL subquery with the ANY operator
The following shows the syntax of a subquery with the ANY
operator:
comparison_operator ANY (subquery)
For example, the following condition evaluates to true if x is greater than any value returned by the subquery. So the condition x > SOME (1,2,3)
evaluates to true if x is greater than 1.
x > ANY (subquery)
Note that the SOME
the operator is a synonym for the ANY
operator so you can use them interchangeably.
The following query finds all employees whose salaries are greater than or equal to the highest salary in every department.
SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary >= SOME (SELECT
MAX(salary)
FROM
employees
GROUP BY department_id);
In this example, the subquery finds the highest salary of employees in each department. The outer query looks at these values and determines which employees’ salaries are greater than or equal to any highest salary by the department.
SQL subquery in the FROM clause
You can use a subquery in the FROM
clause of the SELECT
a statement as follows:
SELECT
*
FROM
(subquery) AS table_name
In this syntax, the table alias is mandatory because all tables in the FROM
the clause must have a name.
Note that the subquery specified in the FROM
a clause is called a derived table in MySQL or inline view in Oracle.
The following statement returns the average salary of every department:
SELECT
AVG(salary) average_salary
FROM
employees
GROUP BY department_id;
You can use this query as a subquery in the FROM
clause to calculate the average salary of departments as follows:
SELECT
ROUND(AVG(average_salary), 0)
FROM
(SELECT
AVG(salary) average_salary
FROM
employees
GROUP BY department_id) department_salary;
SQL Subquery in the SELECT clause
A subquery can be used anywhere an expression can be used in the SELECT
clause. The following example finds the salaries of all employees, their average salary, and the difference between the salary of each employee and the average salary.
SELECT
employee_id,
first_name,
last_name,
salary,
(SELECT
ROUND(AVG(salary), 0)
FROM
employees) average_salary,
salary - (SELECT
ROUND(AVG(salary), 0)
FROM
employees) difference
FROM
employees
ORDER BY first_name , last_name;
Now you should understand what an SQL subquery is and how to use subqueries to form flexible SQL statements.
0 Comments
CAN FEEDBACK
Emoji