Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Thread: Union All

  1. #1
    Join Date
    Jan 2006
    Posts
    119

    Unanswered: Union All

    Hi,
    I was just wondering is any query that uses UNION ALL possible to achieve just using a join? i.e. is UNION ALL just handy syntax?
    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT email, Firstname, Lastname
    FROM customers
    UNION ALL
    SELECT email, Firstname, Lastname
    FROM employees
    There you go - a list of all customers and employees details.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, but george, the question was, could you do that with a JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2006
    Posts
    119
    Quote Originally Posted by r937
    yeah, but george, the question was, could you do that with a JOIN
    Yes that was the quesion. thanks

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I think you can cover much - not sure about all.
    Code:
    SELECT ...
    FROM   employee FULL OUTER JOIN customer ON ( 1 = 2 )
    Then a few COALESCE function calls or CASE expressions in the SELECT-list to get the value from the correct table.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    breako, by any chance are you using mysql version 3.23?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I posted an example of one which is faaaar easier using a union.
    Proof by exception
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Proof by exception
    nice try, but quite a clever backpedal

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

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I can't think of any way to do this using just JOIN operations:
    Code:
    SELECT d0 + d1 + d2 + d3
       FROM       (SELECT 0 AS d0 UNION ALL SELECT  1 UNION ALL SELECT  2) AS z0
       CROSS JOIN (SELECT 0 AS d1 UNION ALL SELECT  3 UNION ALL SELECT  6) AS z1
       CROSS JOIN (SELECT 0 AS d2 UNION ALL SELECT  9 UNION ALL SELECT 18) AS z2
       CROSS JOIN (SELECT 0 AS d3 UNION ALL SELECT 27 UNION ALL SELECT 54) AS z3
       ORDER BY d0 + d1 + d2 + d3
    -PatP

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by r937
    nice try, but quite a clever backpedal

    No, seriously!
    I can't think of a better way of doing my example. I'm more than happy to be proved wrong though
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Pat Phelan
    I can't think of any way to do this using just JOIN operations:
    Code:
    SELECT d0 + d1 + d2 + d3
       FROM       (SELECT 0 AS d0 UNION ALL SELECT  1 UNION ALL SELECT  2) AS z0
       CROSS JOIN (SELECT 0 AS d1 UNION ALL SELECT  3 UNION ALL SELECT  6) AS z1
       CROSS JOIN (SELECT 0 AS d2 UNION ALL SELECT  9 UNION ALL SELECT 18) AS z2
       CROSS JOIN (SELECT 0 AS d3 UNION ALL SELECT 27 UNION ALL SELECT 54) AS z3
       ORDER BY d0 + d1 + d2 + d3
    -PatP
    Aside from the point that this is no SQL (no FROM-clauses in the subselects, each of those subselects can be replaced with:
    Code:
    SELECT COALESCE(t1.d1, t2.d1) AS d1
    FROM ( SELECT 0 AS d1 FROM sysibm.sysdummy1 ) AS t1
         FULL OUTER JOIN
         ( SELECT 1 AS d1 FROM sysibm.sysdummy1 ) AS t2 ON 1 = 2
    
    
    D1
    -----------
              1
              0
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by stolze
    SELECT COALESCE(t1.d1, t2.d1) AS d1
    FROM ( SELECT 0 AS d1 FROM sysibm.sysdummy1 ) AS t1
    FULL OUTER JOIN
    ( SELECT 1 AS d1 FROM sysibm.sysdummy1 ) AS t2 ON 1 = 2
    Just being pedantic now (I know), but this isn't std SQL neither since non-DB2 systems usually don't have a 1x1 table called sysibm.sysdummy1.

    The following is std SQL (I believe):
    Code:
    SELECT COALESCE(t1.d1, t2.d1) AS d1
    FROM ( VALUES (0) AS d1 ) AS t1
         FULL OUTER JOIN
         ( VALUES (1) AS d1 ) AS t2
         ON 1 = 2
    So, in summary, in answer to the original question, any UNION ALL could indeed be replaced by a (rather complex) FULL OUTER JOIN combined with COALESCEs, but I wouldn't call UNION ALL "just handy syntax": it's conceptually completely different from table joins.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by stolze
    Aside from the point that this is no SQL (no FROM-clauses in the subselects, each of those subselects can be replaced with:
    Code:
    SELECT COALESCE(t1.d1, t2.d1) AS d1
    FROM ( SELECT 0 AS d1 FROM sysibm.sysdummy1 ) AS t1
         FULL OUTER JOIN
         ( SELECT 1 AS d1 FROM sysibm.sysdummy1 ) AS t2 ON 1 = 2
    
    
    D1
    -----------
              1
              0
    Coach me a bit here, I'm really missing something. Your code does in fact produce the result set that you posted, but how is that related to the code that I posted? I believe that my code was perfectly valid SQL, but even if the FROM clause was required by a specific SQL interpreter it could be added without harm, and I still don't know how to produce the same kind of results without a UNION.

    -PatP

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Pat Phelan
    I still don't know how to produce the same kind of results without a UNION.
    Here it is:
    Code:
    SELECT d0 + d1 + d2 + d3
       FROM       (SELECT COALESCE(x1,x2,x3) AS d0
                    FROM (SELECT 0 AS x1) FULL OUTER JOIN
                         (SELECT 1 AS x2) ON 1 = 2 FULL OUTER JOIN
                         (SELECT 2 AS x3) ON 1 = 2 ) AS z0
       CROSS JOIN (SELECT COALESCE(x1,x2,x3) AS d1
                    FROM (SELECT 0 AS x1) FULL OUTER JOIN
                         (SELECT 3 AS x2) ON 1 = 2 FULL OUTER JOIN
                         (SELECT 6 AS x3) ON 1 = 2 ) AS z1
       CROSS JOIN (SELECT COALESCE(x1,x2,x3) AS d2
                    FROM (SELECT 0 AS x1) FULL OUTER JOIN
                         (SELECT 9 AS x2) ON 1 = 2 FULL OUTER JOIN
                         (SELECT 18 AS x3) ON 1 = 2 ) AS z2
       CROSS JOIN (SELECT COALESCE(x1,x2,x3) AS d3
                    FROM (SELECT 0 AS x1) FULL OUTER JOIN
                         (SELECT 27 AS x2) ON 1 = 2 FULL OUTER JOIN
                         (SELECT 54 AS x3) ON 1 = 2 ) AS z3
       ORDER BY d0 + d1 + d2 + d3
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Peter.Vanroose
    Just being pedantic now (I know), but this isn't std SQL neither since non-DB2 systems usually don't have a 1x1 table called sysibm.sysdummy1.
    No, you're not pedantic - just right.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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