SQL Server 查询 : Using JOIN to include NULL values

Question

我需要以下 SQL Server 查询的帮助，其中 a.TAProfileID 和 c.CountryCode 列在数据库中具有“NULL”值。

我希望我的 JOIN 语句在它们存在的地方返回“NULL”值。

SELECT 
a.ReservationStayID AS 'Reservation Id',
a.PMSConfirmationNumber as 'PMS No',
a.CreatedOn AS 'Date Created',
a.ArrivalDate AS 'Date of Arrival',
a.DepartureDate AS 'Date of Departure',
a.TAProfileID AS 'TA Id',
a.StatusCode AS 'Status',
b.PropertyCode AS 'Hotel',
c.Name AS 'Travel Agency',
c.CountryCode AS 'Market Code',
d.CountryName AS 'Mkt'

FROM ReservationStay a

inner JOIN GuestStaySummary b ON a.ReservationStayID = b.ReservationStayID
inner JOIN TravelAgency c ON a.TAProfileID = c.TravelAgencyID
inner JOIN Market d ON c.CountryCode = d.CountryCode

Answer 1

以退货或生产NULL您必须使用的值 LEFT JOIN s。

所以，你的查询应该是这样的:

SELECT 
     a.ReservationStayID AS 'Reservation Id'
    ,a.PMSConfirmationNumber AS 'PMS No'
    ,a.CreatedOn AS 'Date Created'
    ,a.ArrivalDate AS 'Date of Arrival'
    ,a.DepartureDate AS 'Date of Departure'
    ,a.TAProfileID AS 'TA Id'
    ,a.StatusCode AS 'Status'
    ,b.PropertyCode AS 'Hotel'
    ,c.NAME AS 'Travel Agency'
    ,c.CountryCode AS 'Market Code'
    ,d.CountryName AS 'Mkt'
FROM ReservationStay a
    INNER JOIN GuestStaySummary b ON a.ReservationStayID = b.ReservationStayID
    LEFT JOIN TravelAgency c ON a.TAProfileID = c.TravelAgencyID
    LEFT JOIN Market d ON c.CountryCode = d.CountryCode