SQL join with null values not returning all rows-CodePudding

I've got a query with two joins. One is a standard inner join, and the other is used to translate a country code to the full country name.

Query:

SELECT Company.Comp_CompanyId, Company.comp_bigchange_contactid, Company.Comp_Type, Company.Comp_Name, Address.Addr_Address1, Address.Addr_Address2, Address.Addr_Address3, Address.Addr_Address4, Address.Addr_City, Address.Addr_PostCode, Custom_Captions.Capt_UK AS Addr_Country, Company.Comp_UpdatedDate
FROM Company 
INNER JOIN Address ON Company.Comp_PrimaryAddressId = Address.Addr_AddressId
INNER JOIN Custom_Captions ON Address.Addr_Country = Custom_Captions.Capt_Code
WHERE (Custom_Captions.Capt_Family = 'addr_country') AND Company.comp_bigchange_sync = 'Y' 
ORDER BY Company.Comp_UpdatedDate ASC

As an example Address.Addr_Country could be 'GB', and the Custom_Captions table would have a row to translate that into 'United Kingdom' (Custom_Captions.Capt_UK)

This works fine except for when the Address.Addr_Country field is Null (not selected). When that field is Null, the whole row is not returned.

Can anyone suggest a way to make this show even when the Addr_Country field is Null? It would just need to return a null value for the country.

CodePudding user response：

Make the join a LEFT OUTER JOIN and move the WHERE clause associated with it to the join - simpler than making the where clause also check for null there perhaps. Related question on this type of a change: SQL JOIN Condition moved to with where clause produces differences

SELECT Company.Comp_CompanyId, Company.comp_bigchange_contactid, Company.Comp_Type, Company.Comp_Name, Address.Addr_Address1, Address.Addr_Address2, Address.Addr_Address3, Address.Addr_Address4, Address.Addr_City, Address.Addr_PostCode, Custom_Captions.Capt_UK AS Addr_Country, Company.Comp_UpdatedDate
FROM Company 
INNER JOIN Address ON Company.Comp_PrimaryAddressId = Address.Addr_AddressId
LEFT OUTER JOIN Custom_Captions ON Address.Addr_Country = Custom_Captions.Capt_Code
    AND Custom_Captions.Capt_Family = 'addr_country' 
WHERE Company.comp_bigchange_sync = 'Y' 
ORDER BY Company.Comp_UpdatedDate ASC

CodePudding user response：

You would need to use a LEFT or RIGHT JOIN on your query but since you're filtering on that row in the WHERE, it would be best to add that filter as a subquery:

SELECT Company.Comp_CompanyId, Company.comp_bigchange_contactid, 
Company.Comp_Type, Company.Comp_Name, Address.Addr_Address1, 
Address.Addr_Address2, Address.Addr_Address3, Address.Addr_Address4, 
Address.Addr_City, Address.Addr_PostCode, Custom_Captions.Capt_UK AS 
Addr_Country, Company.Comp_UpdatedDate
FROM Company 
    INNER JOIN Address ON Company.Comp_PrimaryAddressId = 
Address.Addr_AddressId
    LEFT JOIN (SELECT * FROM Custom_Captions WHERE 
Custom_Captions.Capt_Family = 'addr_country') Custom_Captions ON 
Address.Addr_Country = Custom_Captions.Capt_Code
WHERE Company.comp_bigchange_sync = 'Y' 
ORDER BY Company.Comp_UpdatedDate ASC