Sql joins

rajesh_css · February 10th, 2009, 11:41 PM

Hi,

is it possible to produce the same result using left outer join and right outer join.
if so can anyone explain me

Thanks in Advance
Raj

Jeff Mason · February 11th, 2009, 08:50 AM

It is possible if the two tables are in a one-to-one correspondence with respect to the JOIN condition.

Old Pedant · February 11th, 2009, 05:00 PM

Short answer: Yes.

Example:

Code:

SELECT C.catName, S.subcatName
FROM Categories AS C LEFT JOIN SubCategories AS S
ON C.id = S.id
ORDER BY C.catName, S.subcatName

is 100% equivalent to doing

Code:

SELECT C.catName, S.subcatName
FROM SubCategories AS S RIGHT JOIN Categories AS C 
ON C.id = S.id
ORDER BY C.catName, S.subcatName

I'm not sure if that's what you were asking or if Jeff's answer--which basically says that the two are the same when an INNER JOIN would work as well--was what you meant.

Jeff Mason · February 11th, 2009, 06:48 PM

Quote:

Originally Posted by Old Pedant

I'm not sure if that's what you were asking or if Jeff's answer--which basically says that the two are the same when an INNER JOIN would work as well--was what you meant.

Without JOIN conditions on your query, you are saying that constructing a cartesian product between two tables is commutative: A X B is the same as B X A.

Which of course it is.

Adding JOIN conditions to the cartesian product reduces it to an INNER JOIN.

So we're both right

Old Pedant · February 11th, 2009, 08:47 PM

Sorry. The omission of the conditions was just an oversight. I went back and edited my post to add them in.

And I'm not clear why you say that adding join conditions reduces an OUTER join to the equivalent of an INNER JOIN. Ehhh...you couldn't have meant that. I'm misreading you.

As for "both right": Hell, I don't know if either of us is right. I still don't know exactly what the question was supposed to be asking.

Jeff Mason · February 11th, 2009, 10:22 PM

Quote:

Originally Posted by Old Pedant

And I'm not clear why you say that adding join conditions reduces an OUTER join to the equivalent of an INNER JOIN. Ehhh...you couldn't have meant that. I'm misreading you..

Adding conditions to a cartesian product (not an OUTER JOIN) reduces it to an INNER JOIN. That is, in fact, how you 'do' an INNER JOIN: construct the cartesian product of the two tables, then select those rows where the JOIN condition is satisfied. I mean, that's how the math works; no query processor in it's right mind actually does it that way.

You are of course correct that A LEFT JOIN B ON ... is the equivalent of B RIGHT JOIN A ON...

Quote:

As for "both right": Hell, I don't know if either of us is right. I still don't know exactly what the question was supposed to be asking

I agree we really don't know what the OP was really getting at - we'll have to see if he shows back up to clarify...(or tells us which test question it was

)