When you’re writing an
SQL query, you may want to specify a parameter based on the result of another query. For example, you may want to get a list of customers who have placed orders worth a value over a certain amount, when sales order and customer information is stored within a different table.
That’s where the
SQL subquery operation comes in.
Subqueries, also known as
subselects or 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 break down the basics of
SQL and discuss how you can use them to write more advanced queries. But before that, let’s have a refresher on how typical
SQL queries work.
SQL Query Refresher
Databases store data using tables in categorical rows and lists, such as employees and suppliers. To retrieve data from a table, you need to run a query.
Queries typically begin with the
SELECT statement, which is used to specify which columns from a table should be returned by the query. Queries also often use the
FROM operator, which is used to state which table the command will query.
Here is the syntax for an
SELECT column_name FROM table_name WHERE conditions_are_met;
The following command will return the names of all employees from the
SELECT name FROM employees;
Here are the results of the above query:
In the above query, we return all the employee names within a table. But what if we only want to retrieve names that meet specific criteria? We can use the
SQL WHERE operator to filter records that match a specific condition and remove any rows from the result that doesn’t meet those conditions.
For example, you could use the following query to find the names of all sales associates:
SELECT name FROM employees WHERE title = 'Sales Associate';
Our query return this:
The queries we have explored so far all relate to one table. But if we want to get information from a table based on the result of another query, we would need to use a
subqueries to run a query within a query. The result of a
subquery directs the main query. In other words,
subqueries allow you to run a query that depends on the results of another query.
For example, if you wanted to get a list of students who were on the honor roll—and that information is stored in a different table—you could use a
Here’s the syntax for an
SELECT * FROM table1_name WHERE column1_name IN (SELECT column1_name FROM table2_name WHERE column1_name = 'XYZ');
Subqueries must be enclosed in parentheses, and must only return one column. Thus, you cannot use
SELECT * inside your
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);
subquery returns the following:
|Katyemail@example.com||Mountain View, CA||None||4|
Let’s break our query down. 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 selects all unique customer IDs from our
orders table where the item cost more than $200, and includes those customers in the results.
Insert, Update, and Delete Subqueries
Additionally, we can use
SQL subquery statements with the
SQL Subquery Using Insert
Here’s an example of an
INSERT query that will insert the records of all customers who have made a purchase worth over $200 into a table called
INSERT INTO high_value_customers SELECT * FROM customers WHERE id IN (SELECT DISTINCT customer_id FROM orders WHERE cost > 200);
Now, if we query our
high_value_customers table, the query result returned is as follows:
|Katyfirstname.lastname@example.org||Mountain View, CA||Gold||4|
SQL Subquery Using Update
Similarly, we could use a
subquery to update data within a table. For example, let’s say that we wanted 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:
|Katyemail@example.com||Mountain View, CA||High Value||4|
|Johnfirstname.lastname@example.org||Boston, MA||High Value||1|
SQL Subquery Using Delete
You can also use
subqueries with the
DELETE statement to delete individual or multiple rows in a table. Here’s an example of a
subquery that deletes the orders of high value customers, which we moved to a new table in the database:
DELETE FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE loyalty_plan = 'High Value');
When we run a select query on our orders, we can see that all of the orders from our high value customers have been deleted, and we are left with this order:
Subqueries make it easy to run a query that depends on the result of another query. In this guide, we have broken down the basics of
SQL queries, how to write a
subquery on an
SQL server, and how to use subqueries in
DELETE SQL statements.
Now you’re ready to start using
subqueries like an