Exists keyword in Sql Server

Question

Hi Friends, How can we use Exists keyword in Sql server with sub query ?

Jignesh Trivedi · Answer

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.

Satyapriya Nayak · Answer

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

Vikrant More · Answer

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 )