Types of Subqueries in SQL: Exploring Scalar, Multi-row, and Correlated Subqueries

Subqueries are a crucial component of many SQL queries. Due to their support for complex queries, dynamic filtering, and calculations dependent on the results of previous queries, they enable more efficient and precise data retrieval from the database. This article provides a detailed guide on the types of subqueries in SQL, their functions, and use cases.

What Are Subqueries?

In SQL, a subquery is a nested query used as part of a larger SQL statement. When you use SELECT, UPDATE, INSERT, or DELETE within another SQL statement, you are creating a subquery. The subquery serves as a condition in the main query, providing it with a subset of data that satisfies specific criteria.

Types of SQL Subqueries

1. Scalar Subqueries

Scalar subqueries are the simplest form of subqueries and return a single value, either one column or one row. These subqueries are typically used in the SELECT, WHERE, HAVING, or FROM clauses and combined with a comparison operator. The most common use case for scalar subqueries is to reference an aggregate value such as average, maximum, minimum, sum, or count. Here is an example of how scalar subqueries are used:

SELECT employee_id, salaryFROM employeesWHERE salary  (SELECT AVG(salary) FROM employees);

Note: If the subquery returns zero rows, the value returned by the subquery expression is NULL.

2. Multi-row Subqueries

A multi-row subquery can return one or several rows, which the main query can then utilize in its operations. Much like scalar subqueries, these can be used in the SELECT statement and combined with the HAVING, WHERE, and FROM clauses. However, they are applied using logical operators such as ALL, IN, NOT IN, and ANY. Here is an example of how multi-row subqueries are used:

SELECT customer_id, order_totalFROM ordersWHERE order_total IN (SELECT order_total FROM order_details WHERE product_id  100);

3. Correlated Subqueries

Correlated subqueries are a specific type of subquery that rely on the output of the main query. In these queries, the main query and the subquery are interdependent, and as a result, the subquery cannot be executed before the main query. Each row in the output of the main query triggers a correlated subquery, making this type of subquery also known as repeated subqueries. Here is an example of how correlated subqueries are used:

SELECT e.employee_id, e.employee_name, (SELECT COUNT(*) FROM orders o WHERE o.employee_id  e.employee_id) AS order_countFROM employees e;

Conclusion

Subqueries are a powerful tool in SQL that can significantly enhance query performance and efficiency. Understanding the different types of subqueries—scalar, multi-row, and correlated—and their specific use cases can help you write more effective and maintainable SQL queries.

For more detailed information on SQL subqueries and their effective use, please refer to the following resources:

Read more in this article