SQL EXISTS

In the previous section, we used IN to link the inner query and the outer query in a subquery statement. IN is not the only way to do so -- one can use many operators such as >, <, or =. EXISTS is a special operator that we will discuss in this section.

EXISTS simply tests whether the inner query returns any row. If it does, then the outer query proceeds. If not, the outer query does not execute, and the entire SQL statement returns nothing.

The syntax for EXISTS is:

SELECT "column_name1"
FROM "table_name1"
WHERE EXISTS
(SELECT *
FROM "table_name2"
WHERE [Condition])

Please note that instead of *, you can select one or more columns in the inner query. The effect will be identical.

Let's use the same example tables:

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 issue the following SQL query:

SELECT SUM(Sales) FROM Store_Information
WHERE EXISTS
(SELECT * FROM Geography
WHERE region_name = 'West')

We'll get the following result:

SUM(Sales)

2750

At first, this may appear confusing, because the subquery includes the [region_name = 'West'] condition, yet the query summed up stores for all regions. Upon closer inspection, we find that since the subquery returns more than 0 row, the EXISTS condition is true, and the condition placed inside the inner query does not influence how the outer query is run.