Using the correct, or preferable, not equal operator in MySQL
Solution 1
<> should be preferred, all things being equal, since it accords with the sql standard and is technically more portable...
!= is non-standard, but most db's implement it.
sql:2008 grammar:
<not equals operator> ::=
<>
Solution 2
It's obvious.
The ! character is on the North West corner of US keyboards.
Microsoft headquarters are in the North West corner of the US.
So. <>
is a nod to Microsoft.
!=
is a rejection of Microsoft.
It's a secret political code.
Solution 3
<> is the only one in the SQL-92 standard.
Solution 4
They are the same, it is purely preference.
This should give you a good idea
!= (Not Equal To) Not equal to (not SQL-92 standard)
<> (Not Equal To) Not equal to
Solution 5
DBA's generally like <> and programmers like !=. Just an observation :-)
Rob Van Dam
Full time perl hacker. Open to contract work. Tweet @rvandam.
Updated on April 08, 2020Comments
-
Rob Van Dam about 4 years
Which of the two (semantically equivalent) ways is preferable to test for inequality?
'foo' != 'bar'
(exclamation mark and equals sign)'foo' <> 'bar'
(less than and greater than chevron symbols together)
The MySQL documentation clearly indicates that there is no difference between them and yet some people seem to be attached to only doing it one way or the other. Maybe this is just another pointless vi vs. emacs debate but when other people are reading your code (and therefore your queries), it's useful to maintain some consistency.
<>
looks a lot like<=>
which is a very underused operator but could perhaps lead to confusion at a quick glance since the two are nearly opposite (except for the obviousNULL
cases). -
Adriaan Stander over 14 yearsI would like to think that DBA's might be programmers too? X-)
-
Peter Lang over 14 yearsAgreed, contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt says
<not equals operator> ::= <>
-
Bill Karwin over 14 yearsSo if we negate the not-equals comparison, are we supporting Microsoft? Or are we rejecting our own disapproval? :)
-
DavidDraughn over 11 yearsOne example of a DB that doesn't support '!=' is Microsoft Access.
-
twiz over 11 yearsAlthough your answer obviously isn't wrong, for MySQL I get the impression
!=
is more widely used. This is probably because of it's close relation to PHP, and as JonH said, "programmer" prefer!=
. In other words, more developers likely understand!=
than<>
. My point being that if you want to adhere to standards, then<>
is preferable, but for readability,!=
may be the better option. Just a thought.