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

asked Jan 11 '10 at 07:07 AM in Default

Nani
232 ● 7 ● 10 ● 12

(comments are locked)

1 answer:

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            
)

answered Jan 11 '10 at 09:44 AM