Select * where association is empty in SQL?

Question

So I'm a bit lost in SQL and especially in the right syntax to use.

To be quick : These are my tables

We have products :

+------------------------+------------------+------+-----+---------------------+----------------+
| Field                  | Type             | Null | Key | Default             | Extra          |
+------------------------+------------------+------+-----+---------------------+----------------+
| id                     | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| slug                   | varchar(255)     | NO   |     | NULL                |                |
| title                  | varchar(255)     | NO   |     | NULL                |                |
| resume                 | varchar(255)     | NO   |     | NULL                |                |
| country_id             | varchar(255)     | NO   |     | NULL                |                |
| city_id                | varchar(255)     | NO   |     | NULL                |                |
+------------------------+------------------+------+-----+---------------------+----------------+

And we have cities :

+------------------+------------------+------+-----+---------------------+----------------+
| Field            | Type             | Null | Key | Default             | Extra          |
+------------------+------------------+------+-----+---------------------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| country_id       | int(11)          | NO   |     | NULL                |                |
| name             | varchar(255)     | NO   |     | NULL                |                |
+------------------+------------------+------+-----+---------------------+----------------+

What I want to find is which cities does not have products.

So basically I tried something like this :

SELECT name FROM cities WHERE COUNT (SELECT * FROM cities INNER JOIN products ON products.city_id = cities.id) = 0;

I think I have a misunderstanding of how COUNT works, can someone help on this one?

Answer 1

You can do it with NOT EXISTS:

SELECT name 
FROM cities AS c
WHERE NOT EXISTS (SELECT 1
                  FROM products AS p
                  WHERE p.city_id = c.id)

The above query returns the names of all cities not being linked to a product.

Answer 2

You can use the following query:

SELECT name
FROM cities
WHERE id NOT IN (
    SELECT DISTINCT city_id FROM products
)