开发者

nested exists clauses vs adjacent exists clauses

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 exi开发者_StackOverflow中文版sts(
    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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