Query Performance with and without join

Question

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)

and

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?

score 2 · Accepted Answer · edited May 23 '17 at 12:22

2

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.

edited May 23 '17 at 12:22

Community

1
1

answered Aug 19 '16 at 10:47

TheGameiswar

27,855
8
56
94

`2.Remove where clause in second query` gives wrong results – Ruslan K. Aug 19 '16 at 11:00
1

they both give same results in OP variant. 2 rows. Without WHERE clause it give 832 row – Ruslan K. Aug 19 '16 at 11:08

Query Performance with and without join

1 Answers1