weird sql problem.

Question

weird sql problem.

mig28mx

Ten Centuries

Points: 1090
More actions
December 10, 2019 at 7:07 pm

#3706633
Hi all,
I´m not sure what I´m doing bad with this query:
Situation: I need to select all the purchase order lines(poitem_all) that not exists in the table of requisition lines (preqlines).
```
select po_num from poitem_all
```
results with all the purchase orders (po_num) for the table poitem_all. Ok. Then for select only that not exists in preqitem table:
```
select po_num from poitem_all
where poitem_all.ponum not in (select po_num from preqitem)
```
results with no records! This is weird due to, I have purchase orders that not exists in preqitem table.
But this query:
```
select po_num  from poitem_all
except
(select preqitem.po_num from preqitem)
```
results with the correct information.
Anyone knows what is the trick here?
Thank you in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 1

Is it because you are looking up on a different column: ponum instead of po_num

select po_num from poitem_all
where poitem_all.ponum not in (select po_num from preqitem)

ScottPletcher SSC Guru Points: 100942 More actions · Answer 2

You also might have a NULL value for po_num in preqitem. That would prevent the NOT IN from yielding any results, so add a NOT NULL check. Or, perhaps even better, use NOT EXISTS instead of NOT IN:

select po_num from dbo.poitem_all 
where poitem_all.po_num /*ponum?*/ not in (select po_num from dbo.preqitem where po_num is not null)

--or, more cleanly, write it like this:
select po_num from dbo.poitem_all po
where not exists(select 1 from dbo.preqitem pr where pr.po_num = po.po_num)

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 3

Also - be careful with referencing columns without using the table alias.

 Select po_num
   From poitem_all
  Where poitem_all.po_num Not In (Select po_num From preqitem)

In this query - let's assume the column name in preqitem is actually 'ponum' and not po_num. If that is the case - the query will execute with no errors but will give you results you are not expecting (actually - most or all rows will match).

 Select po_num
   From poitem_all
  Where poitem_all.po_num Not In (Select i.po_num From preqitem i)

This simple change - using the table alias i - would cause the query to fail if the table preqitem does not have a column named po_num.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

mig28mx Ten Centuries Points: 1090 More actions · Answer 4

Hi Jonathan,

Yes. My mistake. I wrote ponum instead of po_num.

But the problem persists.

Thank you.

mig28mx Ten Centuries Points: 1090 More actions · Answer 5

Hi Scott

Your solution worked like a charm!

Thank you.

mig28mx Ten Centuries Points: 1090 More actions · Answer 6

Hi Jeffrey,

It was my mistake. The column name is po_num and not ponum.

thank you.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 7

I think it's worth a second here to go through why a SQL NOT IN with a NULL value in it causes no values to match.

SQL Rule 1) A WHERE condition must be true to allow a row to be selected. Not just "not proven false", but absolutely proven true.

SQL Rule 2) A comparison against a NULL does not yield TRUE or FALSE, only NULL.

Ok, that said, run these statements:

SELECT value FROM ( VALUES(1),(2),(3) ) AS data(value) WHERE value NOT IN (5, 6, 7) --all rows

SELECT value FROM ( VALUES(1),(2),(3) ) AS data(value) WHERE value NOT IN (5, 6, 7, NULL) --no rows!

Probably easiest is to think of NULL as "unknown". When SQL hits the NULL in the value list, is 1 not = NULL? Since NULL is unknown, SQL can't know. An unknown value could be 1, after all. So SQL cannot absolutely say that the NOT IN is true. But if it's not known to be absolutely true, then the WHERE clause has failed, and the row can't be included in the result from the SELECT.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

mig28mx Ten Centuries Points: 1090 More actions · Answer 8

Hi Scott,

Thank for share this interesting rules. I was not knowing that!

I came from Oracle SQL, and these NULL things, works different.

Thank you again.