Finding Unmatched Records in SQL

-- Database by Doug
-- Douglas Kline
-- 10/19/2018
--
-- Finding unmatched records
-- LEFT JOIN... WHERE IS NULL

-- suppose you want to find records in one table 
-- that do not match records in another table

-- some practical examples:

-- products that are not in a category
--     (in Products table, but not in Categories)

-- Categories that have no products
--     (in Categories table, but not in Products table)

-- two main cases,
-- first case: look for the NULL

-- products that are not in a category
SELECT   ProductID
FROM     Products
WHERE    CategoryID IS NULL

-- this is fairly simple, because
-- everything we need is in a single table
-- we don't need to inspect another table
-- this is when the Foreign Key is
-- in the table of interest

-- Categories that have no products
-- this is tougher
-- since which Categories to display
-- depends on what's in the Products table

-- here's the pattern, then we'll build up 
-- to why it works

SELECT         Categories.CategoryName
FROM           Categories
   LEFT JOIN   Products ON Categories.CategoryID = Products.CategoryID
WHERE          Products.ProductID IS NULL

-- the pattern is
-- <table of interest> LEFT JOIN <unmatched table&gt
-- WHERE <unmatched table&gt.<primary key&gt IS NULL

-- consider this, normal join
SELECT         Categories.CategoryName,
               Categories.CategoryID,
               Products.CategoryID,
               Products.ProductID,
               Products.ProductName
FROM           Categories
        JOIN   Products ON Categories.CategoryID = Products.CategoryID
ORDER BY       Categories.CategoryName

-- note 77 records
-- now the LEFT JOIN

SELECT         Categories.CategoryName,
               Categories.CategoryID,
               Products.CategoryID,
               Products.ProductID,
               Products.ProductName
FROM           Categories
   LEFT JOIN   Products ON Categories.CategoryID = Products.CategoryID
ORDER BY       Categories.CategoryName

-- now 78 records
-- and note the Office Supplies category with no Product
-- the record has been replaced with a *NULL record*

-- the LEFT JOIN includes Categories without matching Products
-- Categories LEFT JOIN Products -- Categories is on the LEFT

-- how do I know that it's really a *NULL record*
-- and not just NULLs in the fields? which might be ok
-- because Products.ProductID is NULL, which can't happen for a PK

-- so, let's limit to just that record
SELECT         Categories.CategoryName,
               Categories.CategoryID,
               Products.CategoryID,
               Products.ProductID,
               Products.ProductName
FROM           Categories
   LEFT JOIN   Products ON Categories.CategoryID = Products.CategoryID
WHERE          Products.ProductID IS NULL
ORDER BY       Categories.CategoryName

-- and paring it back to the simple form
SELECT         Categories.CategoryName
FROM           Categories
   LEFT JOIN   Products ON Categories.CategoryID = Products.CategoryID
WHERE          Products.ProductID IS NULL
ORDER BY       Categories.CategoryName


-- Database by Doug
-- Douglas Kline
-- 10/19/2018
--
-- Finding unmatched records
-- LEFT JOIN... WHERE IS NULL