MySQL: Select remaining rows

Question

I've searched everywhere for this, but I can't seem to find a solution. Perhaps I'm using the wrong terms. Either way, I'm turning to good ol' trusty S.O. to help my find the answer.

I have two tables, we'll call them 'tools' and 'installs'

tools = id, name, version
installs = id, tool_id, user_id

The 'tools' table records available tools, which are then installed by a user and recorded in the 'installs' table. Selecting the installed tools are simple enough:

SELECT tools.name
  FROM tools
  LEFT JOIN installs
  ON tools.id = installs.tool_id
  WHERE user_id = 99 ;

How do I select the remaining tools -- the ones that have yet to be installed by user #99?

I'm sorry if this is painfully obvious, but I just can't seem to figure it out! Thanks for the help!

Answer 1

Use:

   SELECT t.name
     FROM TOOLS t
LEFT JOIN INSTALLS i ON i.tool_id = t.id
                    AND i.user_id = 99
    WHERE i.id IS NULL

Alternately, you can use NOT EXISTS:

SELECT t.name
  FROM TOOLS t
 WHERE NOT EXISTS(SELECT NULL 
                    FROM INSTALLS i
                   WHERE i.tool_id = t.id
                     AND i.user_id = 99)

...or NOT IN:

SELECT t.name
  FROM TOOLS t
 WHERE t.id NOT IN (SELECT i.tool_id
                      FROM INSTALLS i
                     WHERE i.user_id = 99)

Of the three options, the LEFT JOIN/IS NULL is the most efficient on MySQL. You can read more about it in this article.