0

What does <> do in the following WHERE clause,

WHERE posn_id <> rid
14
 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

  • 2
    @KumarHarsh seriously, what are you talking about? – ypercubeᵀᴹ Feb 2 '18 at 22:34
2

It's just another way of spelling the "not equals" operator, an alternative to !=

  • 5
    It's more accurate to say, "!= is another way of spelling <>" as <> is the only operator of the two defined by the ansi standard. – Joel Coehoorn Jan 29 '18 at 21:43
  • 1
    Not another way. In SQL is the way. != was added later to please C programmers and was not standard for years – edc65 Jan 29 '18 at 21:44
  • 3
    @edc65 It still isn't. – Joel Coehoorn Jan 29 '18 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.