Hello,
I need to know whether MySQL supports 'FULL JOIN' or not. If so,
and if you also know which version, that would be a great help.

Thanks
Bhargav

nope

but you can get the same results by using LEFT OUTER UNION RIGHT OUTER

Won't using LEFT OUTER UNION RIGHT OUTER duplicate the rows that actually do join successfully? I think that you'd need to qualify one of them to get only the non-matches.

-PatP

pat, pat, pat

take a deep breath

UNION removes dupes

using UNION ALL and qualifying one of them may or may not result in better execution

SELECT ... FROM a LEFT OUTER JOIN b ON a.x = b.y
UNION ALL
SELECT ... FROM a RIGHT OUTER JOIN b ON a.x = b.y WHERE a.x IS NULL

it all depends on whether forcing the optimizer to examine the nullity of the join column is faster than letting it realize there may be dupes and dropping them during the merge

Yeah, but that is a bad thing if the underlying data is supposed to contain dupes, right?

-PatP

"supposed to contain dupes" ????

pat, allow me to introduce you to the concept of "if it ain't got a PK it ain't a table"

Yeah, but ain't that what MySQL is for??? Data what ain't really relational?

I agree with you wholeheartedly, that containing duplicates shouldn't be a concern, but when I'm doing "rescues" of MySQL databases they are frequently chock full of things that make relational purists like you and I shudder!

If Bhargav posted something about a PK that I missed, I'm sorry. Since I don't remember having seen anything about a PK, I didn't assume that one was there.

-PatP

i understand fully where you're coming from, as i came from there myself

however, things are a lot simpler when you explain things in accordance with relational theory first, and then handle the exceptions afterwards

(however, it is wise not to mention the relational theory in the answer, because people usually don't want to digest relational theory at the same time as they are solving an elementary problem)

for example, if the poster comes back with "aack! LEFT OUTER UNION RIGHT OUTER has dropped some important rows!" then i can address that problem, but not before

As long as that works for you, I'm good with it! I guess I just take a different philosophical approach, but as long as your way works for you and mine works for me, then we're both "good to go" on things!

-PatP