PostgreSQL：如何从video_all中排除video_real的同键数据行？

问题场景 #

现有两张表的数据如下：

video_real表 #

device_id | video_definition 
-----------+------------------
         1 |              360
         1 |              480
(2 rows)

video_all表 #

device_id | video_definition 
-----------+------------------
         1 |              360
         1 |              480
         1 |              540
         1 |              720
         1 |             1080
(5 rows)

需要提取出video_all中存在，但video_real里没有的记录，目标结果：

device_id | video_definition 
----------+------------------
        1 |              540
        1 |              720
        1 |             1080
(3 rows)

实现方式 #

下面给你几种可行的PostgreSQL写法：

1. 使用NOT EXISTS子查询 #

这是最直观的写法，通过子查询判断当前记录是否在video_real中不存在：

SELECT device_id, video_definition
FROM video_all va
WHERE NOT EXISTS (
    SELECT 1
    FROM video_real vr
    WHERE vr.device_id = va.device_id
      AND vr.video_definition = va.video_definition
);

2. 使用LEFT JOIN + IS NULL #

通过左连接两张表，筛选出连接后video_real字段为空的记录，也就是video_all独有的数据：

SELECT va.device_id, va.video_definition
FROM video_all va
LEFT JOIN video_real vr
    ON va.device_id = vr.device_id
    AND va.video_definition = vr.video_definition
WHERE vr.device_id IS NULL;

3. 使用EXCEPT集合操作 #

EXCEPT会返回第一个查询结果中存在，但第二个查询结果中没有的记录，注意两个查询的字段顺序和类型要完全一致：

SELECT device_id, video_definition
FROM video_all
EXCEPT
SELECT device_id, video_definition
FROM video_real;

这三种方法都能得到你想要的结果，实际使用时可以根据表的数据量和索引情况选择，比如NOT EXISTS在两张表的关联字段有索引时，性能通常更优。

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