Does the order matter

Hi,
I've been always wondering if the join order in select statement matter, for
example, I expected these two statements have different execution time but
they seem to have the same execution plan.
select ...
from ( SmallTableA A join SmallTableB B on A.id = b.id ) join BigTableX X on
x.id = a.id
comparing with
select ...
from ( BigTableX X join SmallTableA A on x.id = a.id ) join SmallTableB B
on A.id = b.id
I have the same question about the order of AND and OR operand.
select ...
from MyTable
where City = @.City AND StreetAddress = @.StreetAddress
comparing with
select ...
from MyTable
where StreetAddress = @.StreetAddress AND City = @.City
Thank you very muchOn Tue, 27 Sep 2005 15:54:16 -0700, Zeng wrote:

>I've been always wondering if the join order in select statement matter
(snip)
>I have the same question about the order of AND and OR operand.
(snip)
Hi Zeng,
The query optimizer is free to reorder all elements in your query, as
long as the results are unaffected. So you'll probably get the same
execution plan for your different queries.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Zeng,
I think the order does matter when performing OUTER JOINS and when combining
ANDs and ORs.
HTH
Jerry
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:ug8swc7wFHA.1168@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've been always wondering if the join order in select statement matter,
> for
> example, I expected these two statements have different execution time but
> they seem to have the same execution plan.
> select ...
> from ( SmallTableA A join SmallTableB B on A.id = b.id ) join BigTableX X
> on
> x.id = a.id
> comparing with
> select ...
> from ( BigTableX X join SmallTableA A on x.id = a.id ) join SmallTableB B
> on A.id = b.id
>
> I have the same question about the order of AND and OR operand.
> select ...
> from MyTable
> where City = @.City AND StreetAddress = @.StreetAddress
> comparing with
> select ...
> from MyTable
> where StreetAddress = @.StreetAddress AND City = @.City
>
> Thank you very much
>|||I'm not sure what you mean by combining ANDs and ORs, would you mind
explaining? thanks!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eBeo0k7wFHA.3152@.TK2MSFTNGP10.phx.gbl...
> Zeng,
> I think the order does matter when performing OUTER JOINS and when
combining
> ANDs and ORs.
> HTH
> Jerry
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:ug8swc7wFHA.1168@.TK2MSFTNGP15.phx.gbl...
but
X
B
>|||You missed the most basic idea of declarative languages. You tell the
compiler WHAT you want and the compiler figures out HOW to do it. Have
you had a course on progamming languages yet?
Join order does not matter. The optimizer can change it based on
current stats and indexing.|||Zeng,
R=1 AND S=1 AND T=2 OR U=3 AND V=4 etc... I use parens for something like
this.
HTH
Jerry
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:OBzlmC9wFHA.2792@.tk2msftngp13.phx.gbl...
> I'm not sure what you mean by combining ANDs and ORs, would you mind
> explaining? thanks!
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eBeo0k7wFHA.3152@.TK2MSFTNGP10.phx.gbl...
> combining
> but
> X
> B
>|||Joe,
So the order of the join clauses doesn't matter when you're using multiple
joins and join types in the same statment i.e., LEFT, RIGHT, INNER etc...?
But the order of the tables listed for a LEFT or RIGHT join does matter
right? You know T-SQL 100x better than me so...
Thanks
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127874943.221690.200070@.f14g2000cwb.googlegroups.com...
> You missed the most basic idea of declarative languages. You tell the
> compiler WHAT you want and the compiler figures out HOW to do it. Have
> you had a course on progamming languages yet?
> Join order does not matter. The optimizer can change it based on
> current stats and indexing.
>|||Order doesn't matter, unless you force it with optimizer hints.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:ug8swc7wFHA.1168@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've been always wondering if the join order in select statement matter,
> for
> example, I expected these two statements have different execution time but
> they seem to have the same execution plan.
> select ...
> from ( SmallTableA A join SmallTableB B on A.id = b.id ) join BigTableX X
> on
> x.id = a.id
> comparing with
> select ...
> from ( BigTableX X join SmallTableA A on x.id = a.id ) join SmallTableB B
> on A.id = b.id
>
> I have the same question about the order of AND and OR operand.
> select ...
> from MyTable
> where City = @.City AND StreetAddress = @.StreetAddress
> comparing with
> select ...
> from MyTable
> where StreetAddress = @.StreetAddress AND City = @.City
>
> Thank you very much
>|||As long as you join no more than 4 or 5 tables, in that case order
usually should not matter.
Only in a perfect world order does not matter at all.
In reality, if you join too many tables, it is impossible for the
optimizer to consider all the permutations, so the optimizer will
consider only some permutations, then quit.
Just think: even with just 5 tables there are 5*4*3*2=120 possible join
orders, and I wasn't considereing different indexes yet.|||You are correct--sort of, but there's no way to know which permutations will
be considered. There's no documentation that the optimizer applies any
deferrence to the order specified in the query. If order matters, then you
should specify optimizer hints like FORCE ORDER or use SET FORCEPLAN ON.
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1127923668.694625.281030@.f14g2000cwb.googlegroups.com...
> As long as you join no more than 4 or 5 tables, in that case order
> usually should not matter.
> Only in a perfect world order does not matter at all.
> In reality, if you join too many tables, it is impossible for the
> optimizer to consider all the permutations, so the optimizer will
> consider only some permutations, then quit.
> Just think: even with just 5 tables there are 5*4*3*2=120 possible join
> orders, and I wasn't considereing different indexes yet.
>