Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

CREATE TABLE IF NOT EXISTS `test` (
  `a_very_long_fieldname` float(10,6) DEFAULT NULL
);

INSERT INTO `test` (`a_very_long_fieldname`) VALUES
(0.000000),
(10000.000000),
(NULL);

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

SELECT *
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
1  
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
1  
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
1  
@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
2  
@rubo77, everyone is not annoyed that NULL exists. Some of us revel in it. –  Alain Collins Nov 6 '12 at 14:56
1  
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
show 7 more comments

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.

1 Answer

[ 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:

SELECT *
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
add comment

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