EN
MS SQL Server - find row with null value in one of many columns
0
points
In this article, we would like to show you how to find rows with NULL value in one of many columns in MS SQL Server.
Quick solution:
SELECT *
FROM [table_name]
WHERE [column1] IS NULL OR [column2] IS NULL OR [columnN] IS NULL;
Practical example
To show how to find rows with NULL value in one of many columns, we will use the following table:
Note:
At the end of this article you can find database preparation SQL queries.
In this example, we will select rows from users table with NULL value in email OR department_id column.
Query:
SELECT *
FROM [users]
WHERE [email] IS NULL OR [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');