如何在 MySQL 中进行完全外部联接？

Question

提问by Spencer

I want to do a Full Outer Join in MySQL. Is this possible? Is a Full Outer Join supported by MySQL?

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

Answer 1

回答by Pablo Santa Cruz

You don't have FULL JOINS on MySQL, but you can sure emulate them.

你在 MySQL 上没有 FULL JOINS，但你肯定可以模仿它们。

For a code SAMPLE transcribed from this SO questionyou have:

对于从这个 SO 问题转录的代码 SAMPLE，您有：

with two tables t1, t2:

有两个表 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

The query above works for special cases where a FULL OUTER JOIN operation would not produce any duplicate rows. The query above depends on the UNIONset operator to remove duplicate rows introduced by the query pattern. We can avoid introducing duplicate rows by using an anti-joinpattern for the second query, and then use a UNION ALL set operator to combine the two sets. In the more general case, where a FULL OUTER JOIN would return duplicate rows, we can do this:

上面的查询适用于 FULL OUTER JOIN 操作不会产生任何重复行的特殊情况。上面的查询依赖于UNIONset 运算符来删除查询模式引入的重复行。我们可以通过对第二个查询使用反连接模式来避免引入重复行，然后使用 UNION ALL 集合运算符来组合两个集合。在更一般的情况下，FULL OUTER JOIN 会返回重复的行，我们可以这样做：

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

Answer 2

回答by Nathan Long

The answer that Pablo Santa Cruzgave is correct; however, in case anybody stumbled on this page and wants more clarification, here is a detailed breakdown.

Pablo Santa Cruz给出的答案是正确的；但是，如果有人偶然发现此页面并想要更多说明，这里有一个详细的分类。

Example Tables

示例表

Suppose we have the following tables:

假设我们有以下表格：

-- t1
id  name
1   Tim
2   Marta

-- t2
id  name
1   Tim
3   Katarina

Inner Joins

内部连接

An inner join, like this:

一个内部连接，像这样：

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

Would get us only records that appear in both tables, like this:

只会让我们看到出现在两个表中的记录，如下所示：

1 Tim  1 Tim

Inner joins don't have a direction (like left or right) because they are explicitly bidirectional - we require a match on both sides.

内连接没有方向（如左或右），因为它们是明确的双向的——我们需要两边都匹配。

Outer Joins

外连接

Outer joins, on the other hand, are for finding records that may not have a match in the other table. As such, you have to specify which sideof the join is allowed to have a missing record.

另一方面，外连接用于查找在另一个表中可能不匹配的记录。因此，您必须指定允许连接的哪一侧丢失记录。

LEFT JOINand RIGHT JOINare shorthand for LEFT OUTER JOINand RIGHT OUTER JOIN; I will use their full names below to reinforce the concept of outer joins vs inner joins.

LEFT JOIN和RIGHT JOIN被简写LEFT OUTER JOIN和RIGHT OUTER JOIN; 我将在下面使用它们的全名来强调外连接与内连接的概念。

Left Outer Join

左外连接

A left outer join, like this:

一个左外连接，像这样：

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

...would get us all the records from the left table regardless of whether or not they have a match in the right table, like this:

...将从左表中获取所有记录，无论它们是否在右表中匹配，如下所示：

1 Tim   1    Tim
2 Marta NULL NULL

Right Outer Join

右外连接

A right outer join, like this:

一个右外连接，像这样：

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

...would get us all the records from the right table regardless of whether or not they have a match in the left table, like this:

...将从右表中获取所有记录，无论它们在左表中是否匹配，如下所示：

1    Tim   1  Tim
NULL NULL  3  Katarina

Full Outer Join

全外连接

A full outer join would give us all records from both tables, whether or not they have a match in the other table, with NULLs on both sides where there is no match. The result would look like this:

完整的外连接将为我们提供来自两个表的所有记录，无论它们在另一个表中是否有匹配项，在没有匹配项的两侧都带有 NULL。结果如下所示：

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

However, as Pablo Santa Cruz pointed out, MySQL doesn't support this. We can emulate it by doing a UNION of a left join and a right join, like this:

