Last Updated: 2022-12-01
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
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;
inner join on
user_id work? No because
this filters any users that have no products.