SQL Tutorial - Joining on NULLs

Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis.

If you like the video you can support me on Patreon, https://www.patreon.com/beardeddev

In this SQL tutorial we take a look how to join NULLable columns, sometimes we need to join tables that do not have a PRIMARY KEY, FOREIGN KEY relationship. What happens if we join on VARCHAR columns, what happens if those columns contain NULL.

In SQL, matching NULL to NULL equals unknown so how can we return the correct results. Apply manipulation to the column such as wrapping in an ISNULL function can prevent efficient use of indexes and in the tutorial I discuss how to create efficient indexes and how to return results based on when both columns are NULL.

Lastly I move on to combining set operators with joins, this is something that is quite exciting to me and I will be using a lot in the future.

Overall, in the tutorial, I demonstrate two methods for joining NULLable columns.

If you would like to follow along with this SQL tutorial then you can use the below SQL to create the necessary objects.

Please feel free to post any comments.

DROP TABLE IF EXISTS dbo.Employees;

CREATE TABLE dbo.Employees
(
EmpId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
MiddleName VARCHAR(50) NULL,
LastName VARCHAR(50) NOT NULL,
Position VARCHAR(50) NOT NULL
);

DROP TABLE IF EXISTS dbo.Customers;

CREATE TABLE dbo.Customers
(
CustId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
MiddleName VARCHAR(50) NULL,
LastName VARCHAR(50) NOT NULL,
NumOrders TINYINT NOT NULL
);

INSERT INTO dbo.Employees (FirstName, MiddleName, LastName, Position)
VALUES
('Deidre', 'Elizabeth', 'Walsh', 'Sales Assistant'),
('Matthew', NULL, 'Arlington', 'Sales Assistant'),
('Michelle', NULL, 'Montgomery', 'Sales Assistant'),
('Lee', NULL, 'Chen', 'Sales Assistant');

INSERT INTO dbo.Customers (FirstName, MiddleName, LastName, NumOrders)
VALUES
('Deidre', 'Elizabeth', 'Walsh', 25),
('Raphael', NULL, 'Jones', 13),
('Lee', NULL, 'Chen', 17);

CREATE NONCLUSTERED INDEX nc_ix_emp_firstname_middlename_lastname ON dbo.Employees (FirstName, MiddleName, LastName);

CREATE NONCLUSTERED INDEX nc_ix_cust_firstname_middlename_lastname ON dbo.Customers (FirstName, MiddleName, LastName) INCLUDE (NumOrders);

SELECT
E.FirstName,
E.MiddleName,
E.LastName,
C.NumOrders
FROM dbo.Employees AS E
INNER JOIN dbo.Customers AS C
ON EXISTS (SELECT E.FirstName, E.MiddleName, E.LastName INTERSECT SELECT C.FirstName, C.MiddleName, C.LastName);