如何在mysql中进行完整的外部连接？

chhqkbe1  于 2021-06-24  发布在  Mysql
关注(0)|答案(15)|浏览(407)

我想在mysql中做一个完整的外部连接。这可能吗？mysql支持完全外部连接吗？

sqlmysqlJoinouter-joinfull-outer-join

15条答案

按热度按时间
eni9jsuy

eni9jsuy1#

我修复了响应，工作包括所有行（基于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
    );
赞(0）分享  回复(0）举报  2021-06-24
sczxawaw

sczxawaw2#

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
赞(0）分享  回复(0）举报  2021-06-24
lnxxn5zx

lnxxn5zx3#

sql标准说 full join on 是 inner join on 排 union all 由空值扩展的不匹配左表行 union all 右表行扩展为空。ie公司 inner join on 排 union all 中的行 left join on 但不是
inner join on union all 中的行 right join on 但不是 inner join on .
ie公司 left join on 排
union all right join on 不在中的行 inner join on . 或者如果你知道你的 inner join on 结果在特定的右表列中不能为null，然后“ right join on 不在中的行 inner join on “行在吗？” right join on 与 on 条件扩展 and 那个专栏 is null .
ie相似
right join on union all 适当的 left join on 排。
“内部连接”和“外部连接”的区别是什么
（SQL标准2006 SQL/FAST 7.7语法规则1，一般规则1 B，3 C&D，5 B）

赞(0）分享  回复(0）举报  2021-06-24
k4aesqcs

k4aesqcs4#

使用 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

这是使用 left 以及 right 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) ：全部 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]`
赞(0）分享  回复(0）举报  2021-06-24
wlsrxk51

wlsrxk515#

上述答案实际上都不正确，因为当存在重复值时，它们不遵循语义。
对于以下查询（来自此副本）：

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

赞(0）分享  回复(0）举报  2021-06-24
cl25kdpy

cl25kdpy6#

你觉得交叉连接解决方案怎么样？

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 
ON 1=1;
赞(0）分享  回复(0）举报  2021-06-24
au9on6nz

au9on6nz7#

mysql上没有完整的连接，但您肯定可以模仿它们。
对于从这个so问题中转录的代码示例，您有：
有两个表t1、t2：

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

上面的查询适用于完全外部联接操作不会产生任何重复行的特殊情况。上面的查询取决于 UNION 设置运算符以删除查询模式引入的重复行。通过对第二个查询使用反连接模式，然后使用union all set运算符组合这两个集，可以避免引入重复行。在更一般的情况下，如果完全外部联接将返回重复的行，我们可以这样做：

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION ALL
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NULL
赞(0）分享  回复(0）举报  2021-06-24
t8e9dugd

t8e9dugd8#

为了更清楚，修改了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 t1.value IS NULL
赞(0）分享  回复(0）举报  2021-06-24
gfttwv5a

gfttwv5a9#

在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
赞(0）分享  回复(0）举报  2021-06-24
pbossiut

pbossiut10#

这也是可能的，但是您必须在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
赞(0）分享  回复(0）举报  2021-06-24
hfsqlsce

hfsqlsce11#

您可以执行以下操作：

(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);
赞(0）分享  回复(0）举报  2021-06-24
xt0899hw

xt0899hw12#

只需转换一个完整的外部连接即可。

SELECT fields
FROM firsttable
FULL OUTER JOIN secondtable ON joincondition

分为：

SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields (replacing any fields from firsttable with NULL)
FROM secondtable
WHERE NOT EXISTS (SELECT 1 FROM firsttable WHERE joincondition)

或者如果你至少有一列，比如 foo ，在 firsttable 如果不为空，可以执行以下操作：

SELECT fields
FROM firsttable
LEFT JOIN secondtable ON joincondition
UNION ALL
SELECT fields
FROM firsttable
RIGHT JOIN secondtable ON joincondition
WHERE firsttable.foo IS NULL
赞(0）分享  回复(0）举报  2021-06-24
3ks5zfa0

3ks5zfa013#

巴勃罗圣克鲁斯给出的答案是正确的；不过，如果有人无意中发现了这一页，并希望得到更多的澄清，这里是一个详细的细目。

示例表

假设我们有以下表格：

-- 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 JOIN 以及 RIGHT JOIN 是的缩写 LEFT OUTER JOIN 以及 RIGHT OUTER 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

完全外连接

一个完整的外部连接将为我们提供两个表中的所有记录，不管它们在另一个表中是否匹配，在不匹配的两侧都有空值。结果如下：

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

然而，正如pablosantacruz所指出的，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 意思是“运行这两个查询，然后将结果叠加在一起”；有些行来自第一个查询，有些来自第二个查询。
应该指出的是 UNION 在mysql中，将消除完全重复的内容：tim将出现在这里的两个查询中，但是 UNION 只列出他一次。我的数据库Maven同事认为不应该依赖这种行为。因此，为了更明确地说明这一点，我们可以添加一个 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 .

赞(0）分享  回复(0）举报  2021-06-24
iaqfqrcu

iaqfqrcu14#

回答：

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

可以按以下方式重新创建：

SELECT t1.*, t2.* 
 FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp
 LEFT JOIN t1 ON t1.id = tmp.id
 LEFT JOIN t2 ON t2.id = tmp.id;

使用union或union all答案不包括基表具有重复项的边缘情况。
说明：
有一个联合或联合都不能覆盖的边缘情况。我们无法在mysql上测试这一点，因为它不支持完全外部联接，但我们可以在支持它的数据库上说明这一点：

WITH cte_t1 AS
 (
       SELECT 1 AS id1
       UNION ALL SELECT 2
       UNION ALL SELECT 5
       UNION ALL SELECT 6
       UNION ALL SELECT 6
 ),
cte_t2 AS
(
      SELECT 3 AS id2
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
      UNION ALL SELECT 6
)
SELECT  *  FROM  cte_t1 t1 FULL OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2;

This gives us this answer:

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

工会解决方案：

SELECT  * FROM  cte_t1 t1 LEFT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
UNION    
SELECT  * FROM cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

给出错误答案：

id1  id2
NULL  3
NULL  4
1  NULL
2  NULL
5  5
6  6

联合所有解决方案：

SELECT  * FROM cte_t1 t1 LEFT OUTER join cte_t2 t2 ON t1.id1 = t2.id2
UNION ALL
SELECT  * FROM  cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

也不正确。

id1  id2
1  NULL
2  NULL
5  5
6  6
6  6
6  6
6  6
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

鉴于此查询：

SELECT t1.*, t2.*
FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp 
LEFT JOIN t1 ON t1.id = tmp.id 
LEFT JOIN t2 ON t2.id = tmp.id;

提供以下信息：

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

顺序不同，但与正确答案匹配。

赞(0）分享  回复(0）举报  2021-06-24
du7egjpx

du7egjpx15#

mysql没有完整的外部连接语法。您必须通过执行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也没有正确的连接语法。根据mysql对外部连接的简化，右连接通过在 FROM 以及 ON 查询中的子句。因此，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子句这样的 predicate ，where子句是一个before-join predicate 或 ON 子句，这是一个during连接 predicate ，那么您可能想看看魔鬼；这是细节。
mysql查询优化器定期检查 predicate 是否被null拒绝。

现在，如果您已经完成了正确的连接，但是使用t1中列上的where predicate ，那么您可能会遇到被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'

所以表的顺序已经改变，但是 predicate 仍然应用于t1，但是t1现在在'on'子句中。如果t1.col1定义为 NOT NULL 列，则此查询将被null拒绝。
任何被null拒绝的外部连接（left、right、full）都会被mysql转换为内部连接。
因此，您可能期望的结果可能与mysql返回的结果完全不同。你可能会认为mysql的正确连接是一个bug，但这是不对的。这就是mysql查询优化器的工作原理。因此，负责的开发人员在构造查询时必须注意这些细微差别。

赞(0）分享  回复(0）举报  2021-06-24
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com