Exists keyword in Sql Server

hi,

Exist is returns true if a subquery contains any rows.

for example:

SELECT a.Name, a.ID FROM Table1 AS a
WHERE EXISTS
(SELECT * FROM Employee AS b WHERE a.ID = b.ID);

This query return only those row which id present in employee table.

hope this will help u.

Hi Shubham,


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])

Example

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.

Please refer the below link

http://www.1keydata.com/sql/sql-exists.html


Thanks

lets say, i have two tables one is abc_test and another is abc.
abc_test contains 4 records and abc contains 6 records so with using EXISTS keywords it will gives me the 4 records which are exists in both table  because both table have 3 records common plus 1 record from abc_test which is not in the table abc which is same as the left outer join.

select distinct id from abc_test
where exists
(
  select distinct id from abc
)