Where I work we have a generic database table which stores a variety of values associated with different things. Each entry has a type, id and description, along with a set of generic alpha_1..9 and numeric_1..9 columns.
Today I needed to check whether a certain value was in the table but wasn't certain where it might be - all I knew was that it was text, so I started writing the following SQL:
SELECT *
FROM code
WHERE type = 'VALUE'
OR id = 'VALUE'
OR alpha_1 = 'VALUE'
OR alpha_2 = 'VALUE'
OR alpha_3 = 'VALUE'
...
And then I thought... why am I doing this? SQL is verbose enough without me making it worse, and there is a far simpler solution open to me:
SELECT *
FROM code
WHERE 'VALUE' IN (type, id, alpha_1, alpha_2, alpha_3 ...)
The result is the same, but I've avoided a lot of typing (or pasting).
This reminded me of a piece of photography advice: Whenever you're taking a picture, turn around; there may well be a better shot behind you.
And I think there is a useful idea here, worth bearing in mind for all programming: Don't blindly march forward, but stay aware of your surroundings, and always consider if the route you're taking is the best way to proceed.
Nobody has commented on this article yet.