Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to type the least possible characters, to find out all columns in a table that are 0 or NULL

In this example I explain it further:

lets assume the table test

  `a_very_long_fieldname` float(10,6) DEFAULT NULL

INSERT INTO `test` (`a_very_long_fieldname`) VALUES

to find out all columns, that are 0.000000 or NULL this would give the desired result, but it is too long:

FROM `test`
WHERE `a_very_long_fieldname` =0 OR a_very_long_fieldname IS NULL

is there a way not to have to repeat the fieldname twice?

share|improve this question

closed as too localized by ypercubeᵀᴹ, Paul White, Mark Storey-Smith, JNK Nov 6 '12 at 13:19

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

Voting to close as too localized. This question and possible answer are not helpful to anyone, the OP included. – ypercubeᵀᴹ Nov 6 '12 at 9:45
I don't see a point in this, but you can use COALESCE() or IFNULL() function, if you want to avoid writing the column name twice: WHERE COALESCE(column, 0) = 0. – ypercubeᵀᴹ Nov 6 '12 at 12:47
@denzo, you don't want to use functions on columns in your WHERE clause, because then indexes can't be used. – Alain Collins Nov 6 '12 at 14:54
@rubo77, everyone is not annoyed that NULL exists. Some of us revel in it. – Alain Collins Nov 6 '12 at 14:56
As I pointed out earlier, that's not a good solution, since you won't be able to use an index on your field. But maybe how much you type is more important than performance? – Alain Collins Nov 6 '12 at 17:38

[ NOTE: the thought police edited the sarcasm out of this reply. For future reference, please don't do any of the things listed below - except the last, which is true. ]

Name your tables and columns with single letters, which will save you a ton of keystrokes in writing the query.

Or store 0 instead of NULL (I'm sure your users won't know the difference).

Rhetorical question: unless you're paying by the byte, why are you in the least bit concerned that your query is too long?

Oh, I would suggest making it slightly longer:

FROM `test`
WHERE (`a` =0 OR a IS NULL)

because someday, you'll add an AND clause and you'll wonder why your original version doesn't work anymore.

share|improve this answer
Why the irony. I have to administer databases, that have NULL allowed everywhere and so I have to write each time ... OR value IS NULL (everywhere in the code) thats annoying, so please find a faster solution – rubo77 Nov 6 '12 at 12:33
It's not irony, it's sarcasm, but @DTest edited it out anyway. – Alain Collins Nov 6 '12 at 14:55
I still think this is a good question. The solution is in the comment of ypercube. – rubo77 Nov 6 '12 at 17:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.