SQL > Advanced 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_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
Los Angeles300Jan-08-1999
Boston700Jan-08-1999

Table Geography

Region_NameStore_Name
EastBoston
EastNew York
WestLos Angeles
WestSan 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.

Next: SQL CASE




Copyright © 2014   1keydata.com   All Rights Reserved.     Privacy Policy