Last Updated: 2024-11-21

Say you have the following schema

User 
- id
- email

Product
- id
- name
- user_id

i.e. a User has many Products.

This might be modelled in Rails etc. with:

class User
  has_many :products
end

class Product
  belongs_to :user
end

Given that, how would you get the users that have zero products?

The trick is to use an outer join and then a where clause looking for when the user_id is null.

So something like this:

SELECT * 
FROM users 
LEFT OUTER JOIN products ON products.user_id = users.id 
WHERE user_id is null;

Would an inner join on user_id work? No because this filters any users that have no products.