A subquery, also known as a nested query or subselect, is a SELECT
query embedded within the WHERE
or HAVING
clause of another SQL query. The data returned by the subquery is used by the outer statement in the same way a literal value would be used.
Subqueries provide a convenient and efficient way to handle queries that depend on the results from another query. They are similar to normal SELECT
statements, but with some restrictions. The main ones include:
SELECT *
in a subquery is not allowed unless the table referred to has only one column. Multiple columns can be used if the purpose is row comparison.IN
or NOT IN
.UNION
; only a single SELECT
statement is permitted.Subqueries are commonly used with the SELECT
statement, but they can also be used within INSERT
, UPDATE
, or DELETE
statements, or even inside another subquery.
The following query will retrieve details of customers whose order value in the orders table exceeds $5000. Note the use of the DISTINCT
keyword in the subquery to eliminate duplicate cust_id values from the result set.
SELECT * FROM customers
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders
WHERE order_value > 5000);
Tip: A subquery can return various forms of data, such as a single value, a single row, a single column, or a table containing multiple rows and columns.
Subqueries are also applicable in INSERT
statements. Here's a practical example:
INSERT INTO premium_customers
SELECT * FROM customers
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders
WHERE order_value > 5000);
The statement above inserts records into a table named premium_customers using data from a subquery. In this case, premium customers are those who have placed orders worth more than $5000.
Tip: Visit the tutorial on SQL cloning tables to discover how to efficiently insert multiple rows into a table from another table using the INSERT ... SELECT
statement.
You can use subqueries with the UPDATE
statement to update one or more columns in a table. Here's how:
UPDATE orders
SET order_value = order_value + 10
WHERE cust_id IN (SELECT cust_id FROM customers
WHERE postal_code = 75016);
The previous query updates the order values in the orders table for customers residing in the area with postal code 75016, increasing their current order value by $10.
Likewise, you can use subqueries with the DELETE
statement to remove one or more rows from a table. Here's an example:
DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM order_details
WHERE product_id = 5);
The SQL query shown above deletes orders from the orders table where the product's product_id is 5.