SQL > Advanced SQL > Exists

EXISTS is a Boolean operator used in a subquery to test 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.

Syntax

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.

Example

We use the following tables for our example.

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

The following SQL query,

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

produces the result below:

SUM(Sales)
2750

At first, this may appear confusing, because the subquery includes the [region_name = 'West'] condition, yet the query summed up sales for stores in all regions. Upon closer inspection, we find that since the subquery returns more than zero row, the EXISTS condition is true, and the rows returned from the query "SELECT SUM(Sales) FROM Store_Information" become the final result.

Next: SQL CASE




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