Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2004
    Posts
    15

    Unanswered: Does MySQL has 'FULL JOIN' feature?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nope

    but you can get the same results by using LEFT OUTER UNION RIGHT OUTER
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, pat, pat

    take a deep breath

    UNION removes dupes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    UNION removes dupes
    Yeah, but that is a bad thing if the underlying data is supposed to contain dupes, right?

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "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"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    "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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    Since I don't remember having seen anything about a PK, I didn't assume that one was there.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

Posting Permissions

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