SQL Join 2 Tables and leave null when not found

Question

I have 2 Tables

[Countries]

ID	Name
1	Japan
2	Egypt
3	France
4	England
5	Canada

[VisitedCountries]

UserID	CountryID
1	2
1	3
1	5
2	4
3	1
3	5

I'd like to have a list of all the items in the [Countries] Table + 1 additional column with the UserID as the WHERE statement. Like so :

[ExpectedResult] (WHERE UserID = 1)

UID	CID	CName
Null	1	Japan
1	2	Egypt
1	3	France
Null	4	England
1	5	Canada

I tried multiple different JOIN statements, but i still only get the lines where UID is not null only :

[WRONGResult] (WHERE UserID = 1)

UID	CID	CName
1	2	Egypt
1	3	France
1	5	Canada

SELECT c.*, vc.UID   
FROM [Countries] AS c 
JOIN [VisitedCountries] AS vc ON vc.CID = c.ID   
WHERE vc.UID = '1'

UNION ALL did not work either, it just returns 2 tables as a list, not joined.

Care to help or point me to the right documentation / direction?

Answer 1

You simply need to outer join your tables and note the conditions are part of the joining criteria, not a filtering criteria as you still require all rows:

select v.UserId, c.Id, c.Name 
from countries c
left join visitedcountries v on v.CountryId = c.Id and v.UserId = 1;

Answer 2

Move the condition on left joined table column from WHERE into ON clause of LEFT JOIN

SELECT c.*, vc.UID   
FROM [Countries] AS c 
LEFT JOIN [VisitedCountries] AS vc ON vc.CID = c.ID AND vc.UID = '1'

Answer 3

SQL Fiddle

Your Query:

SELECT c.*, vc.UserID   
FROM Countries AS c 
JOIN VisitedCountries AS vc ON vc.CountryID = c.ID   
WHERE vc.UserID = '1'

Results:

| ID |   Name | UserID |
|----|--------|--------|
|  2 |  Egypt |      1 |
|  3 | France |      1 |
|  5 | Canada |      1 |

Query 2:

SELECT c.*, vc.UserID   
FROM Countries AS c 
LEFT JOIN VisitedCountries AS vc ON vc.CountryID = c.ID AND  vc.UserID = '1'

Results:

| ID |    Name | UserID |
|----|---------|--------|
|  2 |   Egypt |      1 |
|  3 |  France |      1 |
|  5 |  Canada |      1 |
|  1 |   Japan | (null) |
|  4 | England | (null) |