how can I select from one table based on non matches in another in mySQL

Question

I have two tables

ITEMS : id, name

CATEGORIES: items_id, category

I need to select all the items whose IDs are NOT in the CATEGORIES table.

I suspect it's really simple, but can't figure out the syntax.

Answer 1

This selects everything from the items table and only the records from categories that match the join. Then filter out the nulls.

Select Item.Id
FROM ITEMS LEFT OUTER JOIN CATEGORIES On
Items.Id = Categories.items_id
where categories.items_id is null

Answer 2

try this:

SELECT
    i.*
    FROM Items   i
        LEFT OUTER JOIN Categories  c ON i.id=c.items_id
        WHERE c.items_id is NULL

Answer 3

NOT IN (select CATEGORIES.item_id)

not sure if that's faster than the join above... but it works.

Answer 4

How about

SELECT id
, name
FROM ITEMS
WHERE NOT EXISTS(SELECT 1 FROM CATEGORIES WHERE Categories.items.id = ITEMS.id)

This will only bring back items that do not have at least one entry in the Categories table

Answer 5

SELECT items.id
  FROM items
 WHERE NOT EXISTS( SELECT *
                     FROM categories
                    WHERE items.id = categories.items.id )

This is the same as joining to the categories table as Mike Pone and KM listed but I find this more readable.

Answer 6

SELECT * FROM Items
WHERE id NOT IN (SELECT items_id FROM Categories)