question

Nani avatar image
Nani asked

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-2005t-sqlnullexistsin
10 |1200

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

David Wimbush avatar image
David Wimbush answered

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 comments
10 |1200

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

David Wimbush avatar image David Wimbush commented ·
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 Like 1 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
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 Like 1 ·
Håkan Winther avatar image
Håkan Winther answered

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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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