SELECT ...... WHERE .. NOT IN (SELECT...)

Question

Post reply

SELECT ...... WHERE .. NOT IN (SELECT...)

HCRUZ

SSC Rookie

Points: 45
More actions
December 1, 2010 at 5:16 am

#231762

Hi,
select GR.NumDoc As Query1 from GR Where GR.NumDoc Not In (Select FactL.NDoc from FactL)
select FactL.NDoc As Query2 from FactL Where FactL.NDoc = 'GRM20611675'
select GR.NumDoc As Query3 from GR Where GR.NumDoc = 'GRM20611675'
I expected:
Query1 = 'GRM20611675'
Query2 =
Query3 = 'GRM20611675'
I get:
Query1 =
Query2 =
Query3 = 'GRM20611675'
Anyone can explain.
Tkx

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323744 More actions · Answer 1

when you use NOT IN, you have to prevent NULLs in your dataset:

change query1 to this, and you'll get the expected resultsi think.

select GR.NumDoc As Query1 from GR Where GR.NumDoc Not In (Select FactL.NDoc from FactL WHERE FactL.NDoc IS NOT NULL)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

HCRUZ SSC Rookie Points: 45 More actions · Answer 2

Hi,

Figure it out.

select GR.NumDoc As Query1 from GR Where GR.NumDoc Not In (Select ISNULL(FactL.NDoc,'') from FactL)

Query1='GRM20611675'

As there as NULL values in FactL.NDoc it seems the NOT IN gets confused.

Why?

Tkx

HCRUZ SSC Rookie Points: 45 More actions · Answer 3

HCRUZ

SSC Rookie

Points: 45

December 1, 2010 at 5:35 am

#1257261

Tkx Lowell

Lowell SSC Guru Points: 323744 More actions · Answer 4

the why is easy; NOT IN requires it a comparison to each value in the sub-select.

really that is the same as saying :

WHERE GR.NumDoc <> 1

AND GR.NumDoc <> 2

AND GR.NumDoc <> NULL

...

since nulls appear int he dataset, and comparing a value to null is undefined, no results return.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!