SQL weirdness

[SQLDenis]

Just got my February SQL Magazine and on page 26 there is something like this, without running the code try to figure out what the results are for the 3 selects
I actually had this problem once in 2001 and it took me a while to debug it

create table #customers (custid char(1) not null)
insert into #customers
select 'a' union all
select 'b' union all
select 'c' 


create table #orders (ordercustid char(1) not null,id int not null)
insert into #orders
select 'a',1 union all
select 'b',1 union all
select 'a',2 union all
select 'b' ,2

select * from #customers where custid in(select custid from #orders) --How many rows?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

After looking at this for a little while, I think I finally get it. My guess was completely wrong, by the way.

I'm glad it wasn't me that had to find that bug.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

it's almost as bad as this

CREATE TABLE test (ID INT)
INSERT INTO test VALUES (1)
INSERT INTO test VALUES (2)
INSERT INTO test VALUES (null)

CREATE TABLE testjoin (ID INT)
INSERT INTO testjoin VALUES (1)
INSERT INTO testjoin VALUES (3)


SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM test)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[chrissie1]

the second one is worse then the first, I think. Another reason to avoid null, I guess.

Christiaan Baes
Belgium

"Time for a new sig." - Me

 

[pwilson]

Why does the second one not work.
Is it because the IN function goofs on the null or what?

 

[SQLDenis]

pwilson,

yes it goofs on NULL
3 ways to fix

CREATE TABLE test (ID INT)
INSERT INTO test VALUES (1)
INSERT INTO test VALUES (2)
INSERT INTO test VALUES (null)

CREATE TABLE testjoin (ID INT)
INSERT INTO testjoin VALUES (1)
INSERT INTO testjoin VALUES (3)

--incorrect
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM test )

--one way
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM test WHERE ID IS NOT NULL)

--another way
SELECT * FROM testjoin j
WHERE NOT EXISTS (SELECT n.ID
FROM test n
WHERE n.ID = j.ID)


--a third way
SELECT j.* FROM testjoin j
LEFT OUTER JOIN test n ON n.ID = j.ID
WHERE n.ID IS NULL

I did a post on my blog (

http://sqlservercode.blogspot.com/2006/01/null-trouble-in-sql-server-land.html)

about problems with NULLS the either day, check it out in your free time

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

First "feature" is annonying - you write query, SELECT wrong column in a subquery for <insert reason here> - and suddenly IN() doesn nothing.

If I had the power , that IN() query would produce runtime error.

And second one - yeah. General problem with NULL value is that it cannot fit into two-state Boolean algebra. For example:

select 
	case when 'blah' IN (NULL) then 'Yep' else 'Nope' end, 
	case when 'blah' NOT IN (NULL) then 'Yep' else 'Nope' end

I guess (ANSI?) potheads who made that thing have all their databases in sixth normal form with no NULLs in data, eventually only as a side-effect of outer joins. Here are some links for anyone interested:

http://www.firstsql.com/idefend2.htm

http://www.firstsql.com/idefend3.htm

http://www.firstsql.com/idefend4.htm

On the other hand... I still occasionally manage one "dBase on roids" proprietary DB (actually authors quickly ported Cobol app to Oracle7/Forms ) with no NULLs at all. And believe me, compared to that crock of $hite NULL problems look like Disneyland.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[chrissie1]

Watch it vongrunt you are in a public forum. ;-)

Christiaan Baes
Belgium

"Time for a new sig." - Me

 

[SQLDenis]

Yes let's all watch out....
Joe Celko is going to show up and say something like this and I quote from a reply in a newsgroup here, so it's not made up

"I believe we asked for DDL, not your code. What you posted looks like
"camelCase" names that violate ISO-11179; what the heck is a "dtbl_"
prefix???

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Your entire view of RDBMS is
wrong. You do not know that rows are not ANYTHING like a record, etc.

>> Here is my code: ..<<

You have just been told by one of the people who wrote this language
that you do not know what you are doing. Think about that. Then tell
your boss, or I will. You need more help than you can get in a
Newsgroup.


"





Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

OK. Grade F for politeness. Grade A- for telling the harsh truth. I guess everyone has some off days.

Btw. ya know what I'm telling to people who automatically create identity column on table without analyzing for natural keys?

"... and Joe Celko will be pi**ed."



------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)