Hello

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.

thanks
Anand

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.

-PatP

Thanks Pat for the clarification. I got it now.

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.

-PatP

You can remember the background of posts from 3.5 years ago? I can barely remember posts from 3.5 hours ago

pootle flump ur codings are working excelent.

I'm weird.

-PatP

phew. That was digging up threads from the dead...

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.

-PatP

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.

That is a jolly good point, and puts a whole new spin on what sometimes looks like pointless "thread resurrection".

We may be at peace - this thread has, after 3.5 years, finally fulfilled its destiny.

pootle flump ur codings are working excelent.