How To Use SQL Subqueries

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, well 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 usersWHERE 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 its faster to use a join operation.

Running Exercises on Subqueries

We will explore a few examples using Arctype, where well 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

Lets 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 BUYERWHERE 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

Lets 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_ITEMSET Price=Price*1.1WHERE 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

Were 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:

DELETEFROM INVENTORYWHERE WarehouseID IN    (SELECT WarehouseID     FROM WAREHOUSE     WHERE SquareFeet < 130000);

Using Nested Subqueries

It is also possible to have subqueries inside another subquery. Heres an example:

SELECT *FROM CATALOG_SKU_2017WHERE 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.