Is there any difference between a full outer join and a cross join.
seems like irrespective of the join condition, a full outer join will fetch all rows (including corresponding nulls) thus being equivalent to a cross join.
Am I missing anything.
Yes, there is a definite difference between them. A cross join is a Cartesian join, for sets of A and B rows, you'll get a result of A * B rows. A full join will match all possible rows, meaning it will return AT MOST A + B rows. For large values of A and B, the difference can be huge.
Let me know what database engine you are using, and I'll cook up a short demo script if you'd like.
The above statement that an outer join will return A + B rows at max is making the assumption that there is a 1 to 1 relationship between table A and table B.
Any type of join will degrade to a cross join when the conditions are always true. Consider two tables with a column called "One" and every row of this column contains the number 1 for this field. You could do an inner join of these two tables on this field and the resulting number of rows would be A * B rather than A + B.
There was a bit of "out of band" communication missing from this discussion. At the time of the original exchange, I was privvy to the detail that the columns that were used to FULL JOIN the two tables each had PK (Primary Key) constraints on them, so no duplicate values were allowed.
Brandon is correct in that any join on a unconstrained columns can decay into a CROSS JOIN. In this case, if the join column has value X (which is not NULL) for every row in both tables, there will then be A * B rows in the result set.
phew. That was digging up threads from the dead...
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert
Wow Pat, I was about to comment on your ability to remember details of this thread from that long ago but it looks like someone else beat me to it. I almost didn't comment b/c I noticed the thread was so old, but then it occurred to me that it 'was' the first result Google returned to me so I figured others would probably see it too .
We (or at least I) very, VERY rarely get thanks or even feedback for much of anything. That makes threads like these unique, which tends to make things stick in my mind.
I often wonder what the heck keeps me doing this (administering the forum) for nothing, but every once in a while you get a chance to make a real positive difference, and at least for me that makes it all worth while.
Well let me add my thanks to you as well then. Years ago I took an Access class (which was actually quite usefull since it was more about db normalization). Virtually everything else I know about databases (or VB, C#, ASP, XML, etc.) has come from reading whatever I could find on the internet and I'm certain I would not have gotten to where I am w/o people like yourself.