Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2005
    Posts
    2

    Full Outer join vs Cross join

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  3. #3
    Join Date
    Sep 2005
    Posts
    2
    Thanks Pat for the clarification. I got it now.

  4. #4
    Join Date
    Mar 2009
    Posts
    3
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can remember the background of posts from 3.5 years ago? I can barely remember posts from 3.5 hours ago
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I'm weird.

    -PatP

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    phew. That was digging up threads from the dead...
    Lou
    使大吃一惊
    "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


  9. #9
    Join Date
    Mar 2009
    Posts
    3

    Wow

    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 .

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  11. #11
    Join Date
    Mar 2009
    Posts
    3
    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.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by brandonvmoore
    it occurred to me that it 'was' the first result Google returned to me so I figured others would probably see it too .
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •