ansaurus

Question

Find records that do not have related records in SQL

Answer 1

+3 A:

Select * From Orders Where OrderID not in (Select Distinct OrderID From OrderItems)

Mark Brittingham 2010-02-02 16:46:27

Answer 2

+1 A:

try with LEFT EXCEPTION JOIN

select *
from Orders
LEFT EXCEPTION JOIN OrderItems ON ...

Danny T. 2010-02-02 16:46:59

+1 - Cool - haven't seen that one before.

Mark Brittingham 2010-02-02 16:50:44

Answer 3

+6 A:

The following joins the tables, then selects those without any order items. The join is thought to be more efficient than using `IN'.

select *
from
    Orders O
    left outer join OrderItems I
    on I.OrderId = O.Id
where
    I.Id is null

Aaron 2010-02-02 16:47:45

That's what I ended up doing, thanks

Slee 2010-02-02 16:49:16

Aaron - I can imagine ways that a left outer join would be more efficient but do you have a specific reference for your assertion? I'd probably avoid using this construct because of the additional complexity but it might be appropriate (for me) for large datasets if there is a clear performance difference.

Mark Brittingham 2010-02-02 16:52:35

I do not have a specific reference; it is just an assertion I've heard from colleagues and one that I have often found to be true in my own experiences. This solution is not necessarily the best for every case, so it is best to try each and check out their respective execution plans and decide which is best from there.

Aaron 2010-02-02 17:34:41

After some searching, I found a lot of agreement with my thoughts on the subject. Here is a relevant link: http://sqlserverpedia.com/blog/sql-server-bloggers/using-joins-instead-of-sub-queries-a-case-study/

Aaron 2010-02-02 17:35:48

Thanks Aaron! Very useful reference - I appreciate the effort it took to look it up.

Mark Brittingham 2010-02-02 17:47:49

ansaurus

tags:

views:

answers:

Find records that do not have related records in SQL

related questions