quote:

---

Originally posted by esthera

Shouldn't not in be the opposite as in

i'm doing in

select * from rep where d in(select distinct reg from dv)
this seems to work
but if i do
select * from rep where d not in(select distinct reg from dv)
-- it doesn't return the opposite of the first. (it's empty when really there are records that are not in the second table.

am i doing something wrong -- is there a better way to do this with a join to get all records from one table that are not in another table?

---

select 'ok'
where 3 not in (1,null,2)

---- 

(0 row(s) affected)

select rep.*
from rep
left outer join  
  (select distinct reg from dv) x
on rep.d = x.reg
where x.reg is null

quote:

---

I'm kinda assuming that EXISTS only has to find the first entry to know that it exists, whereas SELECT DISTINCT is probably going to find them all, sort them, de-dupe the list, and go from there.

---

quote:

---

Originally posted by mahesh_bote



who told u that, the result of NOT IN has to be opposite as IN? it depends on your data

Mahesh

---

use northwind
go

set statistics io on

select  * from customers
where customerid not in(select customerid from orders)

select  * from customers
where customerid not in(select distinct customerid from orders)

select  * from customers c
left join (select customerid from orders) o
on c.customerid = o.customerid
where o.customerid is null

select  * from customers c
left join (select distinct customerid from orders) o
on c.customerid = o.customerid
where o.customerid is null

select  * from customers c
where not exists(select * from orders o where c.customerid = o.customerid)

quote:

---

Originally posted by Jeff Moden

quote:

---

Originally posted by mahesh_bote



who told u that, the result of NOT IN has to be opposite as IN? it depends on your data

Mahesh

---

I should would like to see an example of a simple query where IN is not the opposite of NOT IN on a normalized table (no nulls). Unless that's the difference you were talking about...

--Jeff Moden

---

quote:

---

Originally posted by rockmoose
> I say -distinct- is an anti-pattern!
Many queries with distinct can be rewritten in a more performant way.
All too often is it thrown in due to lazyness or lack of proficiency in other techniques.
The essence is that results should be filtered as early as possible in a query, not at the end.

---

select * from (select 1) as a(a) where a in(select 2 union select null)
select * from (select 1) as a(a) where a not in(select 2 union select null)

a           
----------- 

(0 row(s) affected)

a           
----------- 

(0 row(s) affected)

quote:

---

Originally posted by rockmoose

"But tell me if ur table have NULLS, ur table in not normalized, does it possible to get exact opposite o/p in case of NOT IN v/s IN"

Yes, nulls and IN is very fishy...
Every time you use IN or NOT IN you have to know if NULL is involved...

select * from (select 1) as a(a) where a in(select 2 union select null)
select * from (select 1) as a(a) where a not in(select 2 union select null)

a           
----------- 

(0 row(s) affected)

a           
----------- 

(0 row(s) affected)

Or put another way, every time a column allows NULL, you have to be careful about how how you program, and cater for that special case...

rockmoose

---

quote:

---

Originally posted by jsmith8858

quote:

---

Originally posted by rockmoose
> I say -distinct- is an anti-pattern!
Many queries with distinct can be rewritten in a more performant way.
All too often is it thrown in due to lazyness or lack of proficiency in other techniques.
The essence is that results should be filtered as early as possible in a query, not at the end.

---

I could not agree more, it's a horrible pattern .... any time you see an even slightly complicated SELECT with a DISTINCT in it, a red flag is raised that says "this query is not written correctly."

---

quote:

---

Originally posted by harsh_athalye

EXISTS eliminates NULL problem but it requires correlated subquery.

---