question

Nani avatar image
Nani asked

Problem with EXISTS operator

For getting a list of managers I passed this query, which produced correct results.

select * from emp e1 where exists ( select 1 from emp e2 where e1.empno=e2.mgr);

But the following query is giving wrong results, plz explain in detail.

select * from emp e1 where exists ( select 1 from emp e2 where e1.mgr=e2.empno);
sql-server-2005exists
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Rob Farley avatar image
Rob Farley answered

You're asking two different questions with these queries.

select * from emp e1 where exists ( select 1 from emp e2 where e1.empno=e2.mgr);

...translates into "Find me employees for which there is another employee whose manager is the first employee" (ie, who manages someone)

select * from emp e1 where exists ( select 1 from emp e2 where e1.mgr=e2.empno);

...translates into "Find me employees for which there is another employee who manages the first employee" (ie, who reports to someone)

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.