Selecting rows with specific columns having a null or 'default' value

Question

I've got a rather large DB and I need to select the fields where they are null or defaulted.

For example:

Table: Table_A
Row #1234
    name,  mname, lname
    [data] [data] [null]

I'd need a query to generalize this, so that I can select "lname", where it is NULL, or mname, or none based on its state.

--

I just need to SELECT all "NULL" or "FALSE" fields in a row, and return only those fields which are NULL or FALSE.

come again? there is no such thing as a null row. You need to explain what you are trying to do a lot better. — Mike Nakis
– Mike Nakis, Commented Dec 29, 2011 at 22:26
So, what do you expect this query to retrieve if in row #1234 lname is NULL, but in row #1235 name is NULL? — Mike Nakis
– Mike Nakis, Commented Dec 29, 2011 at 22:40
To return 'name' only. If lname is NULL, and mname is NULL, then it would return both. — Brooklyn Nicholson
– Brooklyn Nicholson, Commented Dec 29, 2011 at 22:42

piotrekkr · Accepted Answer · 2011-12-29 22:26:49Z

1

Try

SELECT * FROM table WHERE col1 IS NULL or col2 IS NULL or ... or col1 = 'default' or col2 = 'default' ...

answered Dec 29, 2011 at 22:26

piotrekkr

3,3464 gold badges30 silver badges40 bronze badges

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

4 Comments

Mike Purcell Over a year ago

OP mentioned that this is a large DB, and although ORs would work, they would result in a painful query.

piotrekkr Over a year ago

sorry if I read large db i think database with big amount of data / rows not big number of columns :)

Brooklyn Nicholson Over a year ago

The data spans over multiple tables; I can JOIN them and do this. Would that eat my performance?

Mike Purcell Over a year ago

Large could relate to width of table or number of rows, as far as optimization it doesn't really matter, the ORs have the potential to cause un-needed load on the database.

Collectives™ on Stack Overflow

Selecting rows with specific columns having a null or 'default' value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related