I've got a schema such as this:

// table badge_types
id | name
+++|++++++++++
1  | mentor
2  | proctor
3  | doctor

// table badges
id | badge_type_id | user_id     
+++|+++++++++++++++|++++++++
1  | 1             | 5
2  | 1             | 6
3  | 2             | 6
4  | 3             | 6
5  | 2             | 19
6  | 3             | 20

What I wish to do, is choose all badge_types that the particular user has not become. Within the above example, calling the query for:

user_id = 5 returns badge_type_id 2 and 3

user_id = 6 returns empty set (user got these already)

user_id = 19 returns badge_type_id 1 and 3

I'm able to do that by having an INTERSECT clause. But I am wondering if it is possible having a simple JOIN? Any help could be appreciated.

Using LEFT JOIN/IS NULL:

   SELECT bt.name
     FROM BADGE_TYPES bt
LEFT JOIN BAGDES b ON b.badge_type_id = bt.id
                  AND b.user_id = ?
    WHERE b.id IS NULL

Using NOT Is available

   SELECT bt.name
     FROM BADGE_TYPES bt
    WHERE NOT EXISTS(SELECT NULL
                       FROM BADGES b 
                      WHERE b.badge_type_id = bt.id
                        AND b.user_id = ?)

Using NOT IN

   SELECT bt.name
     FROM BADGE_TYPES bt
    WHERE bt.id NOT IN (SELECT b.badge_type_id
                       FROM BADGES b 
                      WHERE b.user_id = ?)

MySQL

When the posts are not nullable, LEFT JOIN/IS NULL is the best choice. When they are nullable, [cde] and [cde] are the best choice.

SQL Server

Mind that on SQL Server, [cde] and [cde] perform better than LEFT JOIN /IS NULL if the columns in question are not nullable.

Oracle

For Oracle, all three are equivalent but LEFT JOIN/IS NULL and NOT EXISTS are preferable if columns aren't nullable.