right - sql union join




如何在MySQL中完成一個完整的外部連接? (9)

MySql沒有FULL-OUTER-JOIN語法。 您必須按照以下方式同時執行LEFT JOIN和RIGHT JOIN,

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id  
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

但MySql也沒有RIGHT JOIN語法。 根據MySql的外部連接簡化 ,通過在查詢中的FROMON子句中切換t1和t2,將正確的連接轉換為等價的左連接。 因此,MySql查詢優化器將原始查詢翻譯為以下內容 -

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id  
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id

現在,按照原樣編寫原始查詢沒有任何壞處,但要說明是否有謂詞,例如WHERE子句(謂詞before-join謂詞)或ON謂詞(謂詞before-join謂詞)上的AND謂詞,然後你可能想看看魔鬼; 這是詳細的。

MySql查詢優化器會定期檢查謂詞是否為空拒絕 現在,如果你已經完成了RIGHT JOIN,但是在t1的列中使用了WHERE謂詞,那麼你可能會陷入拒絕null的場景。

例如,下面的查詢 - SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t1.col1 ='someValue'UNION SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id WHERE t1.col1 ='someValue'被查詢優化器轉換為以下內容SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t1.col1 ='someValue'UNION SELECT * FROM t2 LEFT JOIN t1 ON t2.id = t1.id WHERE t1.col1 ='someValue'所以表的順序已經改變,但謂詞仍然適用於t1,但t1現在在'ON'子句中。 如果t1.col1被定義為NOT NULL列,那麼這個查詢將被空拒絕

任何被null拒絕的外連接(左,右,完整)都被MySql轉換為內連接。

因此,您可能期望的結果可能與MySql返回的結果完全不同。 你可能會認為它與MySql的RIGHT JOIN有關,但那不對。 它只是MySql查詢優化器的工作原理。 因此,負責的開發人員在構建查詢時必須注意這些細微差別。

我想在MySQL中完成一個完全外連接。 這可能嗎? MySQL是否支持完全外部聯接?


Mysql不支持任何名為FULL OUTER JOIN的命令。 支持的三個連接是INNER JOIN,LEFT JOIN和RIGHT JOIN。

但是,您可以使用Command UNION作為實現完全外部聯接
(左連接查詢)UNION(右連接查詢)

例如,考慮下面的例子,我有兩個表格學生和標記。 要執行完整的外連接,我會執行以下代碼:

SELECT * FROM students  
LEFT JOIN marks   
ON students.id = marks.id  
UNION ALL  
SELECT * FROM students 
RIGHT JOIN marks  
ON students.id = marks.id;

上述答案都不是真正正確的,因為它們在重複值時不遵循語義。

對於(來自此duplicate )的查詢:

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.Name = t2.Name;

正確的等價物是:

SELECT t1.*, t2.*
FROM (SELECT name FROM t1 UNION  -- This is intentionally UNION to remove duplicates
      SELECT name FROM t2
     ) n LEFT JOIN
     t1
     ON t1.name = n.name LEFT JOIN
     t2
     ON t2.name = n.name;

如果您需要使用NULL值(可能也需要),則使用NULL -safe比較運算符<=>而不是=


你對這個解決方案有什麼看法?

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 
ON 1=1;

使用union查詢將刪除重複項,這與full outer join的行為不同,永遠不會刪除任何重複項:

[Table: t1]                            [Table: t2]
value                                  value
-------                                -------
1                                      1
2                                      2
4                                      2
4                                      5

這是full outer join的預期結果:

value | value
------+-------
1     | 1
2     | 2
2     | 2
Null  | 5
4     | Null
4     | Null

這是使用leftright Join union

value | value
------+-------
Null  | 5 
1     | 1
2     | 2
4     | Null

[SQL Fiddle]

我建議的查詢是:

select 
    t1.value, t2.value
from t1 
left outer join t2  
  on t1.value = t2.value
union all      -- Using `union all` instead of `union`
select 
    t1.value, t2.value
from t2 
left outer join t1 
  on t1.value = t2.value
where 
    t1.value IS NULL 

上述查詢結果與預期結果相同:

value | value
------+-------
1     | 1
2     | 2
2     | 2
4     | NULL
4     | NULL
NULL  | 5

[SQL Fiddle]

