JOIN on column that may contain NULL

Question

I want to do something like this:

SELECT
    Users.id,
    Countries.name 
FROM
    Users, Countries
WHERE 
    Users.country_id = Countries.id

The issue is that Users.country_id can sometimes be NULL, and if it is the entire row is not returned. I want it to return the row but with 'null' or '' in that column. I tried this:

SELECT
    Users.id,
    Countries.name 
FROM
    Users, Countries
WHERE 
    (Users.country_id = Countries.id OR Users.country_id IS NULL)

However that returns the row once for each *Countries.id* that exists. How do I work around this?

Answer 1

SELECT
    Users.id,
    Countries.name 
FROM
    Users, Countries
WHERE 
    Users.country_id = Countries.id
UNION
SELECT
    id,
    '{{no country}}' AS name 
FROM
    Users
WHERE 
   NOT EXISTS ( SELECT * 
                  FROM Countries
                 WHERE Users.country_id = Countries.id );

Answer 2

SELECT          Users.id,
                Countries.name
FROM            Users
LEFT OUTER JOIN Countries ON Users.country_id = Countries.id

Answer 3

SELECT  users.id, countries.name
FROM    users
LEFT JOIN
        countries
ON      countries.id = users.country_id

This is a good example of why the ANSI join syntax (using the JOIN keyword) is preferred.