How To Use SQL Subqueries

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.

Prerequisites

To completely understand and follow this lesson, you must have the following:

Arctype
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.