x

Functionality of 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%')                    

more ▼

asked Jan 11, 2010 at 07:07 AM in Default

avatar image

Nani
232 13 13 16

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

1. NULL is never included in either an EQUALS nor a NOT EQUALS test. You have to explicitly include or exclude it using IS NULL or IS NOT NULL

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

2. You are not checking the existence using a test which depends on the main table in your query.

where exists ( select cname from car where cname like 'honda%') is either True or False. It does not depend on the rest of the query. So you query is basically either: SELECT * from car where TRUE or SELECT * from car where FALSE

so you are getting all records.

You could alias the tables and then make the EXISTS dependent on the main table, something like:

SELECT * from car AS C1 where exists ( select cname from car AS C2 where C2.cname like 'honda%' AND C2.SomeColumn = C1.SomeColumn )

more ▼

answered Jan 11, 2010 at 09:44 AM

avatar image

Kristen ♦
2.2k 7 11 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017
x429
x38

asked: Jan 11, 2010 at 07:07 AM

Seen: 1047 times

Last Updated: Jan 25, 2010 at 11:06 AM

Copyright 2016 Redgate Software. Privacy Policy