CODEWITHSUNDEEP
mysql
Yevgeny Simkin
Yevgeny Simkin

Reputation: 28439

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

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.

Upvotes: 1

Views: 193

Answers (6)

Joe Phillips
Joe Phillips

Reputation: 51200

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

Upvotes: 2

Paul Morgan
Paul Morgan

Reputation: 32578

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.

Upvotes: 0

James Conigliaro
James Conigliaro

Reputation: 3827

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

Upvotes: 0

Yevgeny Simkin
Yevgeny Simkin

Reputation: 28439

NOT IN (select CATEGORIES.item_id)

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

Upvotes: 3

KM.
KM.

Reputation: 103727

try this:

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

Upvotes: 3

Mike Pone
Mike Pone

Reputation: 19330

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

Upvotes: 0

Related Questions