MySQL - Selecting rows with null columns

Question

How can I select any row that contains empty or null column?

I'm trying to run a check on my table, in which I want to see if any of my rows contain a column that doesn't hold a value..

example table: demo

+----+------+------+
| ID | col1 | col2 |
+----+------+------+
| 1  | VAL1 | Val2 |
| 2  | NULL | Val2 |
| 3  | VAL1 | NULL |
+----+------+------+

I want the query to return rows 2-3 , noting that I have many columns in actual table so I don't want to include it in the query with 'where or'.

can it be done with mysql?

do you want to avoid the first record and fetch other records? — Muhammad Raheel, Jan 01 '13 at 16:47
i want to fetch all rows that contain a null column, even if i dont know the columns name :) — Zalaboza, Jan 01 '13 at 19:53

Ravi · Answer 1 · 2015-06-11T02:58:50.227

9

select * from tablename where col1 is NULL or col2 is NULL

Result

 ID | col1  | col2
 ------------------     
 2  | NULL  | Val2     
 3  | VAL1  | NULL

edited Jun 11 '15 at 02:58

answered Jan 01 '13 at 16:48

Ravi

30,829
42
119
173

fthiella · Answer 2 · 2016-04-09T14:50:38.717

5

If you really want not to use ORs, you could use this:

SELECT *
FROM demo
WHERE concat(col1, col2, ...) is Null

or this:

SELECT *
FROM demo
WHERE col1+col2+... is null

but while it's makes the query easier to write, it won't make it faster. I would suggest you just to use ORs.

edited Apr 09 '16 at 14:50

answered Jan 01 '13 at 17:06

fthiella

48,073
15
90
106

This will return NULL if there is one column that is NULL. If you want to check if all columns in your list are empty use `CONCAT_WS("", col1, col2, ...)` – tomvo Jul 26 '22 at 07:32

score 5 · Accepted Answer · answered Apr 08 '16 at 18:03

the best answer that does not need to hard-code the column names is:

DECLARE @sqlStr VARCHAR(max) = (
        SELECT stuff((
                    SELECT 'and ' + c.NAME + ' is null '
                    FROM sys.columns c
                    WHERE object_name(object_id) = 'yourtablename'
                    ORDER BY c.NAME
                    FOR XML PATH('')
                    ), 1, 3, '')
        )

SET @sqlStr = 'select * from ' + yourtablename + ' where ' + @sqlStr

PRINT @sqlStr

EXEC (@sqlStr)

score -2 · Answer 4 · answered Jan 01 '13 at 16:50

-2

SELECT 
    IFNULL(col1,0),
    IFNULL(col2,0)
FROM mytable
WHERE col1 IS NULL or col2 IS NULL

You can use IFNULL to set default value

answered Jan 01 '13 at 16:50

Muhammad Raheel

19,823
7
67
103

i believe, this is used, when you want to fill the null column to zero :/ – Ravi Jan 01 '13 at 16:53
but, OP just need to show the related information, whose column holds NULL value, nevertheless, good effort. :) – Ravi Jan 01 '13 at 16:55

MySQL - Selecting rows with null columns

4 Answers4