Subquery

A subquery in SQL is a query that is nested within another query. It is used to retrieve data that will be used in the main query as a filter condition, or as a source of data for a calculation. The SQL subqueries are used to retrieve data that will be used as a condition in the outer query. Subqueries are enclosed within parentheses and can be used in various SQL statements such as SELECT, INSERT, UPDATE, and DELETE.

Subqueries are used to retrieve data from one or more tables to be used as a condition in the main query. For example, a subquery can be used to find the average salary of employees in a specific department, which can then be used in the main query to find all employees whose salary is greater than the average salary of their department.

Example

Here is an example of a subquery used in the WHERE clause of a SELECT statement:

SELECT *
FROM customers
WHERE customer_id IN 
(SELECT customer_id FROM orders WHERE order_date > '2022-01-01');

In this example, the subquery is used to retrieve the customer IDs of all orders that were placed after January 1, 2022. The main query then uses those customer IDs to retrieve all of the corresponding customer records from the customers table.

Subqueries can be either correlated or non-correlated. A correlated subquery is one that refers to a column in the outer query, whereas a non-correlated subquery is independent of the outer query.

Correlated subquery

Here is an example of a correlated subquery:

SELECT *
FROM orders o
WHERE order_total > 
(SELECT AVG(order_total) FROM orders WHERE customer_id = o.customer_id);

In this example, the subquery calculates the average order total for a specific customer, which is then used as a filter condition to retrieve all orders with a total greater than that average for each corresponding customer.

The SQL subquery can also be used in conjunction with various SQL operators, such as IN, ANY, ALL, EXISTS, and NOT EXISTS.

Subqueries can be a powerful tool in SQL for filtering and manipulating data. However, it is important to use them carefully and efficiently, as poorly constructed subqueries can significantly impact query performance.