What does `<>` mean

operatorsql-standard

What does <> do in the following WHERE clause,

WHERE posn_id <> rid

Best Answer

 WHERE posn_id <> rid

Will return all rows where both posn_id and rid are not NULL and where they are different.

It's the ANSI SQL-Compliant not equals operator in a simple comparison predicate (WHERE statement). Colloquially, it's the "inequality operator". Though many databases accept an alternative !=, the spec itself does not mention != and it should not be used if <> is supported.

SQL uses three valued logic, with possible values being true, false or unknown. The WHERE clause filters out all rows except those where the predicate evaluates to true.

  • If either or both sides are null, the operator returns unknown.
  • On inequality, where both sides are not =, the operator returns true.
  • On equality, <> returns false.

On null treatment, a similar operator is IS DISTINCT FROM which treats nulls as ordinary values, from the PostgreSQL docs

For non-null inputs, IS DISTINCT FROM is the same as the <> operator. However, if both inputs are null it returns false, and if only one input is null it returns true.

For RDBMS specific documentation on comparison operators, see also

Related Question