SQL Server查询：使用JOIN包含NULL值

user3115933 发表于 Dev

用户名

我需要以下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

拉杜·格奥尔基（Radu Gheorghiu）

为了返回或产生NULL值，您将必须使用LEFT JOIN。

因此，您的查询应类似于：

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