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


当前回答

使用交叉联接解决方案:

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

其他回答

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的外部连接简化,通过切换查询中FROM和ON子句中的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子句之类的谓词,这是一个预连接谓词,或者on子句上的AND谓词,它是一个在连接期间的谓词,那么您可能需要看看魔鬼;这是详细的。

MySQL查询优化器定期检查谓词是否被null拒绝。

现在,如果您已经执行了RIGHT JOIN,但在t1开始的列上使用了WHERE谓词,那么您可能会遇到空拒绝的情况。

例如,查询

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拒绝。

任何被null拒绝的外部联接(左、右、全)都会被MySQL转换为内部联接。

因此,您可能期望的结果可能与MySQL返回的结果完全不同。您可能会认为MySQL的RIGHT JOIN存在缺陷,但这是不对的。这就是MySQL查询优化器的工作方式。因此,负责开发人员在构建查询时必须注意这些细微差别。

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

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

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

您在MySQL中没有完全联接,但您可以模拟它们。

对于从堆栈溢出问题中转录的代码示例,您有:

对于两个表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集合运算符组合这两个集合,可以避免引入重复的行。在更一般的情况下,如果完全外部联接将返回重复的行,我们可以这样做:

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