Subqueries

A subquery is a query that is nested inside another query. SQL subqueries are also called
nested queries or inner queries.

Following are the rules to be followed while writing subqueries −

• Subqueries must be enclosed within parentheses.

• Subqueries can be nested within another subquery.
• A subquery must contain the SELECT query and the FROM clause always.
• A subquery consists of all the clauses an ordinary SELECT clause can contain:
GROUP BY, WHERE, HAVING, DISTINCT, TOP/LIMIT, etc. However, an
ORDER BY clause is only used when a TOP clause is specified. It can't include
COMPUTE or FOR BROWSE clause.
• A subquery can return a single value, a single row, a single column, or a whole table.
They are called scalar subqueries.

Types of Subqueries

1) Single Row Subquery

2) Multiple row subquery
3) Correlated Subquery

1. Single-Row Subquery

Sub query which returns single row output. They mark the usage of single row comparison
operators, when used in WHERE conditions.

Single-row subqueries are commonly used when we want

• A specific value, such as a maximum, minimum, average, or count.

• To filter the main query based on a precise condition (e.g., comparing each row in the
main query with the single result from the subquery).

The key to these subqueries is that they use an aggregate function (like MAX, MIN, AVG,
SUM, or COUNT) in most cases, since these functions return a single value.

Example 01

1
From a employee table above,

a) Write using subquery, a query that return first_name, salary and department_id of the
employee with the minimum salary.

Answer
SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

b) How to get employees having the salary below the average salary

Answer
SELECT first_name, salary, department_id
FROM employees
WHERE salary < (SELECT AVG(salary) FROM employees);

2
 c) Which departments have at least one employee earning less than the average salary of
all employees?

Answer
SELECT department_id, MIN (salary)
FROM employees
GROUP BY department_id
HAVING MIN (salary) < (SELECT AVG (salary) FROM employees);

d) Which employee has the highest salary?

e) Find the employee whose salary is exactly the same as the average salary.
f) Which employee works in the department with the most employees? (Hint: use
GROUP BY, ORDER BY, LIMIT)

2. Multiple row subquery

Multiple-row subqueries are nested queries that can return more than one row of results to the
parent query.
Multiple-row subqueries are used most commonly in WHERE and HAVING clauses. Since it
returns multiple rows, it must be handled by set comparison operators (IN, ALL, ANY).

IN operator is used to check if a value matches any value in a list or subquery. It’s useful
when you want to filter records that match one or more values from a specified set.

3
ANY operator compares a specified value to each value returned by the sub query. The ANY
operator checks if a comparison is true for at least one value returned by a subquery. It’s
often used with comparison operators (>, <, >=, <=, =, <>).
ALL compares a value to every value returned by a sub query. It checks if a comparison is
true for all values returned by a subquery. It’s often used with comparison operators (>, <, >=,
<=, =, <>).

Use of multiple row operators

• [> ALL] More than the highest value returned by the subquery
• [< ALL] Less than the lowest value returned by the subquery
• [< ANY] Less than the highest value returned by the subquery
• [> ANY] More than the lowest value returned by the subquery
• [= ANY] Equal to any value returned by the subquery (same as IN)

4
Example

Consider two tables

• employees table with columns id, first_name, salary, and department_id.

• departments table with columns department_id, name, and location_id.

a) Let’s find employees who work in departments located in either location 100 or
location 200.

Answer
SELECT first_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE location_id IN (100, 200));

5
 b) Which employees earn a salary greater than the average salary of employees in each
department?
c) List employees who work in the same department as any employee located at
location_id 100.
d) Which employees earn a higher salary than at least one employee in the 'HR'
department? (Hint: double nested)
e) Which employees work in departments with more than 3 employees? (Hint: use
GROUP BY, COUNT())

3. Correlated subquery

As opposed to a regular subquery, where the outer query depends on values provided by the
inner query, a correlated subquery is one where the inner query depends on values
provided by the outer query.
A correlated subquery is a subquery that references a column from the outer query, making
it execute for each row of the outer query.
This means that in a correlated subquery, the inner query is executed repeatedly, once for
each row that might be selected by the outer query.

6
Example
Suppose we have two tables, employee and department

a) Which employees earn a salary above the average salary of their respective
department?"

SELECT employee_id, first_name, salary, department_id

FROM employees E
WHERE salary > (SELECT AVG(salary)
FROM employees T
WHERE E.department_id = T.department_id);

7
Example
From tables, employee and departments

a) Which employees are managers of their own department?

SELECT first_name
FROM employees E
WHERE employee_id IN (
SELECT manager_id
FROM departments D
WHERE E.department_id = D.department_id
);

b) Which departments have more than 1 employee? (Hint: use COUNT(), and >)
c) Which employees work in a department that is managed by another employee?