sql - MySQL - 选择除此表中的内容以外的所有内容

Question

我想选择用户尚未拥有的所有图像。

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

• _user (id, user, pass)
• _image (id, 数据)
• _user_image (id, user_id, image_id)

Answer 1

使用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

---

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

---

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%。 See this for more details .