MySQL单向比较两个没有主键的表

Question

我有两张 table 。它们没有主键或唯一列。它们的列数相同。

+---------------+  
|    table_1    |  
+---------------+  
| a | b | c | d |  
+---+---+---+---+  
| 1 | 1 | 1 | 1 |  
+---+---+---+---+  
| 1 | 3 | 1 | 1 |  
+---+---+---+---+ 

+---------------+
|    table_2    |
+---------------+
| a | b | c | d |
+---+---+---+---+
| 1 | 1 | 1 | 1 |
+---+---+---+---+
| 1 | 2 | 1 | 1 |
+---+---+---+---+

我想通过比较所有字段从 table_2 中获取 table_1 中不存在的数据。

我的查询如下所示，但它返回了两个表中不存在的所有记录。

SELECT a, b, c, d
FROM
(
    SELECT t1.a, t1.b, t1.c, t1.d
    FROM table_1 as t1
    UNION ALL
    SELECT t2.a, t2.b, t2.c, t2.d
    FROM table_2 as t2
)  t
GROUP BY a, b, c, d
HAVING COUNT(*) = 1
ORDER BY a

+----------------+
|        t       |
+----------------+
| id | a | b | c |
+----+---+---+---+
|  1 | 2 | 1 | 1 |
+----+---+---+---+
|  1 | 3 | 1 | 1 |
+----+---+---+---+

我想要的结果是这样的。

+----------------+
|        t       |
+----------------+
| id | a | b | c |
+----+---+---+---+
|  1 | 2 | 1 | 1 |
+----+---+---+---+

Answer 1

您正在寻找对两个表中的每一个都是唯一的记录集。实现此目的的一种方法是将两个 LEFT JOIN 查询UNION 放在一起，这些查询获取表之间不通用的记录。

SELECT t1.a, t1.b, t1.c, t1.d
FROM table_1 t1
LEFT JOIN table_2 t2
    ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c AND t1.d = t2.d
WHERE t2.a IS NULL
UNION ALL
SELECT t2.a, t2.b, t2.c, t2.d
FROM table_2 t2
LEFT JOIN table_1 t1
    ON t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c AND t1.d = t2.d
WHERE t1.a IS NULL

点击下面的链接获取正在运行的演示:

SQLFiddle