sql - 使用 JOIN 而不是 INTERSECT 查询

Question

我有这样的架构:

// table badge_types
id | name
+++|++++++++++
1  | mentor
2  | proctor
3  | doctor

// table badges
id | badge_type_id | user_id     
+++|+++++++++++++++|++++++++
1  | 1             | 5
2  | 1             | 6
3  | 2             | 6
4  | 3             | 6
5  | 2             | 19
6  | 3             | 20

我想做的是选择特定用户尚未获得的所有 badge_types。在上面的示例中，调用查询:

user_id = 5 返回 badge_type_id 2 和 3

user_id = 6 返回 empty 集(用户已经得到所有的)

user_id = 19 返回 badge_type_id 1 和 3

我可以使用 INTERSECT 子句来做到这一点。但我想知道是否可以通过简单的 JOIN 来完成？任何帮助将不胜感激。

Answer 1

使用 LEFT JOIN/IS NULL:

   SELECT bt.name
     FROM BADGE_TYPES bt
LEFT JOIN BAGDES b ON b.badge_type_id = bt.id
                  AND b.user_id = ?
    WHERE b.id IS NULL

使用 NOT EXISTS

   SELECT bt.name
     FROM BADGE_TYPES bt
    WHERE NOT EXISTS(SELECT NULL
                       FROM BADGES b 
                      WHERE b.badge_type_id = bt.id
                        AND b.user_id = ?)

使用 NOT IN

   SELECT bt.name
     FROM BADGE_TYPES bt
    WHERE bt.id NOT IN (SELECT b.badge_type_id
                       FROM BADGES b 
                      WHERE b.user_id = ?)

MySQL

如果列是 not nullable, LEFT JOIN/IS NULL is the best choice .如果他们are nullable, NOT EXISTS and NOT IN are the best choice .

SQL服务器

注意 on SQL Server, NOT IN and NOT EXISTS perform better than LEFT JOIN /IS NULL if the columns in question are not nullable .

甲骨文

对于 Oracle, all three are equivalent but LEFT JOIN/IS NULL and NOT EXISTS are preferable if columns aren't nullable .