mysql查询选择所有内容，除了 [英] mysql query to select everything except

问题描述

我有两个mysql表.

i have two mysql tables.

第一个称为选择"，由id，user_id和widget_id字段组成.

the first is called "chosen" and consists of id, user_id, and widget_id fields.

另一个称为小部件"，其中包括widget_id.

the other is called "widgets" and includes several things including widget_id.

我创建了一个过滤器，以便用户可以显示他/她选择的小部件，或者显示他/她没有选择的小部件.对于他选择的人，我使用它:

i created a filter so that the user can either display widgets that he/she has chosen, or widgets that he/she hasn't chosen. for the ones he has chosen, i use this:

SELECT * 
  FROM widgets, chosen 
 WHERE chosen.user_id = $user_id 
   AND chosen.widget_id = widgets.widget_id

但是，我不知道如何显示他/她没有选择的内容.这不起作用(显示所有内容):

however, i can't figure out how to display the ones that he/she hasn't chosen. this doesn't work (displays everything):

SELECT * 
  FROM widgets, chosen 
 WHERE !(    chosen.user_id = $user_id 
         AND chosen.widget_id = widgets.widget_id)

我该怎么做?

推荐答案

使用NOT IN:

SELECT w.*
  FROM WIDGET w
 WHERE w.widget_id NOT IN (SELECT c.widget
                             FROM CHOSEN c
                            WHERE c.user_id = $user_id)

使用不存在:

SELECT w.*
  FROM WIDGET w
 WHERE NOT EXISTS (SELECT NULL
                     FROM CHOSEN c
                    WHERE c.widget_id = w.widget_id 
                      AND c.user_id = $user_id)

左联接/为空:

   SELECT w.*
     FROM WIDGET w
LEFT JOIN CHOSEN c ON c.widget_id = w.widget
                  AND c.user_id = $user_id
    WHERE w.widget IS NULL

性能:

如果 查看全文