Finding Null values in multiple columns in SQL

Question

What are the best and the most efficient way of finding null values in multiple columns. For example:

Name     Location    Age      Address
Mike       CLT        19       Null
Null        NY        28        Null

and so on... I just need to find out if there is any NULL value in any of these columns.

What do you want as the desired result? – Gordon Linoff Apr 13 '15 at 12:24 — Gordon Linoff, Apr 13 '15 at 12:24

score 2 · Answer 1 · answered Apr 13 '15 at 12:21

2

Check this query. Hope this gives you desired result.

Select * from YourTableName
where Name is null 
or location is null 
or age is null 
or address is null

answered Apr 13 '15 at 12:21

Paresh J

2,401
3
24
31

score 0 · Answer 2 · answered Apr 13 '15 at 12:15

0

Try using IS NULL in where clause:

SELECT *
FROM mytable
WHERE name IS NULL OR address IS NULL

answered Apr 13 '15 at 12:15

SMA

36,381
8
49
73

score 0 · Answer 3 · answered Apr 13 '15 at 13:10

0

Try this:

select *
from YourTable yt
where yt.Name+yt.Location+CONVERT(varchar(20),yt.Age)+yt.Address is null

Remember to convert all column to the same datatype.

answered Apr 13 '15 at 13:10

Gabor Rajczi

471
2
9

score 0 · Accepted Answer · answered Apr 13 '15 at 14:07

if you want to know if there are nulls in any column, this could be a good trick to generate an XML file from the rows containing those nulls; it should work for almost any table, just replace 'yourtable' with the name of the relevant table:

SELECT 
CAST (
(SELECT * FROM yourtable FOR XML path('x'),ELEMENTS XSINIL) 
AS XML)
.query('//.[@xsi:nil="true"]/..')

Finding Null values in multiple columns in SQL

4 Answers4