MSSQL, but more of academic question
Doing quite a few joins to make a monster, but obviously want it as fast as possible.

Here's an example for the original:

SELECT ...
FROM A
INNER JOIN B ON (...)
INNER JOIN C ON (...)
LEFT JOIN D ON (C.x=D.x)
LEFT JOIN E ON (E.z=D.z)
WHERE
C.q=(scalar)

Now, it's fairly obvious that I can move the scalar into the inner join for C and end up with the same result. Inner joins are commutative and we're using a real scalar.

Now, the next refinement I want to make is in the second left join. The first left join is going to leave me with some NULLS. Where I have a NULL in the second left join, I don't want to do an index dive, and can skip it like this:

SELECT ...
FROM A
INNER JOIN B ON (...)
INNER JOIN C ON (... AND C.q=(scalar))
LEFT JOIN D ON (C.x=D.x)
LEFT JOIN E ON (D.z IS NOT NULL AND E.z=D.z)
WHERE
C.q=(scalar)

Doing this would mean all the NULL columns resulting from the first join would consume no further work.

So my question is: Are LEFT OUTER joins commutative? I can't quite picture it. I don't think this join is commutative because it depends on the previous, but I'm not sure. If they are, the IS NULL is irrelevant.

Or to go in to the black magic a little more, will the optimiser recognise that the order of evaluation specified is the best order?

Or (and this I think is where my thinking simply ran out of coffee and I confused myself) do the NULLS actually exist inside the table spec section of the SQL, or are they only available in the WHERE clause after the whole derived table specified is complete?

Bet you're sorry you asked now, Legless :-)
(MrOli is ugly, but in the morning you will be sober, Thu 11 Dec 2008, 11:03, Reply)