mysql SELECT WHERE only specific columns have values

Question

Good day

I have a table with a dozen columns and i'm looking for a query that will return rows that only have specific columns filled up. So it shouldn't return rows where 'illegal' columns are filled up.

Something like:

SELECT * FROM table WHERE col1 is NOT NULL 
AND all_other_cols is NULL;

^ i know it can be done by listing every other column and indicating "not NULL", but is there another way of doing this?

Hopefully this makes sense

UPDATE:

Based on the answers below I'll just use "DESCRIBE my_table;" then build a query that will filter all unwanted columns with 'is NULL'

Checking all columns is the cleanest approach and might be the best in terms of optimization. If you want something simpler, have a look at COALESCE. — Tobias
– Tobias, Commented Jun 16, 2017 at 18:27

arkascha · Accepted Answer · 2017-06-16 18:30:48Z

2

There is no direct feature for that. But what you can do is this:

You concatenate all table columns to a single string and then test that string against the column that is not meant to be NULL. These two values can only be identical if all other columns are empty or hold NULL.

The answer to this question might help with how to even simplify that:

MySQL concatenating all columns

edited Jun 16, 2017 at 18:30

answered Jun 16, 2017 at 18:24

arkascha

43.2k8 gold badges61 silver badges97 bronze badges

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

3 Comments

Robert Sinclair Over a year ago

thank you i thought of that as well, so there's something like CONCAT_WS but for column names?

arkascha Over a year ago

Take a look at the question I referenced.

Robert Sinclair Over a year ago

I think i'll just end up using DESCRIBE my_table; then build a query with 'unwanted col is NULL', seems to be the simplest, thanks for confirming that there's no direct feature for this

mrbitzilla · Accepted Answer · 2017-06-16 18:25:57Z

1

I think your approach is the simplest one

answered Jun 16, 2017 at 18:25

mrbitzilla

4297 silver badges14 bronze badges

1 Comment

arkascha Over a year ago

This is not an answer to the question. This should have been a comment.

J. Lavoie · Accepted Answer · 2017-06-16 18:26:22Z

1

You could use ISNULL condition : check out here

answered Jun 16, 2017 at 18:26

J. Lavoie

3343 gold badges5 silver badges24 bronze badges

2 Comments

arkascha Over a year ago

The OP wrote that he knows about that, but that it is not what he is looking for...

J. Lavoie Over a year ago

True, but the link I gave is about optimizing isnull if he decides to use it. I should have written my answer in another way

Collectives™ on Stack Overflow

mysql SELECT WHERE only specific columns have values

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related