confusion between in and exists

Question

question

Nani asked Jan 11, '10

confusion between in and exists

create table CAR(cno int identity(1,1), cname varchar(20))

insert into CAR(cname) values('honda city')

insert into CAR(cname) values('accent')

insert into CAR(cname) values('ford fiesta')

insert into CAR(cname) values('honda civic')

insert into CAR(cname) values('Scorpio')

insert into CAR(cname) values(null)

in the above table 6th record has a null field

SELECT * from car where cname in ( select cname from car where cname not like 'honda%')

when i type this query, why im not getting null record

and

when i type following query why im getting all the records

SELECT * from car where exists ( select cname from car where cname like 'honda%')

sql-server-2005 t-sql null exists in

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-01-11T07:29:43Z

David Wimbush answered Jan 11, '10

You're not getting the null row in the first query because IN filters out nulls. In the second query, you're not making any connection between the rows in the outer query and the inner query. To get everything except Hondas, you need this:

SELECT * FROM car WHERE ISNULL(cname, '') NOT LIKE 'Honda%'

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush commented · Jan 11, 2010 at 09:46 AM

ISNULL: http://msdn.microsoft.com/en-us/library/ms184325.aspx. It's different from IS NULL, which is simply the correct way to test for NULLs (= NULL is never true).

1 ·

TimothyAWiseman commented · Jan 11, 2010 at 02:10 PM

David, remember that while = NUll is never true in standard ANSI SQL you can control that behaviour in SQL Server via the "Set Ansi Nulls" setting.

1 ·

Answer 2 · 2010-01-11T08:36:35Z

Håkan Winther answered Jan 11, '10

In the first query, you will not get any NULL records because NULL is "unknown". You may thinkt that NULL is <> from 'honda%' but since NULL is "unknown" SQL can't handle it as different from 'honda%' and can't handle NULL as equal to to 'Honda%'

You can use:

SELECT * from car 
where 
cname in ( select cname from car where cname not like 'honda%')
OR 
cname IS NULL

In the second query you get every record in the table because EXISTS evaluates to true as long as the subquery returns at least one record. To use the EXISTS keyword you have to use a "correlated" subquery, like this:

SELECT * from car AS c1 
where exists 
( 
select cname from car AS c2 
where cname like 'honda%' 
and c1.name=c2.name
)

To read more about the differences look at this page:

http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question