mysql - SQL查找多个缺失值

Question

就像图片中所示的这样，找出table2和table3相对于table1 的缺失值。
右下角的表格是想要查询出来的结果。
跪求各位大神帮忙！！！

Answer 1

sql做不了這個吧，你查詢的結果列中2-4，2-8這些值在資料庫中不存在，你如何去資料庫中查詢資料庫中不存在的值，所以我覺得你的表結構是不是要改一下，ID這一列就是一個id，不用加前綴。

sqlDROP TABLE IF EXISTS table1;
DROP TABLE IF EXISTS table2;
DROP TABLE IF EXISTS table3;

CREATE TABLE table1 (id INT NOT NULL PRIMARY KEY, name VARCHAR(20));
CREATE TABLE table2 (id INT NOT NULL PRIMARY KEY, name VARCHAR(20));
CREATE TABLE table3 (id INT NOT NULL PRIMARY KEY, name VARCHAR(20));

INSERT INTO table1 VALUES(1, 'ACF');
INSERT INTO table1 VALUES(2, 'DEF');
INSERT INTO table1 VALUES(3, 'QWE');
INSERT INTO table1 VALUES(4, 'RTY');
INSERT INTO table1 VALUES(5, 'UIO');
INSERT INTO table1 VALUES(6, 'OPL');
INSERT INTO table1 VALUES(7, 'ASD');
INSERT INTO table1 VALUES(8, 'FGH');
INSERT INTO table1 VALUES(9, 'ZXC');

INSERT INTO table2 VALUES(1, 'ACF');
INSERT INTO table2 VALUES(2, 'DEF');
INSERT INTO table2 VALUES(3, 'QWE');
INSERT INTO table2 VALUES(5, 'UIO');
INSERT INTO table2 VALUES(6, 'OPL');
INSERT INTO table2 VALUES(7, 'ASD');
INSERT INTO table2 VALUES(9, 'ZXC');

INSERT INTO table3 VALUES(1, 'ACF');
INSERT INTO table3 VALUES(3, 'QWE');
INSERT INTO table3 VALUES(4, 'RTY');
INSERT INTO table3 VALUES(7, 'ASD');
INSERT INTO table3 VALUES(8, 'FGH');
INSERT INTO table3 VALUES(9, 'ZXC');

-- 结果查询
SELECT id, name FROM table1 WHERE id NOT IN (SELECT id FROM table2)
UNION
SELECT id, name FROM table1 WHERE id NOT IN (SELECT id FROM table3);