MS SQL Server - search for NULL values in multiple columns

In this article, we would like to show you how to find rows with NULL values in many columns in MS SQL Server.

Quick solution:

SELECT *
FROM [table_name] 
WHERE [column1] IS NULL AND [column2] IS NULL AND [columnN] IS NULL;

Practical example

To show how to find rows with NULL values in many columns, we will use the following table:

Note:

At the end of this article you can find database preparation SQL queries.

Example 1

In this example, we will select all rows with NULL value both in email and department_id column using IS NULL and AND keywords.

Query:

SELECT *
FROM [users]
WHERE [email] IS NULL AND [department_id] IS NULL;

Result:

Database preparation

create_tables.sql file:

CREATE TABLE [users] (
	[id] INT IDENTITY(1,1),
	[name] VARCHAR(50) NOT NULL,
	[surname] VARCHAR(50) NOT NULL,
	[email] VARCHAR(100),
	[department_id] INT,
    [salary] DECIMAL(15,2) NOT NULL,
	PRIMARY KEY ([id])
);

insert_data.sql file:

INSERT INTO [users]
	( [name], [surname], [email], [department_id], [salary])
VALUES
	('John', 'Stewart', 'john@email.com', 1, '3512.00'),
	('Chris', 'Brown', 'chris@email.com', 2, '1344.00'),
	('Kate', 'Lewis', NULL, 3, '6574.00'),
	('Ailisa', 'Gomez', 'ailisa@email.com', 2, '6500.00'),
	('Gwendolyn', 'James', NULL, NULL, '4200.00'),
	('Simon', 'Collins', NULL, 4, '3320.00'),
	('Taylor', 'Martin', NULL, NULL, '1500.00'),
	('Andrew', 'Thompson', 'andrew@email.com', NULL, '2100.00');

Related posts

• MS SQL Server - find row with null value in one of many columns