Using the correct, or preferable, not equal operator in MySQL

mysql sql standards-compliance inequality
Rob Van Dam picture Rob Van Dam · Jan 14, 2010 · Viewed 37k times · Source

Which of the two (semantically equivalent) ways is preferable to test for inequality?

  1. 'foo' != 'bar' (exclamation mark and equals sign)
  2. '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 obvious NULL cases).

Answer

jspcal picture jspcal · Jan 14, 2010

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

Related questions

Why does MySQL allow "group by" queries WITHOUT aggregate functions?

Surprise -- this is a perfectly valid query in MySQL: select X, Y from someTable group by X If you tried this query in Oracle or SQL Server, you’d get the natural error message: Column 'Y' is invalid in …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More