This article explores the use of subqueries in SELECT statements with detailed examples to illustrate their utility.
What is a Subquery?
A subquery is a query within another SQL query and embedded within the SELECT, FROM, WHERE, or other clauses. When placed in a SELECT statement, a subquery can:
1. Return a single value.
2. Return a list of values.
3. Return a table.
Syntax of Subqueries in SELECT
The basic syntax for a subquery in a SELECT statement is as follows:
SELECT column1, (SELECT column_name FROM table2 WHERE condition) AS alias_name
FROM table1;
Types of Subqueries in SELECT
1. Scalar Subqueries: Return a single value.
2. Column Subqueries: Return a single column of values.
3. Row Subqueries: Return a single row of values.
4. Table Subqueries: Return a table.
Scalar Subqueries
Scalar subqueries return a single value. They are often used in the SELECT list to include additional data derived from another table.
Example: Scalar Subquery in SELECT
Consider two tables, employees and departments:
Table: employees
| employee_id | employee_name | department_id |
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | 10 |
Table: departments
| department_id | department_name |
| 10 | Sales |
| 20 | Marketing |
Goal: Retrieve the employee names along with their department names.
SELECT employee_name,
(SELECT department_name FROM departments WHERE departments.department_id = employees.department_id) AS department_name
FROM employees;
Column Subqueries
Column subqueries return a single column of values. These are often used in SELECT statements to create derived columns.
Example: Column Subquery in SELECT
Consider the same employees and departments tables. Now, suppose you want to list all employees along with the maximum salary in their respective departments.
Table: salaries
| employee_id | salary |
| 1 | 50000 |
| 2 | 60000 |
| 3 | 55000 |
Goal: Retrieve the employee names along with the maximum salary in their department.
SELECT employee_name,
salary,
(SELECT MAX(salary) FROM salaries s WHERE s.employee_id IN (SELECT employee_id FROM employees e WHERE e.department_id = employees.department_id)) AS max_department_salary
FROM employees
JOIN salaries ON employees.employee_id = salaries.employee_id;
Row Subqueries
Row subqueries return a single row of values. They are useful when you need to compare rows from different tables.
Example: Row Subquery in SELECT
Using the employees table, suppose you want to find the employee with the highest salary in each department.
SELECT employee_id, employee_name, department_id, salary
FROM employees e
JOIN salaries s ON e.employee_id = s.employee_id
WHERE (department_id, salary) IN (
SELECT department_id, MAX(salary)
FROM employees e2
JOIN salaries s2 ON e2.employee_id = s2.employee_id
GROUP BY department_id
);
Table Subqueries
Table subqueries return a table of values. These are typically used in the FROM clause but can be used in SELECT statements in some SQL dialects.
Example: Table Subquery in SELECT
Suppose you want to list all employees and include their department names using a table subquery.
SELECT e.employee_name, d.department_name
FROM employees e
JOIN (SELECT department_id, department_name FROM departments) d ON e.department_id = d.department_id;
Subqueries with Aggregation
Subqueries can be combined with aggregate functions to perform complex calculations.
Example: Aggregated Subquery in SELECT
Using the employees, salaries, and departments tables, suppose you want to find the total salary for each department.
SELECT department_name,
(SELECT SUM(salary)
FROM employees e
JOIN salaries s ON e.employee_id = s.employee_id
WHERE e.department_id = d.department_id) AS total_salary
FROM departments d;
Subqueries with EXISTS
The EXISTS clause can be used with subqueries to check for the existence of rows in a subquery.
Example: Subquery with EXISTS
Suppose you want to list all departments that have at least one employee.
SELECT department_name
FROM departments d
WHERE EXISTS (SELECT 1
FROM employees e
WHERE e.department_id = d.department_id);
Practical Use Cases
1. Filtering with Subqueries
You can use subqueries to filter results based on complex conditions.
Example: List employees who earn more than the average salary of their department.
SELECT employee_name, salary
FROM employees e
JOIN salaries s ON e.employee_id = s.employee_id
WHERE salary > (SELECT AVG(salary)
FROM employees e2
JOIN salaries s2 ON e2.employee_id = s2.employee_id
WHERE e2.department_id = e.department_id);
2. Derived Tables
Subqueries can create derived tables for more readable and manageable queries.
Example: Using a derived table to calculate the average salary and list employees who earn above it.
SELECT employee_name, salary
FROM employees e
JOIN salaries s ON e.employee_id = s.employee_id
JOIN (SELECT department_id, AVG(salary) AS avg_salary
FROM employees e2
JOIN salaries s2 ON e2.employee_id = s2.employee_id
GROUP BY department_id) avg_salaries ON e.department_id = avg_salaries.department_id
WHERE salary > avg_salaries.avg_salary;
3. Combining Results from Multiple Queries
Subqueries can combine results from multiple queries for more complex data retrieval.
Example: List all departments along with the number of employees in each department.
SELECT department_name,
(SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.department_id) AS employee_count
FROM departments d;