mysql - 帮忙写sql查询

Question

我有一个看起来像这样的数据库:

联系人

id | name1  | bob2  | jack3  | jill

contactsGroupLink

cId| gId1  | 12  | 32  | 32  | 53  | 4

So basically,

a contact is linked to a group by an entry in the contactsGroupLink table.

A contact may be in multiple groups, but a contact may only be on a group once.

The query I want to write is

select `name` 
  from contacts 
 where contact.id not in (select contactId 
                           from contactsGroupLink 
                          where groupId = 5);

哪个有效。它返回 bob 和 jill。

但是它不是很优化，因为它有一个依赖子查询。谁能帮忙优化一下？

Answer 1

因为两列都不太可能为 NULL，所以在 MySQL (only) the best option is to use the LEFT JOIN/IS NULL 中:

   SELECT c.name
     FROM CONTACTS c
LEFT JOIN CONTACTSGROUPLINK cgl ON cgl.contactid = c.id
                               AND cgl.groupid = 5
    WHERE cgl.contactid IS NULL

If the columns were nullable , NOT EXISTS 是更好的选择:

   SELECT c.name
     FROM CONTACTS c
    WHERE NOT EXISTS (SELECT NULL
                        FROM CONTACTSGROUPLINK cgl
                       WHERE cgl.contactid = c.id
                         AND cgl.groupid = 5)

CONTACTSGROUPLINK 表中的两列应该是主键，它将自动为列编制索引(从 ~5.0+ 开始？)。否则，请确保列已编入索引。