CODEWITHSUNDEEP
sqlsql-server-2005
Cove
Cove

Reputation: 709

Not in and Exists not working as i expect

all. My question is easy, but I can't solve it by myselft. I have 2 tables: et_pics.ob_no where ob_no is user id and et_thanks_2014 where thnk.e_to is a link to et_pics.ob_no. I need to find out ob_no in et_pics who absent in e_to in et_thanks_2014. 

SELECT pics.ob_no, thnk.e_to FROM et_pics pics 
left join et_thanks_2014 thnk on thnk.e_to = pics.ob_no
WHERE e_to is null

This code works, but I thinks it's not the best way to solve my task. I've tried to solve it with IN predict:

SELECT pics.ob_no FROM et_pics pics 
WHERE pics.ob_no  in ((SELECT e_to FROM et_thanks_2014))

and not exists

SELECT ob_no from et_pics
WHERE not exists (SELECT DISTINCT (e_to) FROM et_thanks_2014 thnk)

but both returns nothing. Why?

Upvotes: 0

Views: 447

Answers (4)

Ravi Teja
Ravi Teja

Reputation: 11

Why not simply use "EXCEPT". The Query would be some thing like : SELECT ob_no FROM et_pics EXCEPT SELECT e_to FROM et_thanks_2014;

Upvotes: 1

Abaddon666
Abaddon666

Reputation: 1623

You have to add a fitting Where clause:

SELECT pics.ob_no FROM et_pics pics 
WHERE pics.ob_no  in ((SELECT e_to FROM et_thanks_2014 thnk WHERE thnk.e_to = pics.ob_no))

SELECT pics.ob_no from et_pics pics
WHERE not exists (SELECT DISTINCT (e_to) FROM et_thanks_2014 thnk WHERE thnk.e_to = pics.ob_no)

Upvotes: 1

Rahul
Rahul

Reputation: 77866

I believe you should be using NOT IN rather like below since you are trying to get the uncommon values

SELECT pics.ob_no FROM et_pics pics 
WHERE pics.ob_no  NOT IN (SELECT e_to FROM et_thanks_2014);

Moreover, I am not sure why you think that your LEFT JOIN solution is not the best solution.

SELECT pics.ob_no, 
thnk.e_to 
FROM et_pics pics 
left join et_thanks_2014 thnk on thnk.e_to = pics.ob_no
WHERE thnk.e_to is null;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You need correlation. For instance:

SELECT ob_no
from et_pics
WHERE not exists (SELECT 1 FROM et_thanks_2014 thnk WHERE thnk.e_to = pics.ob_no);

Without correlation, your subquery (presumably) always returns at least one row, assuming the table is not empty. Hence, not exists always returns false.

The equivalent not in is:

SELECT ob_no
FROM et_pics
WHERE pics.ob_no NOT IN (SELECT thnk.e_to FROM et_thanks_2014 thnk);

Is almost equivalent. However, if thnk.e_to is ever NULL, then it will never return true, so everything will be filtered. Hence, I tend to recommend NOT IN or LEFT JOIN for semantic reasons.

Also note that DISTINCT is unnecessary in subqueries using IN or EXISTS.

Upvotes: 4

Related Questions