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.
Top comments (0)