nested exists clauses vs adjacent exists clauses

Question

Can someone please tell me which of this 2 querys is better than the other and why? Should I use a join instead?

select * from grandChildTable gct
where exists(
    select * from childTable ct
    where some condition
    and gct.parentId = ct.Id
    and exists(
        select * from Table t
        where some other condition
        and ct.parentId = t.Id))

select * from grandChildTable gct
where exists(
    select * from childTable ct
    where some condition
    and gct.parentId = ct.Id)
and exists(
    select * from Table t
    where some other condition
    and gct.grandParentId = t.Id)

NOTES: The GrandChildTable has the IDs for both the ChildTable and Table because the IDs are compound.

The tables doesn't have references to any of the others.

The relationships between the tables are:

GrandChild to Child
n:1

Child to Table
n:1

You need to run it against your data as there is no absolute answer. Indexes also come into play - big time. I like joins as for me they are easier to read and most of the time have the best performance. — paparazzo, Sep 30 '11 at 01:31
BalamBalam: can you prove that JOINs are more efficient? I state EXISTS is more efficient: http://stackoverflow.com/q/2177346/27535 or http://stackoverflow.com/q/3305891/27535 or http://stackoverflow.com/q/6966023/27535 etc — gbn, Sep 30 '11 at 02:45
@GBN Why do you feel I need to prove JOINs are more efficient? I did not assert that JOINs are more efficient. Read the comment "there is no absolute answer". OK so your experience is that EXISTS is typically faster. I am not doubting your data nor am I asking you to prove EXISTS is faster. — paparazzo, Sep 30 '11 at 13:29

score 0 · Accepted Answer · answered Sep 30 '11 at 02:50

In this case (comparing to parent tables where you have less rows then the child) there may little difference using JOINs. However EXISTS is typically quicker and proven so.

I would also expect the optimiser to generate identical plans for the 2 EXISTS based ones: examine the query plans and see if there are any differences (also this Red Gate link). If not, go for readability.

nested exists clauses vs adjacent exists clauses

1 Answers1