Hi,
I have a select statement like this:
SELECT *
FROM
T1
INNER JOIN T2 ON T1.ID=T2.ID
LEFT OUTER JOIN T3 ON T2.ID=T3.ID
The problem is I need the query processor LEFT JOIN T2 and T3, then, INNER
JOIN the result with T1.
I tried to use bracket like this:
SELECT *
FROM
T1
(
INNER JOIN T2 ON T1.ID=T2.ID
LEFT OUTER JOIN T3 ON T1.ID=T2.ID
)
But it gives me error. Is there any way that I enforce the sequence of the
join priority?
Any help would be appreciated,
AlanMove the ON belonging to the INNER JOIN to after the complete LEFT OUTER
JOIN:
SELECT *
FROM
T1
INNER JOIN T2
LEFT OUTER JOIN T3 ON T2.ID=T3.ID
ON T1.ID=T2.ID
Jacco Schalkwijk
SQL Server MVP
"A.M-SG" <alanalan@.newsgroup.nospam> wrote in message
news:unMnD0M0FHA.736@.tk2msftngp13.phx.gbl...
> Hi,
>
> I have a select statement like this:
>
> SELECT *
> FROM
> T1
> INNER JOIN T2 ON T1.ID=T2.ID
> LEFT OUTER JOIN T3 ON T2.ID=T3.ID
>
> The problem is I need the query processor LEFT JOIN T2 and T3, then, INNER
> JOIN the result with T1.
>
> I tried to use bracket like this:
>
> SELECT *
> FROM
> T1
> (
> INNER JOIN T2 ON T1.ID=T2.ID
> LEFT OUTER JOIN T3 ON T1.ID=T2.ID
> )
> But it gives me error. Is there any way that I enforce the sequence of the
> join priority?
>
> Any help would be appreciated,
> Alan
>|||You'll need to use a subquery to achieve the results you are looking
for. The statement below should get you on your way:
SELECT * FROM T1 INNER JOIN (
SELECT * FROM T2 LEFT OUTER JOIN T3 ON T1.ID=T2.ID) X
ON T1.ID = X.ID
HTH
Jason Strate|||This wont work if T2 and T3 have the same coloum names.
Use:-
SELECT *
FROM
T1
INNER JOIN T2
LEFT OUTER JOIN T3 ON T2.ID = T3.ID
ON T1.ID = T2.ID
The t1->t2 ON clause is after the t2->t3 ON clause and so it is processed
after.
You dont need parenthesis, just move the ON clause.
"j strate" <jason.strate@.digineer.com> wrote in message
news:1129301301.659027.3430@.g43g2000cwa.googlegroups.com...
> You'll need to use a subquery to achieve the results you are looking
> for. The statement below should get you on your way:
> SELECT * FROM T1 INNER JOIN (
> SELECT * FROM T2 LEFT OUTER JOIN T3 ON T1.ID=T2.ID) X
> ON T1.ID = X.ID
> HTH
> Jason Strate
>|||Why?
Either order will give you same result, according to the query posted.
A.M-SG wrote:
>Hi,
>
>I have a select statement like this:
>
>SELECT *
>FROM
>T1
>INNER JOIN T2 ON T1.ID=T2.ID
>LEFT OUTER JOIN T3 ON T2.ID=T3.ID
>
>The problem is I need the query processor LEFT JOIN T2 and T3, then, INNER
>JOIN the result with T1.
>
>I tried to use bracket like this:
>
>SELECT *
>FROM
>T1
>(
>INNER JOIN T2 ON T1.ID=T2.ID
>LEFT OUTER JOIN T3 ON T1.ID=T2.ID
> )
>But it gives me error. Is there any way that I enforce the sequence of the
>join priority?
>
>Any help would be appreciated,
>Alan
>
>|||I don't know what the other posters are thinking, because unless you force
the order using hints or FORCEPLAN, the query optimizer ignores the order of
the joins and chooses the best plan based on available indexes, statistics,
etc. Look up SET FORCEPLAN, and OPTION(FORCE ORDER) in Books Online if you
want to coerce the optimizer to use a specific order. In that case, the
suggestions by the other posters may have merit.
"A.M-SG" <alanalan@.newsgroup.nospam> wrote in message
news:unMnD0M0FHA.736@.tk2msftngp13.phx.gbl...
> Hi,
>
> I have a select statement like this:
>
> SELECT *
> FROM
> T1
> INNER JOIN T2 ON T1.ID=T2.ID
> LEFT OUTER JOIN T3 ON T2.ID=T3.ID
>
> The problem is I need the query processor LEFT JOIN T2 and T3, then, INNER
> JOIN the result with T1.
>
> I tried to use bracket like this:
>
> SELECT *
> FROM
> T1
> (
> INNER JOIN T2 ON T1.ID=T2.ID
> LEFT OUTER JOIN T3 ON T1.ID=T2.ID
> )
> But it gives me error. Is there any way that I enforce the sequence of the
> join priority?
>
> Any help would be appreciated,
> Alan
>|||On Fri, 14 Oct 2005 13:03:42 -0400, Brian Selzer wrote:
>I don't know what the other posters are thinking, because unless you force
>the order using hints or FORCEPLAN, the query optimizer ignores the order o
f
>the joins and chooses the best plan based on available indexes, statistics,
>etc.
Brian, Trey,
What the other posters were thinking, is that though the evaluation
order of INNER JOINs does not matter, this can change when OUTER JOINs
are involved.
In the give example, both orders of evaluation will produce the same
results. But the OP said "I have a select statement *like* this"
(emphasis is mine). His real statement is probably more complex, and
*will* probably return wrong results if the joins are performed in the
wrong order.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Fri, 14 Oct 2005 10:38:57 -0400, A.M-SG wrote:
>I tried to use bracket like this:
(snip)
>But it gives me error. Is there any way that I enforce the sequence of the
>join priority?
Hi Alan,
Jacco's answer is correct. But it might be easier to understand if you
return to this query in a year or so if you include some parentheses to
clarify (they are optional in this case):
SELECT *
FROM T1
INNER JOIN (T2 LEFT OUTER JOIN T3
ON T2.ID=T3.ID)
ON T1.ID=T2.ID
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I'm not sure that's right. The evaluation order is immaterial. Although
I'm not privy to the internals of the relational engine within SQL Server,
in a typical evaluator, an expression is compiled into an execution tree.
For any given node in the tree, the order in which each immediately
subordinate node is executed is immaterial, so long as all are executed
prior to the execution of the given node. The join expression can be
written in several different ways to produce an equivalent execution tree.
While it is true that changing the order of tables in the FROM clause may
require a left join to change to a right join, the expressions are
equivalent with respect to the number and type (inner or outer) of joins,
produce equivalent execution trees, and most important, produce the same
results.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vac0l19i21rv81n5itu6fk0b0brvuu4e9a@.
4ax.com...
> On Fri, 14 Oct 2005 13:03:42 -0400, Brian Selzer wrote:
>
> Brian, Trey,
> What the other posters were thinking, is that though the evaluation
> order of INNER JOINs does not matter, this can change when OUTER JOINs
> are involved.
> In the give example, both orders of evaluation will produce the same
> results. But the OP said "I have a select statement *like* this"
> (emphasis is mine). His real statement is probably more complex, and
> *will* probably return wrong results if the joins are performed in the
> wrong order.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||The rules in SQL-92 are that the JOINs are done in left-to-right order,
with the usual exceptions for parens. Your parens are wrong if you
wrote this:
Your parens are wrong if you wrote this:
SELECT *
FROM T1
INNER JOIN
T2
ON T1.id = T2.id
LEFT OUTER JOIN
T3
ON T1.id = T2.id;
It would done as:
SELECT *
FROM (T1
INNER JOIN
T2
ON T1.id = T2.id) -- makes sense
LEFT OUTER JOIN
T3
ON T1.id = T2.id; -- where is T3?
Hey! that is a serious CROSS JOIN problems
Try this:
SELECT *
FROM T1
INNER JOIN
(T2
LEFT OUTER JOIN
T3
ON T2.id = T3.id)
ON T1.id = T2.id);
You also need to research the scoping rules when you use a derived
table name. It is logical and follows the pattern you would expect
from block-structures languages. But you need to think about it.
