SQL Subqueries

Previous Page Next Page

What Is a Subquery?

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:

A subquery must always appear within parentheses.
A subquery must return only one column. Using 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.
Subqueries that return more than one row can only be used with multiple value operators such as IN or NOT IN.
A subquery cannot be a 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.

Subqueries with the SELECT Statement

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.

Example

Try this code »

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.

Note: A subquery can be nested within the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery.

Subqueries with the INSERT Statement

Subqueries are also applicable in INSERT statements. Here's a practical example:

Example

Try this code »

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.

Subqueries with the UPDATE Statement

You can use subqueries with the UPDATE statement to update one or more columns in a table. Here's how:

Example

Try this code »

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.

Subqueries with the DELETE Statement

Likewise, you can use subqueries with the DELETE statement to remove one or more rows from a table. Here's an example:

Example

Try this code »

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.

Previous Page Next Page