Sunday, November 9, 2008

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

3 comments:

Ravinder Reddy said...

Hi Nitin that example is good on joins with null valuse.
Nitin in that example you have written table name like contactinfo while you introduce the columns of this table,
but in joins you have written as contact.cityid like.

just modify it.

Nitin R.K. said...

@Ravinder: Thanks you for your feedback. I've made the correction - the SQL statement now has ContactInfo table name.

Melroy said...

THX!