CODEWITHSUNDEEP
mysql
Yevgeny Simkin
Yevgeny Simkin

Reputation: 28349

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: 51110

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

Upvotes: 2

Paul Morgan
Paul Morgan

Reputation: 32528

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: 3829

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: 28349

NOT IN (select CATEGORIES.item_id)

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

Upvotes: 3

KM.
KM.

Reputation: 103587

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: 19320

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