In this beginner’s tutorial on SQL subqueries, you’ll learn how to filter query results using data from another table, execute a query within another query to combine results, and much more.
To completely understand and follow this lesson, you must have the following:
- Basic knowledge of SQL
What Are Subqueries?
A subquery is a SQL query nested within another SQL query. They help queries create criteria for a WHERE clause, which allows them to filter rows and conduct operations on them. With SELECT, INSERT, UPDATE, and DELETE commands, subqueries can be employed.
In a WHERE clause, an example of a subquery
We need to find people in a database who have transactions worth more than $1,000. For our example, we’ll use two tables: users and transactions, which will contain information about users and transactions.
We may build a query that pulls all rows from the transactions table with an amount more than $1,000, and then use it as a condition for a second query that fetches rows from the users table depending on the first query’s findings.
This is how the query will look:
SELECT * FROM users WHERE id IN (SELECT user_id FROM transactions WHERE amount > 1000);
Advantages of Subqueries
- Subqueries, as opposed to joins, increase query readability by breaking them down into smaller chunks.
- Subqueries are simple to comprehend and manage.
- Complex joins and unions can be replaced with subqueries.
Disadvantages of Subqueries
- In the same SQL query, subqueries cannot alter a table and select from the same table.
- Because subqueries are time-consuming, it’s better to utilize a join operation instead.
Selecting Data with Subqueries
Write a query to select all entries from the BUYER table, which is linked to the SKU DATA table. Create a new query in Arctype and run the following code:
SELECT * FROM BUYER WHERE BuyerName IN (SELECT BUYER FROM SKU_DATA);
We constructed an inner query in the code above that chooses the BUYER column from the SKU DATA table and uses it as a condition to select rows from the BUYER table with the same BuyerName column values.
Updating Data with Subqueries
Let’s build a query to increase the Price field in the ORDER ITEM database by 10% for all products sold in 2016. Create a new query in Arctype and run the following code:
UPDATE ORDER_ITEM SET Price=Price*1.1 WHERE SKU IN (SELECT SKU FROM CATALOG_SKU_2016);
We generated an inner query in the code above that picks the SKU column from the CATALOG SKU 2016 table to filter the entries in the ORDER ITEM table that need to be updated.
Deleting Data with Subqueries
We’ll create a subquery that deletes any records from the INVENTORY database that are held in warehouses that are fewer than 130,000 square feet. This is how the query will look:
DELETE FROM INVENTORY WHERE WarehouseID IN (SELECT WarehouseID FROM WAREHOUSE WHERE SquareFeet < 130000);
Using Nested Subqueries
It’s also feasible to nest subqueries within subqueries. Here’s an illustration:
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 picked all entries from the CATALOG SKU 2017 dataset that were held in warehouses with more than 130,000 square feet.