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