select count(cars.carid)

from Cars left join RentalOrders

on cars.CarID=RentalOrders.CarRef

where carid not in(selectRentalOrders.CarRef from RentalOrders)

when I wrote this above-query for sofiacarrental_v2.2 it shows 30 in the result but when I changed it this query to that:

select count(cars.carid)

 from Cars left join RentalOrders

on cars.CarID=RentalOrders.CarRef

where not exists (select RentalOrders.CarRef from RentalOrders)

I replaced not in with not exists it showed 0 in the result.

there is any point in term of using them or I made a mistake in the second query? 

 

 

 

 

 

 

• Edited by Ali mn 10 hours 5 minutes ago

Hi Ali,

Exists returns true if there is any data, In returns true if a sub-item matches.

Question is answered here:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e52131e3-761e-4a11-b725-ae585ed1f115/difference-between-in-and-exists?forum=transactsql

Now to your where statement:

where not exists (select RentalOrders.CarRef from RentalOrders)

This statement means, if there are no records (NOT EXIST) in RentalOrders-Table. But I guess you have records in that table, and so as you say where NOT exists, the "NOT EXISTS" returns false, because records exist. So your where-condition is false, and so you exclude everything from your query => 0 records in the resultset

So this query should work:

select count(cars.carid)
from  Cars cars
where not exists (select * from RentalOrders ro where ro.CarRef = cars.CarID)

Hi Ali,

EXISTS looks for a boolean result whereas IN looks for individual values.

In most cases EXISTS and IN can be used interchangably however in most cases EXISTS performs better than IN as it expects a boolean value and doesnt look for individual values

http://sqlknowledgebank.blogspot.in/2012/11/in-exists-clause-and-their-performance.html

http://www.gregreda.com/2013/06/03/join-vs-exists-vs-in/