SQL Tutorial

It is possible to embed a SQL statement within another. When this is done on the WHERE or the HAVING statements, we have a subquery construct. What is subquery useful for? First, it can also be used to join tables. Also, there are cases where the only way to correlate two tables is through a subquery.

The syntax is as follows:

SELECT "column_name1"
FROM "table_name"
WHERE "column_name2" [Comparison Operator]
(SELECT "column_name1"
FROM "table_name"
WHERE [Condition])

[Comparison Operator] could be equality operators such as =, >, <, >=, <=. It can also be a text operator such as "LIKE."

Let's use the same example as we did to illustrate SQL joins:

Table Store_Information store_name Sales Date Los Angeles $1500 Jan-05-1999 San Diego $250 Jan-07-1999 Los Angeles $300 Jan-08-1999 Boston $700 Jan-08-1999

Table Geography region_name store_name East Boston East New York West Los Angeles West San Diego

and we want to use a subquery to find the sales of all stores in the West region. To do so, we use the following SQL statement:

SELECT SUM(Sales) FROM Store_Information
WHERE Store_name IN
(SELECT store_name FROM Geography
WHERE region_name = 'West')

Result:

SUM(Sales)

2050

In this example, instead of joining the two tables directly and then adding up only the sales amount for stores in the West region, we first use the subquery to find out which stores are in the West region, and then we sum up the sales amount for these stores.