然而，正如 Pablo Santa Cruz 指出的，MySQL 不支持这一点。我们可以通过对左连接和右连接进行 UNION 来模拟它，如下所示：

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`;

You can think of a UNIONas meaning "run both of these queries, then stack the results on top of each other"; some of the rows will come from the first query and some from the second.

您可以将 aUNION视为“运行这两个查询，然后将结果堆叠在一起”；一些行将来自第一个查询，一些来自第二个查询。

It should be noted that a UNIONin MySQL will eliminate exact duplicates: Tim would appear in both of the queries here, but the result of the UNIONonly lists him once. My database guru colleague feels that this behavior should not be relied upon. So to be more explicit about it, we could add a WHEREclause to the second query:

需要注意的是UNION，MySQL中的 a将消除完全重复：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;

On the other hand, if you wantedto see duplicates for some reason, you could use UNION ALL.

另一方面，如果您出于某种原因想查看重复项，则可以使用UNION ALL.

Answer 3

回答by shA.t

Using a unionquery will remove duplicates, and this is different than the behavior of full outer jointhat never removes any duplicate:

使用union查询将删除重复项，这与full outer join从不删除任何重复项的行为不同：

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

This is the expected result of full outer join:

这是预期的结果full outer join：

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

This is the result of using leftand right Joinwith union:

这是使用left和right Joinwith的结果union：

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

[SQL Fiddle]

My suggested query is:

我建议的查询是：

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

Result of above query that is as same as expected result:

上述查询的结果与预期结果相同：

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

[SQL Fiddle]

@Steve Chambers: ^{[From comments, with many thanks!]}
Note:This may be the best solution, both for efficiency and for generating the same results as a FULL OUTER JOIN. This blog postalso explains it well - to quote from Method 2: "This handles duplicate rows correctly and doesn't include anything it shouldn't. It's necessary to use UNION ALLinstead of plain UNION, which would eliminate the duplicates I want to keep. This may be significantly more efficient on large result sets, since there's no need to sort and remove duplicates."

@Steve Chambers：^{[来自评论，非常感谢！]}
注意：这可能是最好的解决方案，无论是为了效率还是为了产生与FULL OUTER JOIN. 这篇博客文章也很好地解释了它 - 引用方法 2：“这正确处理重复的行，并且不包含任何它不应该包含的内容。有必要使用UNION ALL而不是 plain UNION，这将消除我想要保留的重复项。这在大型结果集上可能会更高效，因为不需要排序和删除重复项。”

I decided to add another solution that comes from full outer joinvisualization and math, it is not better that above but more readable:

我决定添加另一个来自full outer join可视化和数学的解决方案，它并不比上面更好，但更具可读性：

Full outer join means (t1 ∪ t2): all in t1or in t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only: all in both t1and t2plus all in t1that aren't in t2and plus all in t2that aren't in t1:

全外连接的意思是(t1 ∪ t2)：all int1或 in t2
(t1 ∪ t2) = (t1 ∩ t2) + t1_only + t2_only：all in both t1and t2plus all in t1that are not in t2and plus all in t2that are not in 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]

Answer 4

回答by ARK

MySql does not have FULL-OUTER-JOIN syntax. You have to emulate by doing both LEFT JOIN and RIGHT JOIN as follows-

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

But MySql also does not have a RIGHT JOIN syntax. According to MySql's outer join simplification, the right join is converted to the equivalent left join by switching the t1 and t2 in the FROMand ONclause in the query. Thus, the MySql Query Optimizer translates the original query into the following -

但是 MySql 也没有 RIGHT JOIN 语法。根据MySql的外连接简化，通过在查询的FROMandON子句中切换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

Now, there is no harm in writing the original query as is, but say if you have predicates like the WHERE clause, which is a before-joinpredicate or an AND predicate on the ONclause, which is a during-joinpredicate, then you might want to take a look at the devil; which is in details.

现在，按原样编写原始查询没有坏处，但是如果您有像 WHERE 子句这样的谓词，它是连接前谓词或ON子句上的 AND 谓词，它是连接期间谓词，那么您可能想看看魔鬼；这是细节。

MySql query optimizer routinely checks the predicates if they are null-rejected. Now, if you have done the RIGHT JOIN, but with WHERE predicate on the column from t1, then you might be at a risk of running into a null-rejectedscenario.

MySql 查询优化器会例行检查谓词是否为空拒绝。现在，如果您已经完成了 RIGHT JOIN，但是在来自 t1 的列上使用 WHERE 谓词，那么您可能会面临遇到拒绝空值的情况的风险。

For example, THe following query -

例如，以下查询 -

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'

gets translated to the following by the Query Optimizer-

查询优化器将其转换为以下内容-

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'

So the order of tables has changed, but the predicate is still applied to t1, but t1 is now in the 'ON' clause. If t1.col1 is defined as NOT NULLcolumn, then this query will be null-rejected.

所以表的顺序已经改变，但谓词仍然适用于 t1，但 t1 现在在 'ON' 子句中。如果 t1.col1 被定义为NOT NULL列，那么这个查询将是null-rejected。

Any outer-join (left, right, full) that is null-rejectedis converted to an inner-join by MySql.

任何拒绝空值的外连接（左、右、全）都被 MySql 转换为内连接。

Thus the results you might be expecting might be completely different from what the MySql is returning. You might think its a bug with MySql's RIGHT JOIN, but thats not right. Its just how the MySql query-optimizer works. So the developer-in-charge has to pay attention to these nuances when he is constructing the query.

因此，您可能期望的结果可能与 MySql 返回的结果完全不同。您可能认为这是 MySql 的 RIGHT JOIN 的一个错误，但那是不对的。它就是 MySql 查询优化器的工作原理。因此，负责开发人员在构建查询时必须注意这些细微差别。

Answer 5

回答by Gordon Linoff

None of the above answers are actually correct, because they do not follow the semantics when there are duplicated values.

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

For a query such as (from this duplicate):

对于诸如（来自此副本）之类的查询：

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

The correct equivalent is:

正确的等价物是：

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;

If you need for this to work with NULLvalues (which may also be necessary), then use the NULL-safe comparison operator, <=>rather than =.

如果您需要使用它来处理NULL值（这也可能是必要的），请使用NULL-safe 比较运算符，<=>而不是=.

Answer 6

回答by Rami Jamleh

In SQLite you should do this:

在 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

Answer 7

回答by a20

Modified shA.t's query for more clarity:

修改 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

Answer 8

回答by lolo

You can do the following:

您可以执行以下操作：

(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);

Answer 9

回答by Super Mario

what'd you say about Cross joinsolution?

您对Cross join解决方案有何看法？

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

Answer 10

回答by Alex Pliutau

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

如何在 MySQL 中进行完全外部联接？

提问by Spencer

回答by Pablo Santa Cruz

回答by Nathan Long

Example Tables

示例表

Inner Joins

内部连接

Outer Joins

外连接

Left Outer Join

左外连接

Right Outer Join

右外连接

Full Outer Join

全外连接

回答by shA.t

回答by ARK

回答by Gordon Linoff

回答by Rami Jamleh

回答by a20

回答by lolo

回答by Super Mario

回答by Alex Pliutau

相关推荐

最近更新

标签

如何在 MySQL 中进行完全外部联接？

提问by Spencer

回答by Pablo Santa Cruz

回答by Nathan Long

Example Tables

示例表

Inner Joins

内部连接

Outer Joins

外连接

Left Outer Join

左外连接

Right Outer Join

右外连接

Full Outer Join

全外连接

回答by shA.t

回答by ARK

回答by Gordon Linoff

回答by Rami Jamleh

回答by a20

回答by lolo

回答by Super Mario

回答by Alex Pliutau

相关推荐

MySQL 按时间戳的月份部分分组的 SQL 查询

MySQL phpMyAdmin 报告“无权限”

如何从 MySQL Workbench 中的图表生成 SQL 脚本？

使用 REST 公开 MySQL 数据库/表

相关推荐

最近更新

标签