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_Name  Sales  Txn_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 

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

This page was last updated on June 19, 2023.




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