How to I get a list of non subscribed users from this database? I have 2 tables. 

1) subscription_types

 | type |
  'type1'
  'type2'


2) subscriptions

| user | sub_type | subscribed |
  'U1'   'type1'    'Y'
  'U1'   'type2'    'Y'
  'U2'   'type1'    'Y'


From this ERD you can see that user 'U2' does not have entry for subscription 'type2'. I'd like to have a report of users who do not have subscription record.

So far I have:

 select user, count(1) 
 from subscriptions 
 group by user
 having count(1) != (select count(1) from subscription_types);


Which gives me a list of users that do not have all subscriptions. But not which ones they are missing. 

I've tried many different variations of join queries where the subscription_type is null, no luck. 

I was hoping the following query would solve it but alas it did not 

 select user, sub_type
 from subscriptions 
 group by user, sub_type
 having sub_type not in (select type from subscription_types);
It seems to me that you want the cartesian product between users and subscription_types, minus the subscriptions:

select distinct s.user, st.subscription_types
from subscriptions s
cross join subscription_types st
except
select user, sub_type
from subscriptions


This can, of course, be expressed in other ways, but this is probably the most obvious one.