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

Nani gravatar image

Nani
232 10 13 13

(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

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1950
x372
x31

asked: Jan 11, 2010 at 07:07 AM

Seen: 884 times

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