Zahid Zahid - 2 months ago 4
SQL Question

Query Performance with and without join

Suppose We have two Tables

Customers and Orders

Customers Table:-

custid custname
------- ---------
1 aaa
2 bbb
3 ccc

Orders Table :-

orderid custid date
-------- ------ -----
101 1 2016-03-01
102 1 2016-03-03
103 2 2016-03-01

Now , we have show customers who have placed no orders

We can do it several ways:-

1.Without Join

Select custid
from Customers
where custid not in
(Select custid from Orders)


2.With Join

Select C.custid
from Customers C left join Orders O
on C.custid = O.custid
where O.orderid is null

I was asked will there be any performance difference ?if ,which will do better and why?


Both queries are different,so comparison is not valid ..for the queries to be same/to get same execution plan..

1.Replace Not IN in first query with Not Exists*

if you do this,you may get same plan for both queries and same treatment...

*why was Not IN asked to be removed,other than the reason it may give you weird results when nulls are involved,can be found from this answer Not IN vs Not Exists

Pasting Relevant terms from answer..

The execution plans may be the same at the moment but if either column is altered in the future to allow NULLs the NOT IN version will need to do more work (even if no NULLs are actually present in the data) and the semantics of NOT IN if NULLs are present are unlikely to be the ones you want anyway.