SQLite中两表基于Name列取交集获指定列及差集实现

表结构与数据 #

people表 #

work表 #

1. 获取两表共有的Name对应的work_id和age #

使用INNER JOIN关联两个表，只返回Name匹配的行，直接获取所需列：

SELECT w.work_id, p.age
FROM work w
INNER JOIN people p ON w.Name = p.Name;

执行结果：

2. 获取work表中Name不在people表的行 #

有两种常用写法：

方法一：NOT IN子查询 #

SELECT *
FROM work
WHERE Name NOT IN (SELECT Name FROM people);

方法二：LEFT JOIN + IS NULL #

这种写法在Name存在NULL值时更可靠：

SELECT w.*
FROM work w
LEFT JOIN people p ON w.Name = p.Name
WHERE p.Name IS NULL;

两种方法的执行结果：

关于子查询的疑问 #

你写的SELECT Name, Age FROM people where Name IN (SELECT Name FROM work)这类子查询，SQLite不会逐行将people表的记录和work表所有行比较。SQLite的查询优化器会自动优化这类语句：

• 如果Name列有索引，会直接利用索引快速匹配，避免全表扫描；
• 即使没有索引，优化器也会先执行子查询得到一个临时的Name集合，再用这个集合去匹配people表的行，而不是逐行做全表比对。

内容的提问来源于stack exchange，提问作者user13840624