SQL Subquery

In this tutorial, you will learn how to embed a query within another query in SQL.

What is a subquery?

A subquery, also known as a nested query or subselection, isSELECTembedded in another SQL query's WHERE or HAVINGqueries in the clause. The data returned by the subquery is used by the external statement in the same way as literal values.

Subqueries provide a simple and effective method for handling queries that depend on the results of another query. They are almost the same as ordinary SELECT statements, but with few restrictions. The most important points are as follows:

• The subquery must always appear within parentheses.

• The subquery must return only one column. This means that you cannot use SELECT *, unless the referenced table has only one column. If the purpose is row comparison, you can use a subquery that returns multiple columns.

• You can only use return multiple values operators (such asIN or NOT INoperator) for multiple rows of subqueries.

• Subqueries cannot beUNION. Only one SELECT statement is allowed.

Subqueries are most commonly used withSELECTused together with the statement, but can also be used inINSERT,UPDATEorDELETEused in the statement or in another subquery.

Subqueries with SELECT statements

The following statement only returns orders in the order table with an order value exceeding5000 dollars customer details. It should also be noted that we used the keywordDISTINCTDuplicate cust_id values have been removed from the result set.

SELECT　*　FROM customers
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders WHERE order_value >　5000);

Subqueries with INSERT statements

Subqueries can also be used with INSERT statements. Here is an example:

INSERT INTO premium_customers　
SELECT　*　FROM customers　
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders WHERE order_value >　5000);

The above statement will insert the records of premium customers into the table named premium_customers using the data returned by the subquery. Here, premium customers are those who have placed orders with a value exceeding5000 dollars customers.

Subqueries with UPDATE statements

You can also combine subqueries with UPDATE statements to update single or multiple columns in a table, as shown below:

UPDATE orders
SET order_value = order_value　+　10
WHERE cust_id IN (SELECT cust_id FROM customers WHERE postal_code =　75016);

by increasing the current order value by10US dollars, the above statement will update the orders (orders) table where the postal code is75016the order values of customers from the regions.

Subquery with DELETE statement

Similarly, you can combine subqueries with DELETE statements to delete single or multiple rows from a table, as shown below:

DELETE FROM orders
DELETE FROM orders WHERE order_id IN (SELECT order_id FROM order_details WHERE product_id =　5);

The SQL statement in the above example will select records from the table that containsproduct_idfor5delete these orders from the order table of the product.