SQL > Advanced SQL > Subquery

A subquery is a SQL statement that has another SQL query embedded in the WHERE or the HAVING clause.

Syntax

The syntax for a subquery when the embedded SQL statement is part of the WHERE condition 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."

Examples

We use the following tables for our examples.

Table Store_Information

 Store_Name  Sales  Txn_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 

Example 1: Simple subquery

To use a subquery to find the sales of all stores in the West region, 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.

Notice that in this example, the inner query and the outer query are independent of each other. This type of subquery is called a simple subquery.

Example 2: Correlated 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);

Result:

SUM (Sales)
2750

Here, the inner query is used to make sure that SQL only sums up sales amount from stores that appear in both the Store_Information and the Geography tables.

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

Next: SQL WITH

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact