SQL Basics | DML: Data Manipulation Language | Subquery
You can put another SQL statement inside a single SQL statement. When another SQL statement is inserted in a WHERE clause or HAVING clause, you can use a subquery. Subqueries are first used to combine tables, and sometimes a subquery is the only way to join two tables.
Subquery
A subquery is written as follows.
SELECT "field1" FROM "table1"
WHERE "field2" [comparison_operator] (SELECT "field1" FROM "table2" WHERE "condition");
Comparison operators include =, >, <, >=, and <=, as well as string operators such as LIKE.
Subquery example
This is an example related to SQL joins.
store_information table
| store_name | sales | txn_date |
|---|---|---|
| Los Angeles | 1500 | Jan-05-2018 |
| San Diego | 250 | Jan-07-2018 |
| Los Angeles | 300 | Jan-08-2018 |
| Boston | 700 | Jan-08-2018 |
geography table
| region_name | store_name |
|---|---|
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | San Diego |
To use a subquery to calculate all sales for stores in the West region, use the following SQL.
SELECT SUM(sales) FROM store_information
WHERE store_name IN
(SELECT store_name FROM geography
WHERE region_name = 'West');
The result is as follows.
| SUM(sales) |
|---|
| 2050 |
In this example, the two tables are not joined directly, but the total sales for stores in the West region can still be calculated. First, it checks which stores are in the West region. Then it calculates and sums the sales for those stores.