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.
精彩评论