SQL Subquery

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.

The syntax is as follows:

SELECT "column_name1"
FROM "table_name1"
WHERE "column_name2" [Comparison Operator]
(SELECT "column_name3"
FROM "table_name2"
WHERE [Condition])

[Comparison Operator] could be equality operators such as =, >, <, >=, <=. It can also be a text operator such as "LIKE". The portion in red is considered as the "inner query", while the portion in green is considered as the "outer query".

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:

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.

In the above example, the inner query is first executed, and the result is then fed into the outer query. This type of subquery is called a simple subquery. If the inner query is dependent on the outer query, we will have a correlated subquery. An example of a correlated subquery is shown below:

SELECT SUM(a1.Sales) FROM Store_Information a1
WHERE a1.Store_name IN
(SELECT store_name FROM Geography a2
WHERE a2.store_name = a1.store_name)

Notice the WHERE clause in the inner query, where the condition involves a table from the outer query.

Link to this page: If you find this page useful, we encourage you to link to this page. Simply copy and paste the code below to your website, blog, or profile.

<a href="http://www.1keydata.com/sql/sql-subquery.html">SQL Subquery</a>