Replacing NULLs in a Left Join–#SQLNewBlogger

I saw someone ask a question on how to replace NULL in a left join and decided to write a post. I realized this is one of those simple things that people new to SQL might not get.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Here are some hints to get started.

A Left Join Example

Let’s create a table of customers and orders with a few values in each. This is common, where we have customers that we might add as prospects in some CRM type system. Then we link orders to customers.

Use this code:

DROP TABLE IF EXISTS dbo.Customer
GO
CREATE TABLE dbo.Customer
( CustomerID INT NOT NULL IDENTITY(1,1) CONSTRAINT CustomerPK PRIMARY KEY
, CustomerName VARCHAR(20)
)
GO
INSERT dbo.Customer (CustomerName)
VALUES
   ('Joe'),
   ('Bob'),
   ('Sally'),
   ('Amy')
GO
DROP TABLE IF EXISTS dbo.OrderHeader
GO
CREATE TABLE dbo.OrderHeader
( OrderID INT NOT NULL IDENTITY(1,1) CONSTRAINT OrderHeaderPK PRIMARY KEY
, CustomerID INT
, OrderNote VARCHAR(100)
)
GO
INSERT dbo.OrderHeader (CustomerID, OrderNote)
VALUES
   (1, 'Initial Order'),
   (1, 'Re-order'),
   (3, 'Initial Order')
GO

Potentially, we have customers without orders. If we use an inner join, we only see customers with orders. Using the left join below, we see all customers with their corresponding orders.

SELECT
   c.CustomerID
, c.CustomerName
, oh.OrderID
, oh.OrderNote
FROM
   dbo.Customer AS c
   LEFT JOIN dbo.OrderHeader AS oh
     ON oh.CustomerID = c.CustomerID;
GO

I see these results:

2022-09-02 14_00_08-SQLQuery6.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (58))_ - Microsoft SQL Server

This works, but really, I’d like to clean up the results to show something better.

Looking for NULLs

I can use a couple of functions to look for a NULL value in my results. Both ISNULL and COALESCE can help here. ISNULL is for a single expression and replaces NULL with value, while COALESCE works by returning the first non-NULL expression. I’ll use ISNULL here and in another post look at COALESCE.

Here’s a better query that replaces one value with a NA and another with a blank.

SELECT
   c.CustomerID
, c.CustomerName
, ISNULL(oh.OrderID, 0) AS OrderID
, ISNULL(oh.OrderNote, 'No orders placed') AS OrderNote
FROM
   dbo.Customer AS c
   LEFT JOIN dbo.OrderHeader AS oh
     ON oh.CustomerID = c.CustomerID;
GO

Here are the results. Note that I return a 0 for the OrderID. This is because the result set is a numeric, and I need these types to match.

2022-09-02 14_04_37-SQLQuery6.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (58))_ - Microsoft SQL Server

I could also return a string if I cast all OrderIDs to strings, as shown below.

SELECT
   c.CustomerID
, c.CustomerName
, ISNULL(CAST(oh.OrderID AS VARCHAR(20)), 'N/A') AS OrderID
, ISNULL(oh.OrderNote, 'No orders placed') AS OrderNote
FROM
   dbo.Customer AS c
   LEFT JOIN dbo.OrderHeader AS oh
     ON oh.CustomerID = c.CustomerID;
GO

This produces these results.

2022-09-02 14_05_29-SQLQuery6.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (58))_ - Microsoft SQL Server

Both cases clean up the NULL values with something that makes more sense to a person looking at the data in a report.

SQLNewBlogger

This was a post inspired by a question I saw. This is how I’d solve the issue, and decided to share that knowledge more widely, both to help others and also provide an example of where I might have a hiring manager ask me about this from noticing my blog.

This post took about 15 minutes to write. You could easily do this on your blog.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.