Start preparing for your Next Exam | Use coupon – TOGETHER | Avail 30% discount

Join SQL Server Tables

  1. Home
  3. Exam 70-761: Querying Data with Transact-SQL
  5. Join SQL Server Tables

Join SQL Server tables where columns include NULL values

Problem and Concern

When building database tables you are faced with the decision of whether to allow NULL values or to not allow NULL values in your columns. By default SQL Server sets the column value to allow NULL values when creating new tables, unless other options are set. This is not necessarily a bad thing, but dealing with NULL values especially when joining tables can become a challenge. Let’s take a look at this issue and how this can be resolved.

Problem Solution
In most cases your columns in your tables probably allow NULL values unless you consciously changed them when you created a table or changed the default setting for your database by using the SET ANSI_NULL_DEFAULT OFF and SET ANSI_NULL_DFLT_ON OFF option. These settings change the behavior of how a table is created if you do not specify NULL or NOT NULL when creating a table.

The following illustration show you how a table would get created differently with each of these options, but by default SQL Server sets columns to allow nulls.

Illustration 1: When these two options are OFF the default nullable value is set to no, meaning no null values are acceptable.

ALTER DATABASE AdventureWorks SET ANSI_NULL_DEFAULT OFF;
GO
SET ANSI_NULL_DFLT_ON OFF;
GO
CREATE TABLE Table1 (a TINYINT);
GO
sp_help Table1
GO
The screen shot below shows that the column does not allow NULLs.

Illustration 2: When these two options are ON the default nullable value is set to yes, meaning null values are acceptable.

ALTER DATABASE AdventureWorks SET ANSI_NULL_DEFAULT ON;
GO
SET ANSI_NULL_DFLT_ON ON;
GO
CREATE TABLE Table2 (a TINYINT);
GO
sp_help Table2
GO
The screen shot below shows that the this column does allow NULLs.

So now that we have that covered let’s get down to the issue at hand. So based on how tables and columns are setup and how data is stored you may run into an issue where you have data stored in tables that have NULL values and you need to join on these values. Sounds pretty easy, but let’s see what happens when this occurs.

Setup Test

First let’s create two tables and load some sample data. (This is not a very practical example, but it does help illustrate the issue.)

CREATE TABLE [dbo].[CarModels](
[Make] varchar NULL,
[Model] varchar NULL,
[Trim] varchar NULL
) ON [PRIMARY]GO CREATE TABLE [dbo].[Orders](
[Make] varchar NULL,
[Model] varchar NULL,
[Trim] varchar NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.Orders VALUES (‘Nissan’,’Altima’,’2-door 2.5 S Coupe’)
INSERT INTO dbo.Orders VALUES (‘Nissan’,’Altima’,’4-door 3.5 SE Sedan’)
INSERT INTO dbo.Orders VALUES (‘Nissan’,’Altima’,”)
INSERT INTO dbo.Orders VALUES (‘Nissan’,’Altima’,NULL)
INSERT INTO dbo.CarModels VALUES (‘Nissan’,’Altima’,”)
INSERT INTO dbo.CarModels VALUES (‘Nissan’,’Altima’,’2-door 2.5 S Coupe’)
INSERT INTO dbo.CarModels VALUES (‘Nissan’,’Altima’,’2-door 3.5 SE Coupe’)
INSERT INTO dbo.CarModels VALUES (‘Nissan’,’Altima’,’4-door 2.5 S Sedan’)
INSERT INTO dbo.CarModels VALUES (‘Nissan’,’Altima’,’4-door 3.5 SE Sedan’)
INSERT INTO dbo.CarModels VALUES (‘Nissan’,’Altima’,’4-door 3.5 SL Sedan’)
INSERT INTO dbo.CarModels VALUES (‘Nissan’,’Altima’,’4-door HYBRID Sedan’)
INSERT INTO dbo.CarModels VALUES (‘Nissan’,’Altima’,NULL)

Selecting Data

The first thing we will do is show the data in these two tables. The idea here is that we want to join the tables based on Make, Model and Trim. For most of the records there is some value, but there are a few records where the Trim value is NULL.

SELECT *
FROM dbo.Orders a SELECT *
FROM dbo.CarModels b
query results
The first query does a straight join of these tables based on all three columns.

SELECT *
FROM dbo.Orders a
INNER JOIN dbo.CarModels b
ON a.Make = b.Make
AND a.Model = b.Model
AND a.Trim = b.Trim
The result of the above query matches only three of the four records from the Orders table. The records that have a NULL value for Trim do not show up in the resultset.

This next example introduces the ISNULL function in the join clause. The ISNULL function takes two parameters, the first is the value to interrogate to see if it is NULL and it if is NULL the second parameter specifies what the value should be converted to. So in this example if any value is NULL it will be converted to a space ”.

SELECT *
FROM dbo.Orders a
INNER JOIN dbo.CarModels b
ON a.Make = b.Make
AND a.Model = b.Model
AND isnull(a.Trim,”) = isnull(b.Trim,”)

In the results below we can see that we now have more rows, but one of the issues we are facing is that there are also values of space in the table and therefore we are picking up additional joins that we do not want. The rows that should not be included are highlighted below.

To take this a step further we are using the ISNULL function again, but this time we are converting the NULL values to ‘999999’. This is a value that we know does not exist in our table and therefore will not cause any unwanted joins.

SELECT *
FROM dbo.Orders a
INNER JOIN dbo.CarModels b
ON a.Make = b.Make
AND a.Model = b.Model
AND isnull(a.Trim,’999999′) = isnull(b.Trim,’999999′)
Here is our final result with the four rows we are expecting.

Joining Three or More Tables

Although each join specification joins only two tables, FROM clauses can contain multiple join specifications. This allows many tables to be joined for a single query.

The ProductVendor table of the AdventureWorks2008R2 database offers a good example of a situation in which joining more than two tables is helpful. The following Transact-SQL query finds the names of all products of a particular subcategory and the names of their vendors:Copy

SELECT p.Name, v.Name
FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing.Vendor v
ON pv.BusinessEntityID = v.BusinessEntityID
WHERE ProductSubcategoryID = 15
ORDER BY v.Name;
Menu