How To Use SQL Subqueries
Tags: guide, beginners, sql
Published: 2021-08-17 22:18:47

## Prerequisites

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

- [Arctype](https://arctype.com)
- 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.

![](https://lh3.googleusercontent.com/1d9wdXxtcFi4xFdprEZ7HwX1OKofwNZZaBpCKKa9MO-eblX264nzJFT-WoHzGhM8UpwKSAKpwH_ar7Got4dThHRETRsiP99unGzkVvIDU_POsQofmvIcfcn2gU5_431vDN5sh_1E)
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](https://arctype.com), 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](https://gist.github.com/LordGhostX/13792039bd92d2120084100a3ab13b4b) 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](https://gist.github.com/LordGhostX/13792039bd92d2120084100a3ab13b4b#file-create-sql) 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](https://gist.github.com/LordGhostX/13792039bd92d2120084100a3ab13b4b#file-insert-sql) from the Gist to fill the database with dummy data.

![arctype table view](https://lh4.googleusercontent.com/0zOVjos1gA7BW03O45-40qirgfJwcuWcW68Fq3KxMNQB-ErC-tHbBpVvf9krTIDQBZ-ldAs1x8gFTDMU3JhpSqgbJWaOY7VALOgyLjq5ksH1PlqML0FSOo6ESNFitC54OO_nfMwT)

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

![arctype select subquery results view](https://lh3.googleusercontent.com/_MJT6CNDwvHfrLIkViVF1cuvzwxwv5TIavPuaZG-eQtcttYGz8v4RBfhiAo2JYT2I1KGeWoWepB5pXD45YkiUKOWhe2fYFmK1IinpJP3vcGhey1poTD7wadPFacEGKCWw2rA3YwH)

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

```

![arctype update subquery](https://lh6.googleusercontent.com/F_ew6nfG0FuwbAowpZiT1PLnQQfAXN0wYyRpgAnE4RDbyEFnainuo0ERFTeCgy1NtylCejZ1Cr2YnMjIauFmor0dwA7db3PxPHNLFKUP7lraJoebOLyaDT14m5kHtgoCv8nZofbR)

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

![arctype delete subquery view](https://lh3.googleusercontent.com/nrobtOuHvHmVuPcz-d5UGcSA3qK4NUO1SdUUrJ2hcYIPVfWe7jlE5KWZFEQbDKZuNHL5rxj5XJ0w8LnLVjPaQuTXu4FB8GS7MKz1Ld4Co577dkUIvrHv34M6b_zS70-Vpt04NmWe)

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

![arctype nested subquery results view](https://lh4.googleusercontent.com/YX-5u9PVaAiGsZRL1I-WZdw1rbET8ZtRXXFTGLdESGGZ1TFf0JN_fR9KiPd_eN8PUlAGIt_pD_Q6WJrBtZV-Syk0O24QV9oQVbbdq4uS3ptwRUIyCgsv3CKd0d36tEtstgHFRV-m)

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](https://arctype.com).