SQLite: full outer join with four tables with 30+

问题:

This question is an extension to this post
I want to join four different tables with SQLite which have just two columns in common. However, assume that there are 30+ columns, i.e more than just columns a - h. Please take a look at following example

Table1:

a   b   lon lat
---------------
22  33  11  22

Table2:

c   d   lon lat
---------------
1   2   44  45

Table3

e       f       lon lat
-----------------------
NULL    NULL    100 101

Table4

g       h       lon lat
-----------------------
NULL    NULL    200 201

The current solution is the following

SELECT a,b,NULL AS c, NULL AS d,NULL AS e, NULL AS f, NULL AS g, NULL AS h,
lon,lat
FROM table1
UNION ALL
SELECT NULL, NULL,c,d,NULL AS e, NULL AS f, NULL AS g, NULL AS h, lon,lat
FROM table2
UNION ALL
SELECT NULL, NULL,NULL,NULL,e,f, NULL AS g, NULL AS h, lon,lat
FROM table3
UNION ALL
SELECT NULL, NULL,NULL,NULL,NULL,NULL,g,h, lon,lat
from table4

Result:

+------+------+------+------+------+------+------+------+-----+-----+
|  a   |  b   |  c   |  d   |  e   |  f   |  g   |  h   | lon | lat |
+------+------+------+------+------+------+------+------+-----+-----+
| 22   | 33   | NULL | NULL | NULL | NULL | NULL | NULL |  11 |  22 |
| NULL | NULL | 1    | 2    | NULL | NULL | NULL | NULL |  44 |  45 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 100 | 101 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 200 | 201 |
+------+------+------+------+------+------+------+------+-----+-----+

DEMO

• Problem: What if I have not just the columns a until h, but a until z, i.e many many columns in table1, table2, table3, and table4 -> It would be very time-consuming and the structure wouldn't be clear if I had to write everywhere NULL as [letter] in my sql statement
• @zarruq was a huge help and he suggested that In that case I can just use `UNION ALL` and then `PIVOT` to convert columns to rows
• However, I am not sure how to do that. And, I do not know 100% what he means by that.
• EDIT: SQLite does not support pivot: Any other suggestions?

回答1:

I don't believe there's a particularly neat way of doing this when you have 30+ columns. The following is the best I could do, using nested CTEs to implement full outer joins, then using coalesce to choose the first non-null lat/lon.

It's still required to enumerate all 30+ fields in the top SELECT statement, but at least the massive lists of NULL AS ... aren't needed:

SELECT 
  a, b, c, d, e, f, g, h,
  coalesce(lat1, lat2, lat3, lat4) AS lat,
  coalesce(lon1, lon2, lon3, lon4) AS lon
  FROM (
    WITH t1_x_t2 AS (
        SELECT t1.*, t2.*, 
        t1.lat AS lat1, t2.lat AS lat2, t1.lon AS lon1, t2.lon AS lon2
        FROM table1 t1 LEFT OUTER JOIN table2 t2 ON 0
      UNION ALL
        SELECT t1.*, t2.*, 
        t1.lat AS lat1, t2.lat AS lat2, t1.lon AS lon1, t2.lon AS lon2
        FROM table2 t2 LEFT OUTER JOIN table1 t1 ON 0
    ), t3_x_t4 AS (
        SELECT t3.*, t4.*, 
        t3.lat AS lat3, t4.lat AS lat4, t3.lon AS lon3, t4.lon AS lon4
        FROM table3 t3 LEFT OUTER JOIN table4 t4 ON 0
      UNION ALL
        SELECT t3.*, t4.*, 
        t3.lat AS lat3, t4.lat AS lat4, t3.lon AS lon3, t4.lon AS lon4
        FROM table4 t4 LEFT OUTER JOIN table3 t3 ON 0
    )
    SELECT t1_x_t2.*, t3_x_t4.* FROM t1_x_t2 LEFT OUTER JOIN t3_x_t4 ON 0
    UNION ALL
    SELECT t1_x_t2.*, t3_x_t4.* FROM t3_x_t4 LEFT OUTER JOIN t1_x_t2 ON 0
)

回答2:

One way to derive the result using pivot()(if your dbms supports that) could be as below.

select a,b,c,d,e,f,g,h,lon,lat
from (
select 'a' as columnName1, a as val1,'b' as ColumnName2, b as val2, lon,lat
from table1
union all
select 'c',c,'d', d, lon,lat
from table2
union all
select 'e',e,'f', f, lon,lat
from table3
union all
select 'g',g,'h', h, lon,lat
from table4
) t1
PIVOT (MAX(val1)
      FOR columnName1 IN (a,c,e,g)) as Pivot1
PIVOT (MAX(val2)
      FOR columnName2 IN (b,d,f,h)) AS Pivot2;

Result:

+------+------+------+------+------+------+------+------+-----+-----+
|  a   |  b   |  c   |  d   |  e   |  f   |  g   |  h   | lon | lat |
+------+------+------+------+------+------+------+------+-----+-----+
| 22   | 33   | NULL | NULL | NULL | NULL | NULL | NULL |  11 |  22 |
| NULL | NULL | 1    | 2    | NULL | NULL | NULL | NULL |  44 |  45 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 100 | 101 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 200 | 201 |
+------+------+------+------+------+------+------+------+-----+-----+

DEMO

P.S. Kindly note that the above query is for sql-Server, so you probably need to tweek it for your dbms.