SQL Code to get results when there are no records in a table

Question

There are 4 tables (user, user options, option type 1, and option type 2). The user table is not directly involved here but mentioned for completeness. Here are the table columns for each of the involved tables.

User Options:
    UserID,
    Option1ID,
    Option2ID,

Option1:
    Option1ID,
    Option1Description

Option2:
    Option2ID,
    Option2Description

All the values for Option1 can be combined with all the values for Option2 hence if there a 'x' number of Option1 values and 'y' number of Option2 values then the resultant number of option combinations is 'x' * 'y'. I want to write a query such that it will always result in 'x' * 'y' rows for a given UserID even if there is no record for a particular combination of Option1ID and Option2ID for that user in the UserOptions table. Additionally there should be a column that indicates if the particular combination of Option1 and Option2 exists in the UserOptions table.

Option1
Option1ID   Option1Description
----------------------------------
1           1_Description1
2           1_Description2


Options2
Option2ID   Option2Description
----------------------------------
1           2_Description1
2           2_Description2


UserOptions
UserID  Option1ID   Option2ID
---------------------------------
1       1           2
1       2           2


Result
UserID  Option1ID   Option2ID   Exists
----------------------------------------------
1       1           1           0
1       1           2           1
1       2           1           0
1       2           2           1

Given the above what would the SQL query be? In addition note that UserID = 2 does not exist in the UsersOptions table. In this case the query should still return 4 records where the UserID column will always be 2, the OptionID columns will be the same, and the Exists column will always be 0.

Answer 1

You can cross join the users table with the two options tables to generate all possible combinations, then search for a match in bridge table useroptions with a left join:

select u.userid, o1.option1id, o2.option2id, 
    case when uo.userid is null then 0 else 1 end as uo_exists
from users u
cross join option1 o1
cross join option2 o2
left join useroptions uo 
    on  uo.userid = u.id
    and uo.option1id = o1.option1id
    and uo.option2id = o1.option2id

You could also use exists instead of a left join:

select u.userid, o1.option1id, o2.option2id, 
    case when exists (
        select 1 
        from useroptions uo 
        where  uo.userid = u.id and uo.option1id = o1.option1id and uo.option2id = o1.option2iduo.userid
    ) then 1 else 0 end as uo_exists
from users u
cross join option1 o1
cross join option2 o2