Union All

Breako · #1 (**permalink**) 08-17-07, 12:28

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

gvee · #2 (**permalink**) 08-17-07, 12:36

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.

r937 · #3 (**permalink**) 08-17-07, 12:59

yeah, but george, the question was, could you do that with a JOIN

Breako · #4 (**permalink**) 08-17-07, 13:01

Quote:

Originally Posted by r937

yeah, but george, the question was, could you do that with a JOIN

Yes that was the quesion. thanks

stolze · #5 (**permalink**) 08-17-07, 13:30

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.

r937 · #6 (**permalink**) 08-17-07, 13:48

breako, by any chance are you using mysql version 3.23?

gvee · #7 (**permalink**) 08-17-07, 15:02

I posted an example of one which is faaaar easier using a union.
Proof by exception

r937 · #8 (**permalink**) 08-17-07, 15:04

Quote:

Originally Posted by georgev

Proof by exception

nice try, but quite a clever backpedal

Pat Phelan · #9 (**permalink**) 08-18-07, 01:00

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

gvee · #10 (**permalink**) 08-19-07, 17:41

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

stolze · #11 (**permalink**) 08-20-07, 08:33

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

Peter.Vanroose · #12 (**permalink**) 08-25-07, 18:34

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.

Pat Phelan · #13 (**permalink**) 08-25-07, 20:35

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

Peter.Vanroose · #14 (**permalink**) 08-25-07, 21:31

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