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:
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.
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.
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.
