mysql比较两个表的不同的数据

-- 使用LEFT JOIN
SELECT *
FROM table1
LEFT JOIN table2 ON table1.id = table2.id
WHERE table2.id IS NULL;

-- 使用NOT EXISTS
SELECT *
FROM table1
WHERE NOT EXISTS (
    SELECT 1
    FROM table2
    WHERE table1.id = table2.id
);

-- 使用FULL OUTER JOIN
SELECT table1.*, 'table1' AS source
FROM table1
FULL OUTER JOIN table2 ON table1.id = table2.id
WHERE table2.id IS NULL
UNION ALL
SELECT table2.*, 'table2' AS source
FROM table1
FULL OUTER JOIN table2 ON table1.id = table2.id
WHERE table1.id IS NULL;

-- 使用UNION和EXCEPT
SELECT *
FROM table1
EXCEPT
SELECT *
FROM table2
UNION ALL
SELECT *
FROM table2
EXCEPT
SELECT *
FROM table1;

SELECT table1.id, table1.column1, table2.column2
FROM table1
LEFT JOIN table2 ON table1.id = table2.id
WHERE table1.column1 <> table2.column2;