MySQL-选择除此表中的内容以外的所有内容 [英] MySQL - Select All Except what is in this Table

问题描述

我要选择用户尚未拥有的所有图像.

I am wanting to select all images that a user does not already have.

我有三个表:user，image和user_image:

• _user(ID，用户，密码)
• _image(id，数据)
• _user_image(id，user_id，image_id)

推荐答案

使用LEFT JOIN/IS NULL

---

Using LEFT JOIN/IS NULL

---

   SELECT DISTINCT
          i.data
     FROM IMAGE i
     JOIN USER_IMAGE ui ON ui.image_id = i.id
LEFT JOIN USER u ON u.id = ui.user_id
                AND u.user = ?
    WHERE u.id IS NULL

使用NOT IN

---

Using NOT IN

---

SELECT DISTINCT
       i.data
  FROM IMAGE i
  JOIN USER_IMAGE ui ON ui.image_id = i.id
 WHERE ui.user_id NOT IN (SELECT u.id
                            FROM USER u
                           WHERE u.user = ?)

使用NOT EXISTS

---

Using NOT EXISTS

---

SELECT DISTINCT
       i.data
  FROM IMAGE i
  JOIN USER_IMAGE ui ON ui.image_id = i.id
 WHERE NOT EXISTS(SELECT NULL
                    FROM USER u
                   WHERE u.id = ui.user_id
                     AND u.user = ?)

性能:

---

LEFT JOIN/IS NULL和NOT IN提供相同的性能-NOT EXISTS的效率低约30％. 看到这个了解更多详情.

Performance:

---

The LEFT JOIN/IS NULL and NOT IN provide equivalent performance - the NOT EXISTS is ~30% less efficient. See this for more details.

这篇关于MySQL-选择除此表中的内容以外的所有内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！