Article Index
SQL Tutorial
Microsoft SQL Server
Stored In The Master Database?
Installing A Production Data Server
SQL Server B (Middle Of The Road)
SQL Commands
SQL BETWEEN
SQL Aggregate Functions
SQL ALIAS
SQL Subquery
SQL INTERSECT
Table Manipulation
SQL CREATE INDEX Statement
SQL PRIMARY KEY
SQL UPDATE Statement
Advanced SQL
SQL Running Totals

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."

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

Table Store_Information

Table Geography

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.

The syntax is as follows:
[SQL Statement 1]
UNION
[SQL Statement 2]

Say we have the following two tables,

Table Store_Information

Table Internet_Sales

and we want to find out all the dates where there is a sales transaction. To do so, we use the following SQL statement:

SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales

Result:

Please note that if we type "SELECT DISTINCT Date" for either or both of the SQL statement, we will get the same result set.


 SQL UNION ALL

The purpose of the SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values.

The syntax for UNION ALL is as follows:

[SQL Statement 1]
UNION ALL
[SQL Statement 2]

Let's use the same example as the previous section to illustrate the difference. Assume that we have the following two tables,

Table Store_Information

Table Internet_Sales

and we want to find out all the dates where there is a sales transaction at a store as well as all the dates where there is a sale over the internet. To do so, we use the following SQL statement:

SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales

Result: