CODEWITHSUNDEEP
sql-server
shubhDcoder
shubhDcoder

Reputation: 11

EXISTS operator in SQL SERVER

actually i am having doubt regarding exists operator

there are two tables.

CREATE TABLE EMPDETAIL(
EID NUMERIC(8),
NAME VARCHAR(20),
DOB DATE,
ADDRESS VARCHAR(50),
CONTACTNO NUMERIC(13)
)

CREATE TABLE EMPWEBDETAIL(
EID NUMERIC(8),
EMAIL VARCHAR(30),
USERID VARCHAR(10),
PSWORD VARCHAR(10)
)

now i want to obtain the employee who have an entry in empdetail table but not in empwebdetail,,

i can do it with joins, subquery, correlated subquery

but i want to do it with exists operator.. the solution is 

SELECT A.EID, A.NAME FROM EMPDETAIL AS A WHERE EXISTS( SELECT B.EID FROM EMPWEBDETAIL AS B WHERE A.EID = B.EID)

THIS WILL GIVE ME THE DESIRED RESULT

MY QUERY IS EXISTS CHECKS ONLY THAT THE EXPRESSION IN PARENTHESES YIELDING SOME RESULTSET OR WE CAN SAY IF IT RETURNS TRUE OR FALSE, WHICH WORKED JUST FINE IN ABOVE CASE

BUT TAKE A LOOK IN SECOND QUERY BELOW..

SELECT A.EID, A.NAME FROM EMPDETAIL AS A WHERE EXISTS(SELECT B.EID FROM EMPDETAIL AS B WHERE B.EID IN (111,112,113))

ACCORDING TO THE PREVIOUS RESULT IT SHOULD HAVE GIVEN ME THE RECORDS FOR ONLY THESE THREE EMPLOYEES(111,112,113) BUT RATHER IT IS GIVING THE COMPLETE ROWS OF EMPDETAIL TABLE...

Upvotes: 0

Views: 337

Answers (3)

BBlake
BBlake

Reputation: 2398

You still need to join your EXISTS table to the outer query table in order to limit your results. Your second query needs to be more along the lines of: 

SELECT A.EID, A.NAME 
FROM EMPDETAIL AS A 
WHERE EXISTS(SELECT B.EID FROM EMPDETAIL AS B 
             WHERE B.EID = A.EID AND B.EID IN (111,112,113))

or a better version would be:

SELECT A.EID, A.NAME 
FROM EMPDETAIL AS A 
WHERE EXISTS(SELECT B.EID FROM EMPDETAIL AS B WHERE B.EID = A.EID) 
   AND A.EID IN (111,112,113))

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166396

No, Exists will return all rows where the exists part for that row is true.

Thus even 

SELECT A.EID, A.NAME 
FROM EMPDETAIL AS A 
WHERE EXISTS(SELECT B.EID FROM EMPDETAIL AS B WHERE 1 = 1)

would return all rows from EMPDETAIL

Have a look at the below demo

SQL Fiddle DEMO

Upvotes: 0

tommy_o
tommy_o

Reputation: 3783

Is this what you want?

SELECT  A.EID, A.NAME 
FROM    EMPDETAIL AS A 
WHERE   EXISTS(SELECT B.EID FROM EMPWEBDETAIL AS B WHERE A.EID = B.EID) 
AND     A.EID IN (111,112,113)

The exists clause in the first example works because you're rrelating the two queries with A.EID = B.EID. Keep that and add an additional WHERE query filter for the IN.

Upvotes: 2

Related Questions