Prerequisites

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

• Arctype
• Basic knowledge of SQL

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.

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);

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);

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);

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
		)
	);

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.