[來自評論,非常感謝!]
注意:這可能是最好的解決方案,既可以提高效率,又可以產生與FULL OUTER JOIN相同的結果。 這篇博客文章也很好地解釋了這一點 - 從方法2引用: “它正確處理重複的行,並且不包含任何它不應該的東西。有必要使用UNION ALL而不是普通的UNION ,這會消除我想要的副本保留。對於大型結果集,這可能會更有效,因為不需要分類和刪除重複項。“

我決定添加另一個來自full outer join可視化和數學的解決方案,它不是上面更好但更易讀:

完全外連接意味著(t1 ∪ t2) :全部在t1t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_onlyt1t2加上t1中所有不在t2且加上t2中不在t1

-- (t1 ∩ t2): all in both t1 and t2
select t1.value, t2.value
from t1 join t2 on t1.value = t2.value    
union all  -- And plus 
-- all in t1 that not exists in t2
select t1.value, null
from t1
where not exists( select 1 from t2 where t2.value = t1.value)    
union all  -- and plus
-- all in t2 that not exists in t1
select null, t2.value
from t2
where not exists( select 1 from t1 where t2.value = t1.value)

[SQL Fiddle]


修改shA.t的查詢更清晰:

-- t1 left join t2
SELECT t1.value, t2.value
FROM t1 LEFT JOIN t2 ON t1.value = t2.value   

    UNION ALL -- include duplicates

-- t1 right exclude join t2 (records found only in t2)
SELECT t1.value, t2.value
FROM t1 RIGHT JOIN t2 ON t1.value = t2.value
WHERE t2.value IS NULL 

帕布羅聖克魯斯給出的答案是正確的; 但是,如果有人偶然發現此頁面並希望得到更多解釋,請參閱詳細分類。

示例表

假設我們有以下表格:

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

內部聯接

內部連接如下所示:

SELECT *
FROM `t1`
INNER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

我們只能得到兩個表中出現的記錄,如下所示:

1 Tim  1 Tim

內部連接沒有方向(如左或右),因為它們明確地是雙向的 - 我們需要在兩側進行匹配。

外連接

另一方面,外連接用於查找其他表中可能不匹配的記錄。 因此,您必須指定允許連接的哪一方有缺失記錄。

LEFT JOINRIGHT JOINLEFT JOINRIGHT JOIN連接的縮寫; 我將在下面使用他們的全名來強化外連接與內連接的概念。

左外連接

左外連接,如下所示:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

...會從左表中獲取所有記錄,無論它們是否在右表中匹配,如下所示:

1 Tim   1    Tim
2 Marta NULL NULL

右外連接

右外連接,如下所示:

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

...會從右表中獲取所有記錄,而不管它們是否在左表中匹配,如下所示:

1    Tim   1  Tim
NULL NULL  3  Katarina

全外聯接

一個完整的外連接會給我們來自兩個表的所有記錄,不管它們是否在另一個表中有匹配,在兩邊都沒有匹配的地方有NULL。 結果如下所示:

1    Tim   1    Tim
2    Marta NULL NULL
NULL NULL  3    Katarina

但是,正如Pablo Santa Cruz指出的那樣,MySQL不支持這一點。 我們可以通過執行左連接和右連接的聯合來模擬它,如下所示:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

您可以將UNION視為“運行這兩個查詢,然後將結果堆疊在一起”。 一些行將來自第一個查詢,一些來自第二個查詢。

應該注意的是,MySQL中的UNION會消除重複的確切副本:Tim會出現在這裡的兩個查詢中,但是UNION的結果只會列出一次。 我的數據庫大師同事認為這種行為不應該依賴。 所以為了更加明確它,我們可以在第二個查詢中添加一個WHERE子句:

SELECT *
FROM `t1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`
WHERE `t1`.`id` IS NULL;

另一方面,如果你看到重複出於某種原因,你可以使用UNION ALL


我修復了這個問題,作品包括所有行(基於Pavle Lekic的回复)

    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    WHERE b.`key` is null
    )
    UNION ALL
    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    where  a.`key` = b.`key`
    )
    UNION ALL
    (
    SELECT b.* FROM tablea a
    right JOIN tableb b ON b.`key` = a.key
    WHERE a.`key` is null
    );

SELECT
    a.name,
    b.title
FROM
    author AS a
LEFT JOIN
    book AS b
    ON a.id = b.author_id
UNION
SELECT
    a.name,
    b.title
FROM
    author AS a
RIGHT JOIN
    book AS b
    ON a.id = b.author_id




full-outer-join