mysql query to select everything except

i have two mysql tables.

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

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 wid开发者_Go百科gets, chosen 
 WHERE !(    chosen.user_id = $user_id 
         AND chosen.widget_id = widgets.widget_id)

how do i do this?

Using 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)

Using NOT EXISTS:

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)

LEFT JOIN/IS NULL:

   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

Performance:

If the columns compared (widget_id in either table) are not nullable, LEFT JOIN/IS NULL performs the best on MySQL. If the columns are nullable (the value could be NULL), NOT IN or NOT EXISTS perform better.

Boolean logic: (ab)' is the same as a' + b'

SELECT * 
  FROM widgets, chosen 
 WHERE chosen.user_id <> $user_id 
         OR chosen.widget_id <> widgets.widget_id

Basically, you want to select all rows from widgets that have no associated row in chosen for the given $user_id. You can accomplish this with a LEFT OUTER JOIN looking for the rows that didn't match in the chosen table. Something like this:

SELECT w.* FROM widgets as w
LEFT OUTER JOIN chosen as c on c.widget_id=w.widget_id AND c.user_id=$user_id
WHERE c.id IS NULL;

BTW: I recommend using the newer join syntax over the comma delimited tables join syntax. It's easier to read and understand, especially when it comes to outer joins.