retrieving items disregarded by a table join?

Question

I have two mysql tables. ItemTable which is simple an item-id and some item related data.

 --------------------------------------
| ItemId | ItemDescription | ItemPrice |
 --------------------------------------

I have another table CategoryTable that is responsible for categorizing the items into different categories

  --------------------------------------
| ItemId | CategoryId                   |
 ---------------------------------------

Not all items are categorized. What is the fastest/most-efficient way to find an uncategorized item (by its id) without changing the schemas of the two tables. I tried joining the two tables on ItemId but it disregards the uncategorized items.

Answer 1

Just select the ItemIds from the CategoryTable, and then select those ids in ItemTable that are not in CategoryTable.

SELECT ItemId
FROM ItemTable
WHERE ItemId NOT IN (SELECT C.ItemId
                     FROM CategoryTable C);

If you want every ItemId and its Category, and also include those items that have no category, you can use LEFT JOIN. This will output NULL for the Category field of uncategorized items.

SELECT I.ItemId, C.Category
FROM ItemTable I
LEFT JOIN CategoryTable C
ON I.ItemId = C.ItemId;