Find units with zero categories

Question

Hello,

I am after an SQL command to find units with no categories. Here are the tables:

Unit

id  name

UnitCategory

id  name

UnitCategoryIndex

id  categoryid  unitid

Thanks!

Answer 1

SELECT id FROM Unit WHERE NOT EXISTS (SELECT 1 FROM UnitCategoryIndex WHERE Unit.id = UnitCategoryIndex.unitid)

Answer 2

select *
  from unit U
 where not exists ( select *
                      from unitcategoryindex X
                     where X.unitid = U.id
                  )

Answer 3

Double check the syntax (i wrote it for SQL Server).

SELECT u.id, u.name
FROM Unit as u
LEFT JOIN UnitCategoryIndex as uci
ON u.id = uci.UnitId
where uci.id is null