Subqueries are queries placed within an existing SQL statement. MySQL offers a very limited support for subqueries, however Oracle and DB2 fully support them. They may exist in any of the following types of SQL statements.
Syntax:
SELECT "column_name1"
FROM "table_name1"
WHERE "column_name2" [Comparison Operator]
(SELECT "column_name3"
FROM "table_name2"
WHERE [Condition])
Example:
Table: Store_Information
Store_name
Sales
Date
Los Angeles
$1500
Jan-05-2005
San Diego
$250
Jan-07-2005
Los Angeles
$300
Jan-08-2005
Boston
$700
Jan-08-2005
Table: Internet_Sales
Date
Sales
Jan-07-2005
$250
Jan-10-2005
$535
Jan-11-2005
$320
Jan-12-2005
$750
If we want to use a subquery to find the sales of all stores in the West region. Then 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')
Output:
SUM(Sales)
2050
Here instead of joining the two tables directly and then adding
up only the sales amount for stores in the West region, we use subquery to
find out which stores are in the West region and then add the
sales amount for these stores.
Share And Enjoy:These icons link to social bookmarking sites where readers can share and discover new web pages.
