mysql - SQL查找多个缺失值

Question

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

Answer 1

SQL can’t do this. The values ​​2-4 and 2-8 in the result columns of your query do not exist in the database. How do you query the database for values ​​that do not exist in the database? So I think your table structure Do you need to change it? The ID column is just an ID without adding a prefix.

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