Wednesday, 14 March 2012

Exists in Sql Server


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"
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
Los Angeles$1500Jan-05-1999
San Diego$250Jan-07-1999
Los Angeles$300Jan-08-1999

Table Geography
EastNew York
WestLos Angeles
WestSan Diego

and we issue the following SQL query:
SELECT SUM(Sales) FROM Store_Information
(SELECT * FROM Geography
WHERE region_name = 'West')

We'll get the following result:


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.

No comments:

Post a Comment