MySQL比较两个表数据的差异

一、几种比较方式

1. 内连接（INNER JOIN）：连接两个表的相同记录，通过比较连接后的结果集，找出相同和不同的数据。
2. 外连接（LEFT JOIN或RIGHT JOIN）：连接两个表的所有记录，包括匹配和不匹配的记录，用于找出一个表有而另一个表没有的数据，或者两个表数据不匹配的情况。
3. 子查询：将一个表作为子查询，通过查询结果与另一个表进行比较，找出不同的数据。
4. EXISTS子查询：利用EXISTS关键字判断一个表中是否存在满足条件的记录，通过该方式找出两个表中不同的数据。
5. EXCEPT关键字（在MySQL中不原生支持）：返回在第一个表中存在但在第二个表中不存在的记录。

这些方式可以根据具体需求和数据结构选择合适的方法来比较两个表的数据差异。请注意，EXCEPT关键字在MySQL中不被原生支持，需要使用其他方式来模拟实现。

二、举例说明

当比较两个表的数据差异时，以下是一些示例SQL查询：

1. 内连接（INNER JOIN）：

SELECT t1.column1, t1.column2, t2.column1, t2.column2
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
WHERE t1.column1 <> t2.column1 OR t1.column2 <> t2.column2;
1
2
3
4

2. 外连接（LEFT JOIN）：

SELECT t1.column1, t1.column2, t2.column1, t2.column2
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.id IS NULL OR t1.column1 <> t2.column1 OR t1.column2 <> t2.column2;
1
2
3
4

3. 子查询：

SELECT column1, column2
FROM table1
WHERE (column1, column2) NOT IN (SELECT column1, column2 FROM table2);
1
2
3

4. EXISTS子查询：

SELECT column1, column2
FROM table1
WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table2.column1 = table1.column1 AND table2.column2 = table1.column2);
1
2
3

5. EXCEPT关键字：

-- 创建表table1和table2
CREATE TABLE table1 (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE table2 (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 向table1和table2插入数据
INSERT INTO table1 VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO table2 VALUES (1, 'Alice'), (3, 'Charlie'), (4, 'David');

-- 查询table1和table2的差集[注意如果是id一样，name不一样不会出现在结果中]
SELECT id FROM table1
EXCEPT
SELECT id FROM table2;

-- 查询table2和table1的差集[只要id和name中有一个不同则就会被认为数据不一致，出现在结果中]
SELECT id,name  FROM table2
EXCEPT
SELECT id,name  FROM table1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

三、使用场景

1. 内连接（INNER JOIN）：适用于需要比较两个表中相同记录的情况，可以找到两个表中相同数据和不同数据的交集。

2. 外连接（LEFT JOIN或RIGHT JOIN）：适用于找到一个表有而另一个表没有的数据，或者两个表数据不匹配的情况。LEFT JOIN适用于从左表（左侧表达式）中找出匹配和不匹配的数据，而RIGHT JOIN则相反。

3. 子查询：适用于将一个表作为子查询，通过查询结果与另一个表进行比较，找出不同的数据。

4. EXISTS子查询：适用于判断一个表中是否存在满足条件的记录，并找出两个表中不同的数据。

5. EXCEPT关键字（在MySQL中不原生支持）：适用于返回在第一个表中存在但在第二个表中不存在的记录。