我想在MySQL中进行完全的外部连接。这可能吗?MySQL是否支持完全外部联接?


当前回答

在SQLite中,您应该执行以下操作:

SELECT * 
FROM leftTable lt 
LEFT JOIN rightTable rt ON lt.id = rt.lrid 
UNION
SELECT lt.*, rl.*  -- To match column set
FROM rightTable rt 
LEFT JOIN  leftTable lt ON lt.id = rt.lrid

其他回答

前面的答案实际上都不正确,因为当存在重复值时,它们不遵循语义。

对于查询,例如(来自此副本):

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安全比较运算符<=>而不是=。

使用交叉联接解决方案:

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

您可以执行以下操作:

(SELECT 
    *
FROM
    table1 t1
        LEFT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t2.id IS NULL)
UNION ALL
 (SELECT 
    *
FROM
    table1 t1
        RIGHT JOIN
    table2 t2 ON t1.id = t2.id
WHERE
    t1.id IS NULL);

使用联合查询将删除重复项,这与从不删除任何重复项的完全外部联接的行为不同:

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

这是完全外部联接的预期结果:

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

这是使用左联合和右联合的结果:

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,这将消除我想要保留的重复项。这在大型结果集上可能会更有效,因为不需要排序和删除重复项。”


我决定添加另一个来自完全外部连接可视化和数学的解决方案。它并不比上述内容更好,但更具可读性:

完全外部连接意味着(t1ût2):都在t1或t2中(t1ût2)=(t1ğt2)+t1_only+t2_only:t1和t2中的所有值加上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

这也是可能的,但您必须在select中提到相同的字段名。

SELECT t1.name, t2.name FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT t1.name, t2.name FROM t2
LEFT JOIN t1 ON t1.id = t2.id