SQL Server SQL Join 2 Tables and leave null when not found

5ssjco0h  于 2023-06-28  发布在  其他
关注(0)|答案(3)|浏览(117)

I have 2 Tables

[Countries]
| ID | Name |
| ------------ | ------------ |
| 1 | Japan |
| 2 | Egypt |
| 3 | France |
| 4 | England |
| 5 | Canada |

[VisitedCountries]

UserIDCountryID
12
13
15
24
31
35

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?

dced5bon

dced5bon1#

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;
lhcgjxsq

lhcgjxsq2#

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Countries
    (`ID` int, `Name` varchar(7))
;
    
INSERT INTO Countries
    (`ID`, `Name`)
VALUES
    (1, 'Japan'),
    (2, 'Egypt'),
    (3, 'France'),
    (4, 'England'),
    (5, 'Canada')
;

CREATE TABLE VisitedCountries
    (`UserID` int, `CountryID` int)
;
    
INSERT INTO VisitedCountries
    (`UserID`, `CountryID`)
VALUES
    (1, 2),
    (1, 3),
    (1, 5),
    (2, 4),
    (3, 1),
    (3, 5)
;

Query 1:

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) |
kgsdhlau

kgsdhlau3#

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'

相关问题