Off topic, even for the off topic gang but...
Who wants to help me out with some deep, deep SQL magic?
(MrOli is ugly, but in the morning you will be sober, Thu 11 Dec 2008, 10:38, 9 replies, latest was 16 years ago)

Whats
the platform, what's the problem.
( Legless Mister Stabby - shanks for the memory, Thu 11 Dec 2008, 10:47, Reply)

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)

Witchery..
Sorry mate - above my pay grade and not my platform.

But good luck...

Cheers
( Legless Mister Stabby - shanks for the memory, Thu 11 Dec 2008, 11:17, Reply)

I thought I knew a bit about SQL
But this is way over my head...!
(Son of Sam-i-am We're all as mad as hatters here, Thu 11 Dec 2008, 11:20, Reply)

You forgot about
GRAVELFLANGE() and HYDROCORTISONE()
( Noeli overtheshoulderboulderholderthingstraplatchboobs, Thu 11 Dec 2008, 11:29, Reply)

Oh well
I'll play with it till it gets sore and let you know what I find out :-)

Unfortunately right now I've got far more basic problems, such as how to find out the address of whoever came up with the bastard schema I'm trying to use so I can garrote him with his own intestines...
(MrOli is ugly, but in the morning you will be sober, Thu 11 Dec 2008, 11:31, Reply)

Gravelflange?!
I've got some cream for that ;-)
(MrOli is ugly, but in the morning you will be sober, Thu 11 Dec 2008, 11:37, Reply)

Out of my skillset unfortunately
SELECT * FROM People WHERE People.AttractedTo = 'me' ORDER BY People.HooterDimensions DESC
(bill, Thu 11 Dec 2008, 18:39, Reply)

made you look
that's a bit of a cunt's trick doing that, adding a meaningless post

tbh, tbf, lolz
(sirolimus, Thu 11 Dec 2008, 18:44, Reply)