SQL Join and NULL Values

A common problem face by inexperienced SQL developers is in forming joins with tables containing NULL values. Let's take the following ContactInfo & City tables as an example:

ContactInfo columns:
ContactId INT
ContactName VARCHAR(50)
CityId INT

ContactInfo Data: { (1, 'Nitin', 1), {2, 'Reddy', NULL) }

City columns:
CityId INT
CityName VARCHAR(50)

City Data: { (1, 'Dubai') }

When joining the ContactInfo and the City tables, using the SQL statement
select * from ContactInfo
join City on ContactInfo.CityId = City.CityId
you would get only one row in the result: { (1, 'Nitin', 1) }

This occurs because the join removes the rows with NULL values as the expression from the join condition (NULL = 1) evaluates to false.

To retrieve data with the null values, the query has to be modified to:
select * from ContactInfo
left outer join City on ContactInfo.CityId = City.CityId