How To Use SQL Subqueries

Learn how to filter query results with data from another table, run a query inside another query to merge results, and more in this basic guide to SQL subqueries.

a month ago   •   4 min read

By Solomon Esenyi
Table of contents

Prerequisites

To follow and fully understand this tutorial, you need to have the following:

What Are Subqueries?

A subquery is an SQL query that is nested in another SQL query. They assist queries in creating conditions for a WHERE clause to filter rows and perform operations on them. Subqueries can be used with SELECT, INSERT, UPDATE, and DELETE statements.

Example of a Subquery in a WHERE Clause

We want to fetch users in a database with a transaction amount greater than $1,000. For our example, we’ll have two tables, namely users and transactions which store the users and transaction information.

We can write a query that fetches all the rows from the transactions table where the amount is more than $1,000, and then use it as a condition for another query that will fetch rows from the users table based on results from the first query.

The query will look like this:

SELECT *
FROM users
WHERE id IN
    (SELECT user_id
     FROM transactions
     WHERE amount > 1000);

Advantages of Subqueries

  • Subqueries improve query readability as opposed to joins by structuring them into isolated parts.
  • It is easy to understand and maintain subqueries easily.
  • Subqueries can replace complex joins and unions.

Disadvantages of Subqueries

  • Subqueries cannot modify a table and select from the same table in the same SQL statement.
  • Subqueries are an expensive task, so it’s faster to use a join operation.

Running Exercises on Subqueries

We will explore a few examples using Arctype, where we’ll showcase subqueries and learn how they are constructed and used in applications.

We need to create a database we will work on within this tutorial. For the sake of convenience, I have created a GitHub Gist that contains SQL files to build our database schemas and insert dummy data into it. To use this, we need to do the following:

  • Connect to our database using Arctype
  • Navigate to the query tab in Arctype and create a new query
  • Paste and run the create.sql file from the Gist to create the schemas
  • Navigate to the query tab once again to create a new query
  • Paste and run the insert.sql file from the Gist to fill the database with dummy data.
arctype table view
Your tables should look like this after running insert.sql

Selecting Data with Subqueries

Let’s write a query to select all rows from the BUYER table referenced in the SKU_DATA table. Create a new query in Arctype and execute the code below:

SELECT *
FROM BUYER
WHERE BuyerName IN
    (SELECT BUYER
     FROM SKU_DATA);
arctype select subquery results view
Querying for buyers also in SKU_DATA
The fast and easy-to-use SQL client for developers and teams

In the code above, we created an inner query that selects the BUYER column from the SKU_DATA table then uses it as a condition to select rows from the BUYER table that have the same BuyerName column values.

Updating Data with Subqueries

Let’s write a query that will increase the value of the Price column in the ORDER_ITEM table by 10% for all items sold in 2016. Create a new query in Arctype and execute the code below:

UPDATE ORDER_ITEM
SET Price=Price*1.1
WHERE SKU IN
    (SELECT SKU
     FROM CATALOG_SKU_2016);
arctype update subquery
Adjusting 2016 SKUs for inflation using a subquery

In the code above, we created an inner query that selects the SKU column from the CATALOG_SKU_2016 table to filter the rows that we should update in the ORDER_ITEM table.

Deleting Data with Subqueries

We’re going to write a subquery that will delete all records from the INVENTORY table stored in warehouses with less than 130,000 square feet. The query will look like this:

DELETE
FROM INVENTORY
WHERE WarehouseID IN
    (SELECT WarehouseID
     FROM WAREHOUSE
     WHERE SquareFeet < 130000);
arctype delete subquery view
Deleting all products in small warehouses

Using Nested Subqueries

It is also possible to have subqueries inside another subquery. Here’s an example:

SELECT *
FROM CATALOG_SKU_2017
WHERE SKU IN
	(
		SELECT SKU
		FROM INVENTORY
		WHERE WarehouseID IN
		(
			SELECT WarehouseID
			FROM WAREHOUSE
			WHERE SquareFeet > 130000
		)
	);
arctype nested subquery results view
Selecting only 2017 SKUs in large warehouses

In this example, we selected all the rows from the CATALOG_SKU_2017 table stored in warehouses with square feet greater than 130,000.

Conclusion

In this article, we learned about subqueries,  a way of running queries inside queries. We also learned how they work, their benefits and limitations, and ran examples on data using Arctype.

JOIN the Arctype Newsletter
Programming stories, tutorials, and database tips every 2 weeks

Spread the word

Keep reading