Is using the not equal <> sign in a SQL statement poor programming practice?
I have three values in a table column type. The values can be 0, 1, or 2. Which SQL statement is better?
1) WHERE type <> 2
2) WHERE type in (0,1)
-
3better the first for me...Mattia Caputo– Mattia Caputo2015-10-09 12:16:31 +00:00Commented Oct 9, 2015 at 12:16
-
type can not be null.Mukhtiar Ahmed– Mukhtiar Ahmed2015-10-09 12:34:21 +00:00Commented Oct 9, 2015 at 12:34
-
Based on indexing strategy the 2nd one is better since the list of values not equal to 2 is far greater than searching for 2 values in the indexAbercrombieande– Abercrombieande2015-10-09 13:03:09 +00:00Commented Oct 9, 2015 at 13:03
Add a comment
|
1 Answer
Neither is better than the other. Once you introduce a new type you might have to change one statement or the other.
An example: Let's say the type means "who pays".
- 0 = supplier pays
- 1 = client pays
- 2 = our selling department pays
So our query means "we don't pay". When introducing
- 3 = our buying department pays
then you'd have to change
WHERE type <> 2
to
WHERE type NOT IN (2,3)
When introducing
- 3 = other dealer pays
then you'd have to change the other query from
WHERE type IN (0,1)
to
WHERE type IN (0,1,3)
If you want to make it super-correct, you have a lookup table:
type meaning wepay 0 supplier pays false 1 client pays false 2 our selling department pays true
And the query:
WHERE type NOT IN (select type from types where wepay = true)
or
WHERE type IN (select type from types where wepay = false)
Again, it doesn't matter which of the two options to choose.
3 Comments
Pepo_rasta
does extra data in sql worth avoiding altering code? also, isnt
... IN ( SELECT... significantly slower? just asking
Thorsten Kettner
Generally additional data should never mean you have to change your queries. (Well, you cannot always avoid it, but this still serves as a good rule.) And no, a subquery on a small lookup table shouldn't have any impact on execution speed worth mentioning.