数据库 |如何连接两个表以获得不同的行？

Question

Skills:                       user_skills:

+-------------------------+   +--------------------------------+
| UID | Skill                 | UID | user_id | skill_id
---------------------------   +---------------------------------
|  1  | C++                   |  1  |    34   |     1
|  2  | C#                    |  2  |    34   |     2
|  3  | Python   

我的预期输出是:

+----------------------------------+
| ID(Skill ID from Skills) | Skill               
------------------------------------
| 3                        | Python     

我只想要用户 34 没有的技能。

当前获得用户所拥有的技能我正在使用:

SELECT ss. * 
FROM skills AS s
LEFT JOIN user_skills AS us ON s.UID = us.skill_id
WHERE us.user_id = 34

但我似乎无法理解逻辑以得出相反的结论。因为当每个用户有不止一种技能时，这不起作用。

SELECT s. * 
FROM skills AS s
LEFT JOIN user_skills AS us ON s.UID != us.skill_id
WHERE us.user_id = 34

Answer 1

not exists 运算符更适合此要求:

SELECT *
FROM   skills s
WHERE  NOT EXISTS (SELECT *
                   FROM   user_skills us
                   WHERE  us.skill_id = s.uid AND
                          us.user_id = 34)