How to Use a Subquery in a SELECT Statement

A subquery (also known as a nested query or inner query) is a query placed inside another query. The subquery is executed first, and its result is used by the outer query for further operations.

Subquery in a SELECT statement can be used to return a single value, multiple values or even a complete set of data depending on how it is structured. The most common type of subquery in a SELECT statement is the scalar subquery, which returns a single value that is used by the outer query.

Syntax

SELECT column1, column2,

(SELECT column_name FROM table_name WHERE condition) AS alias_name

FROM table_name

WHERE condition;

Explanation:

• (SELECT column_name FROM table_name WHERE condition): This is the inner query (subquery) that returns a value (or multiple values).
• alias_name: The result of the subquery can be aliased using a name to reference it in the outer query.
• Outer query: The outer query can make use of the result returned from the subquery, often to filter or compute additional information.

Examples of Using a Subquery in a SELECT Statement

To understand how to Use a Subquery in a SELECT statement, we will use the employee table as shown below:

Example 1: Using a Subquery to Retrieve a Single Value

Let’s say we want to find the name of the employee who has the highest salary in the IT department. We can use a subquery to retrieve the maximum salary from the IT department and then use it in the outer query.

SELECT name 
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE department = 'IT');

Output:

Explanation:

• The inner query (SELECT MAX(salary) FROM employees WHERE department = 'IT') returns the highest salary from the IT department.
• The outer query then selects the employee's name whose salary matches that maximum value.

Example 2: Using a Subquery to Filter Results

Let’s say we want to retrieve all employees who earn more than the average salary in the company. We can achieve this by using a subquery to calculate the average salary and then filter the employees based on that value.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Output:

Explanation:

• The inner query (SELECT AVG(salary) FROM employees) calculates the average salary of all employees.
• The outer query selects the names and salaries of employees who earn more than the calculated average.

Example 3: Using a Subquery to Retrieve Multiple Columns

We can also use subqueries to retrieve multiple columns. Let’s say we want to find the names of employees who work in the same department as Alice.

SELECT name
FROM employees
WHERE department = (SELECT department FROM employees WHERE name = 'Alice');

Output:

Explanation:

• The inner query (SELECT department FROM employees WHERE name = 'Alice') retrieves Alice’s department (HR).
• The outer query then retrieves the names of all employees who work in the HR department.
• The subquery finds that Alice works in the HR department, and the outer query returns the employees in the same department: Alice and Eve.

Conclusion

Subqueries in SQL provide a powerful way to perform complex queries by nesting one query inside another. They help us simplify the process of fetching data based on the result of another query which allows for more flexibility and efficiency in SQL statements. Whether you are calculating averages, filtering records or performing comparisons, subqueries are an essential tool in the SQL toolbox.