<p>考慮以下表：</p> <pre class="lang-sql prettyprint-override"><code>create table `t1` ( `date` date, `value` int ); create table `t2` ( `date` date, `value` int ); create table `t3` ( `date` date, `value` int ); insert into `t1` (`date`, `value`) values ("2022-01-01", 1), ("2022-03-01", 3), ("2022-04-01", 4); insert into `t2` (`date`, `value`) values ("2022-01-01", 1), ("2022-02-01", 2), ("2022-04-01", 4); insert into `t3` (`date`, `value`) values ("2022-01-01", 1), ("2022-02-01", 2), ("2022-03-01", 3); </code></pre> <p><code>t1</code>表缺少<code>2022-02-01</code>日期，<code>2022-02-01</code>日期，<code>t2</code>缺少<code>2022-code>t2</code>缺少<code; /code>，<code>t3</code>缺<code>2022-04-01</code>。我想要將這三個表連接起來，產生以下結果：</p> <pre class="brush:php;toolbar:false;">| t1.date | t1.value | t2.date | t2.value | t3.date | t3.value | | | | | | | | | 2022-01-01 | 1 | 2022-01-01 | 1 | 2022-01-01 | 1 | | null | null | 2022-02-01 | 2 | 2022-02-01 | 2 | | 2022-03-01 | 3 | null | null | 2022-03-01 | 3 | | 2022-04-01 | 4 | 2022-04-01 | 4 | null | null |</pre> <p>我知道如何在兩個表之間進行<code>full outer join</code>，但是在三個或更多表之間，情況會更複雜。我嘗試了像這樣的查詢，但沒有產生我想要的結果：</p> <pre class="lang-sql prettyprint-override"><code>select * from `t1` left join `t2` on `t2`.`date` = `t1`.`date` left join `t3` on `t3`.`date` = `t2`.`date` or `t3`.`date` = `t1`.`date` union select * from `t1` right join `t2` on `t2`.`date` = `t1`.`date` right join `t3` on `t3`.`date` = `t2`.`date` or `t3`.`date` = `t1`.`date`; </code></pre></p>

-- t1与t2进行全连接
select *
from `t1`
left join `t2` on `t2`.`date` = `t1`.`date` 
union 
select *
from `t1`
right join `t2` on `t2`.`date` = `t1`.`date` 

-- t1与t3进行全连接
union 
select *
from `t1`
left join `t3` on `t3`.`date` = `t1`.`date` 

union 
select *
from `t1`
right join `t3` on `t3`.`date` = `t1`.`date`