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
– Muhammad Raheel, Commented Jan 1, 2013 at 16:47
i want to fetch all rows that contain a null column, even if i dont know the columns name :) — Momen Zalabany
– Momen Zalabany, Commented Jan 1, 2013 at 19:53

Ravi · Accepted Answer · 2015-06-11 02:58:50Z

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, 2015 at 2:58

answered Jan 1, 2013 at 16:48

Ravi

31.6k44 gold badges124 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

FLICKER · Accepted Answer · 2016-04-08 18:03:34Z

5

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)

answered Apr 8, 2016 at 18:03

FLICKER

6,7434 gold badges52 silver badges83 bronze badges

1 Comment

FLICKER Over a year ago

for next generation :)

fthiella · Accepted Answer · 2016-04-09 14:50:38Z

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 9, 2016 at 14:50

answered Jan 1, 2013 at 17:06

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

1 Comment

tomvo Over a year ago

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, ...)

Muhammad Raheel · Accepted Answer · 2013-01-01 16:50:10Z

-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 1, 2013 at 16:50

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

2 Comments

Ravi Over a year ago

i believe, this is used, when you want to fill the null column to zero :/

Ravi Over a year ago

but, OP just need to show the related information, whose column holds NULL value, nevertheless, good effort. :)

Collectives™ on Stack Overflow

MySQL - Selecting rows with null columns

4 Answers 4

Comments

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related