選擇不同的-> 加入 3 個表

嗨，我需要混合（不僅僅是加入）三個表，但每個表上可能存在或不存在 id（請參見下面的範例）。

我花了幾天時間嘗試加入和聯合，但一直缺少一些數據！！！有人可以給我一個新的想法嗎？

謝謝

如果 MySQL 已經實現FULL了連接，這將是相當簡單的：

select 
   id, 
   a.price as price_1701, 
   b.price as price_1702, 
   c.price as price_1703
from 
   t_1701 as a
   full join t_1702 as b using (id)
   full join t_1703 as c using (id)
order by
   id ;

不幸的是，它沒有（也沒有姐妹實現 MariaDB），所以你必須使用 aUNION和LEFT連接來恢復：

select 
   id, 
   a.price as price_1701, 
   b.price as price_1702, 
   c.price as price_1703
from 
   ( select id from t_1701
     union
     select id from t_1702
     union
     select id from t_1703
   ) as t
   left join t_1701 as a using (id)
   left join t_1702 as b using (id)
   left join t_1703 as c using (id)
order by
   id ;

您可以使用 union 來“混合”這些表，然後使用諸如 max 之類的聚合函式來過濾掉存在值的 null ：

select id, max(price_1701), max(price_1702), max(price_1703)
from (
   select id, price as price_1701, null as price_1702, null as price_1703
   from t_1701
   union all
   select id, null as price_1701, price as price_1702, null as price_1703
   from t_1702
   union all
   select id, null as price_1701, null as price_1702, price as price_1703
   from t_1703
) as t
group by id

引用自：https://dba.stackexchange.com/questions/198609