SQL Subquery: A Complete Guide

An SQL subquery is a query inside another query. It is used in the WHERE or HAVING clause of an SQL statement. Subqueries let you specify the results of one query as an argument in another query.

When you’re writing an SQL query, you may want to specify a parameter based on the result of another query. For example, say you have a list of customers. You may want to get a list of customers who have placed orders worth a value over a certain amount. Order and customer information is stored within a different table.

That’s where the SQL subquery operation comes in. Subqueries, also known as nested queries, are SELECT queries within the WHERE clause of another SQL query. The main SQL query then uses the result of the subquery.

In this tutorial, we are going to discuss how to use subqueries in SQL, with reference to a few examples.

SQL Subquery: A Guide

An SQL subquery is a query within another query. They are used to run a query that depends on the results of another query. Subqueries let you do this without having to write two separate queries and copy-paste the results. Subqueries appear in a WHERE or HAVING clause.

Here is the syntax for a subquery in an SQL SELECT statement:

SELECT name
FROM products
WHERE supplier_id
IN (SELECT id FROM suppliers WHERE local = True);

This query selects the name of all products supplied by a local company. We use a subquery as an input to the SQL IN statement. The SQL IN statement returns all records from the “products” table where a supplier_id is in the results of our subquery.

We can use subqueries in INSERT, UPDATE, and DELETE statements.

A subquery must appear in parentheses. This distinguishes the inside of a subquery with another subquery.

You should make sure you select the relevant data in your subquery. In our subquery above, we selected the id from the suppliers table. If we had selected another column, our query would not work. This is because our main query depends on the supplier ID.

SQL Subquery Example: SELECT

Say you want to get a list of students who were on the honor roll, you could use a subquery. This assumes the honor roll information is in another table.

Let’s use an example to illustrate how SQL subqueries work. The following query will return a list of all customers who have made an order worth over $200:

SELECT * FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders
		WHERE cost > 200);

Our subquery returns the following:

(2 rows)

On the first line, we select every column from our customers table. Then, we specify that we only want to get customers whose customer ID is in a subquery. Our subquery selects all unique customer IDs from our orders table where the item cost more than $200. Records matching these conditions includes those customers in the results.

SQL Subquery: INSERT

We want to insert all the records of customers who have made a purchase worth over $200 into a table called high_value_customers. To do so, we can use an SQL subquery and an SQL INSERT command:

INSERT INTO high_value_customers
SELECT * FROM customers
WHERE id IN (SELECT DISTINCT customer_id FROM orders
		WHERE cost > 200);

If we query our high_value_customers table, we can see all of our high value customers in one table:

(2 rows)

SQL Subquery: UPDATE

We could use a subquery to update data within a table. For example, say we want to update the loyalty plans of all customers who have made a high-value purchase to Bronze. We could use the following query to perform that operation:

UPDATE customers
SET loyalty_plan = 'High Value'
WHERE id IN (SELECT DISTINCT customer_id FROM orders
		WHERE cost > 200);

When we query our customers table, we can see that the loyalty plans for our two high value customers have been updated:

(2 rows)

SQL Subquery: DELETE

You can use subqueries with the SQL DELETE statement to delete individual or multiple rows in a table.

Let’s write a command that deletes the orders of high value customers, which we moved to a new table in the database. To do this, we will need to depend on a subquery:

DELETE FROM orders
WHERE customer_id IN (SELECT id FROM customers
WHERE loyalty_plan = 'High Value');

We can see all the orders from our high value customers have been deleted. Our nested subquery returns:

(1 row)

Conclusion

An SQL subquery is a query within another query. Subqueries let you depend on the result of one query in another. Subqueries are specified in the HAVING or WHERE clauses of an SQL statement.

Subqueries make it easy to run a query that depends on the result of another query. In this guide, we have discussed how to write a subquery on an SQL server. We also talked about how to use subqueries in SELECT, INSERT, UPDATE, and DELETE SQL statements.

"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"

Venus, Software Engineer at Rockbot

Find Your Bootcamp Match

Do you want to learn more about coding in SQL? Check out our How to Learn SQL guide. This guide contains expert advice on learning SQL. You’ll also find a list of top courses to help you continue your learning journey.