Find NULL in any column

-- Database by Doug

-- Douglas Kline

-- May 23 2024

-- Find records that have a NULL in any field of the record

-- in other words, return all records that contain a NULL in any field

-- review:

-- NULL is an odd beast - it means "an unknown value"

-- unknown values are not equal to anything

-- unknown values are not "not equal" to anything

-- think of NULL as a system defined value that cannot be compared to anything

SELECT 1

WHERE '' = NULL

OR '' > NULL

OR '' < NULL

OR 0 = NULL

OR 0 < NULL

OR 0 > NULL

OR NULL = NULL

OR NULL <> NULL

-- let's try the special unary comparison operator, IS NULL

SELECT 1

WHERE (NULL) IS NULL

-- let's use the class Northwind Database

USE Northwind;

-- and see what's in the products table

SELECT *

FROM Products

-- notice that I've put a couple of records in so we have some

-- records with NULL values

SELECT *

FROM Products

WHERE ProductID > 77

SELECT *

FROM Products

WHERE CategoryID IS NULL

OR QuantityPerUnit IS NULL

-- results show Doug's Spicy Mustard, Doug's Bold Ketchup

-- but what if I wanted to test for a NULL in ANY column?

-- can I do this?

SELECT *

FROM Products

WHERE * IS NULL

-- kinda makes sense, but * can only be used in SELECT

-- sounds like maybe you could use the ANY operator modifier,

-- but unfortunately, ANY requires a *comparison*, e.g., =, <, >, <>, etc.

-- and NULL comparisons always return FALSE

-- and ANY operates across rows, on a single column

-- not across columns on a single row... :-(

-- here is how it would look for the Products table

SELECT *

FROM Products

WHERE ProductID IS NULL

OR ProductName IS NULL

OR SupplierID IS NULL

OR CategoryID IS NULL

OR QuantityPerUnit IS NULL

OR UnitPrice IS NULL

OR UnitsInStock IS NULL

OR UnitsOnOrder IS NULL

OR ReorderLevel IS NULL

OR Discontinued IS NULL

-- but this would be a bit tedious with hundreds of columns

-- but if my requirement is a straightforward static SQL statement,

-- I just need to find a good / fast way to build it

-- I can find a list of all columns from sys.columns

SELECT name, is_nullable

FROM sys.columns

WHERE object_id = OBJECT_ID('Products')

AND is_nullable = 1

-- this would be helpful in writing my WHERE clause with lots of ORs

-- adding some string concatenation

SELECT name

+ ' IS NULL'

FROM sys.columns

WHERE object_id = OBJECT_ID('Products')

AND is_nullable = 1

-- now let's put it all in one string using string_agg

SELECT STRING_AGG(

name

+ ' IS NULL',

' OR '

)

FROM sys.columns

WHERE object_id = OBJECT_ID('Products')

AND is_nullable = 1

-- and to be complete, let's add the rest of the SELECT

SELECT 'SELECT * FROM Products WHERE '

+ STRING_AGG(

name

+ ' IS NULL',

' OR '

)

FROM sys.columns

WHERE object_id = OBJECT_ID('Products')

AND is_nullable = 1

-- now copy and paste, and reformat

SELECT * FROM Products WHERE SupplierID IS NULL OR CategoryID IS NULL OR QuantityPerUnit IS NULL OR UnitPrice IS NULL OR UnitsInStock IS NULL OR UnitsOnOrder IS NULL OR ReorderLevel IS NULL

SELECT * FROM Products WHERE SupplierID IS NULL OR CategoryID IS NULL OR QuantityPerUnit IS NULL OR UnitPrice IS NULL OR UnitsInStock IS NULL OR UnitsOnOrder IS NULL OR ReorderLevel IS NULL

SELECT *

FROM Products

WHERE SupplierID IS NULL

OR CategoryID IS NULL

OR QuantityPerUnit IS NULL

OR UnitPrice IS NULL

OR UnitsInStock IS NULL

OR UnitsOnOrder IS NULL

OR ReorderLevel IS NULL

-- Summary

-- you need to find records that have a NULL in any column

-- you have lots of columns

-- you have to write a static SQL statement

-- use some SQL to write your statement

-- drawing the columns from sys.columns

-- Database by Doug

-- Douglas Kline

-- May 23 2024

-- Find records that have a NULL in any field of the record