What is this operator <=> in MySQL?

Question

I'm working on code written by a previous developer and in a query it says,

WHERE p.name <=> NULL

What does <=> mean in this query? Is it something equal to =? Or is it a syntax error?

But it is not showing any errors or exceptions. I already know that <> = != in MySQL.

@Mhd.Tahawi, We [need](http://stackoverflow.com/search?q=%3C%3D%3E) a google that allows us to google for operators directly. — Pacerier, Feb 14 '15 at 16:22
@Pacerier - i found this, some 5ish years ago http://symbolhound.com (dunno if there's a better alternative) — Andrew, Jun 11 '18 at 21:13

score 277 · Accepted Answer · edited Jun 20 '20 at 09:12

277

TL;DR

It's the NULL safe equal operator.

Like the regular = operator, two values are compared and the result is either 0 (not equal) or 1 (equal); in other words: 'a' <=> 'b' yields 0 and 'a' <=> 'a' yields 1.

Unlike the regular = operator, values of NULL don't have a special meaning and so it never yields NULL as a possible outcome; so: 'a' <=> NULL yields 0 and NULL <=> NULL yields 1.

Usefulness

This can come in useful when both operands may contain NULL and you need a consistent comparison result between two columns.

Another use-case is with prepared statements, for example:

... WHERE col_a <=> ? ...

Here, the placeholder can be either a scalar value or NULL without having to change anything about the query.

Related operators

Besides <=> there are also two other operators that can be used to compare against NULL, namely IS NULL and IS NOT NULL; they're part of the ANSI standard and therefore supported on other databases, unlike <=>, which is MySQL-specific.

You can think of them as specialisations of MySQL's <=>:

'a' IS NULL     ==> 'a' <=> NULL
'a' IS NOT NULL ==> NOT('a' <=> NULL)

Based on this, your particular query (fragment) can be converted to the more portable:

WHERE p.name IS NULL

Support

The SQL:2003 standard introduced a predicate for this, which works exactly like MySQL's <=> operator, in the following form:

IS [NOT] DISTINCT FROM

The following is universally supported, but is relative complex:

CASE WHEN (a = b) or (a IS NULL AND b IS NULL)
     THEN 1
     ELSE 0
END = 1

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 21 '14 at 07:56

Ja͢ck

170,779
38
263
309

So then what is the use of `IS NULL` and `IS NOT NULL` ?? – zzlalani Feb 21 '14 at 08:06
2

so it means there is no difference? and this `<=>` is somewhat a useless operator? right? – zzlalani Feb 21 '14 at 08:18
16

@zzlalani Not at all; `<=>` takes two operands whereas `IS (NOT) NULL` only takes one; **big** difference ... it's as useful as `=` itself in that respect. – Ja͢ck Feb 21 '14 at 08:20
12

@zzlalani `IS NULL` and `IS NOT NULL` are in the SQL standard. `<=>` is a MySQL specific extension. – Daniel Dinnyes Feb 21 '14 at 11:10
5

So, like the `is not distinct from` operator. Curious to know if MySQL can use an index on that... – Denis de Bernardy Feb 21 '14 at 20:45
@Denis it should, because it's just like doing normal equality with an OR condition. See also [this question](http://stackoverflow.com/questions/5956408/is-there-a-performance-difference-between-and) – Ja͢ck Feb 21 '14 at 21:44
1

@Ja͢ck, Is there a `>=<` operator for the inverse of `<=>` ? – Pacerier Feb 15 '15 at 13:55
4

@Pacerier No, the inverse of `a <=> b` is `NOT(a <=> b)`. – Ja͢ck Feb 16 '15 at 03:56
4

@zzlalani, The spaceship operator is **actually very useful**. It's as useful as `=` in "normal" programming languages and maths. `a = b` is either `true` or `false`, except in SQL land they managed to brainwash the [population there](http://goo.gl/mzihcp) that it can also be `null`, and to everyone else it just doesn't make any sense. Indeed, the whole land of Java, C#, Javascript, PHP, etc, would revolt if `null == null` give you something besides `true`. Even something [as wrong as Javascript](http://goo.gl/rM6e0f) knows that `undefined == undefined` = `true`. – Pacerier Apr 16 '15 at 11:21

score 64 · Answer 2 · edited Feb 26 '14 at 05:01

64

is <=> NULL-safe equal to operator

This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

See here for the documentation

Sample :

you should use IS NOT NULL. (The comparison operators = and <> both give UNKNOWN with NULL on either side of the expression.)

SELECT * 
FROM table 
WHERE YourColumn IS NOT NULL;

can also negate the null safe equality operator but this is not standard SQL.

SELECT *
FROM table 
WHERE NOT (YourColumn <=> NULL);

edited Feb 26 '14 at 05:01

Vignesh Kumar A

27,863
13
63
115

answered Feb 21 '14 at 06:41

Drixson Oseña

3,631
3
23
36

2

Correction: `<=>` should be called the [equals operator](http://stackoverflow.com/questions/21927117/what-is-this-operator-in-mysql#comment47483870_21928508) and `=` is the `NULL` unsafe equals operator. – Pacerier Apr 16 '15 at 11:28

Rahul Tripathi · Answer 3 · 2014-02-26T04:25:35.467

It is the NULL-safe equal to operator

<=> Operator is used to compare NULL values with the fields. If normal =(equals) Operators return NULL if one of the comparison value is NULL. With <=> operator returns true or false. <=> Operator is same as IS NULL.

From the manual:-

<=> performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

Edit:-(Although very late to add one important side note mentioning NOT <=> as well)

On a side note:-

NOT <=>

There is one more point NOT <=> which is used to compare NULL values with the fields. If normal != or <> (not equals) Operators return NULL if one of the comparison value is NULL. With NOT applied to <=> operator returns true or false. NOT applied to <=> Operator is same as IS NOT NULL.

Example:-

SELECT NULL != NULL,         //--Result is NULL
   NOT NULL <=> NULL,        //--Result is 0
   NULL IS NOT NULL;         //--Result is 0

`NOT <=>` is not an operator, it's `NOT` applied to the result of `op1 <=> op2`. — Ja͢ck, Feb 26 '14 at 04:25
@Jack:- Yes you are correct. Updated the answer! Added that so as to make a point :) — Rahul Tripathi, Feb 26 '14 at 04:27

score 20 · Answer 4 · answered Feb 21 '14 at 06:43

<=> is MySQL's null-safe "equal to" operator. From the manual:

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

Salman A · Answer 5 · 2022-01-20T16:31:50.500

15

<=> is the NULL-safe equal operator. It is equivalent to the standard SQL is not distinct from operator. Its behavior is best explained via truth table:

a	b	a <=> b
1	0	false
1	1	true
1	null	false*
null	null	true*

* Notice that this operator will never return unknown (represented by null) unlike other operators.

edited Jan 20 '22 at 16:31

answered Feb 22 '14 at 09:01

Salman A

262,204
82
430
521

Dimag Kharab · Answer 6 · 2014-02-25T07:09:32.393

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

It significance:

When you compare a NULL value with a non-NULL value, you'll get NULL. If you want to check if a value is null.

The Equality operator(<=>) which considers NULL as a normal value, so it returns 1 (not NULL) if both values are NULL and returns 0 (not NULL) if one of the values is NULL:

eg

 SELECT NULL <=> NULL -- 1
 SELECT TRUE <=> TRUE -- 1
 SELECT col1 <=> col2 FROM myTable

Jojodmo · Answer 7 · 2014-02-26T15:24:25.163

From the MySQL documentation:

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

An example using the <=> operator would be:

SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;

Which would return:

1, 1, 0

An example of the regular = operator would be:

SELECT 1 = 1, NULL = NULL, 1 = NULL;

Which would return:

1, NULL, NULL

The <=> operator is very similar to the = operator, except <=> will never return NULL

score 5 · Answer 8 · answered Feb 21 '14 at 06:42

5

It is the NULL - Safe Equal to operator. Check description.

answered Feb 21 '14 at 06:42

MusicLovingIndianGirl

5,909
9
34
65

score 1 · Answer 9 · answered Feb 27 '16 at 10:44

mysql> SELECT * FROM t JOIN t2 WHERE t2.ids = t.ids;
+----+------+----+------+
| id | ids  | id | ids  |
+----+------+----+------+
|  1 |    1 |  1 |    1 |
|  2 |    2 |  2 |    2 |
|  5 |    6 |  5 |    6 |
|  6 |    7 |  6 |    7 |
+----+------+----+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM t JOIN t2 WHERE t2.ids <=> t.ids;
+----+------+----+------+
| id | ids  | id | ids  |
+----+------+----+------+
|  1 |    1 |  1 |    1 |
|  2 |    2 |  2 |    2 |
|  3 | NULL |  3 | NULL |
|  4 | NULL |  3 | NULL |
|  3 | NULL |  4 | NULL |
|  4 | NULL |  4 | NULL |
|  5 |    6 |  5 |    6 |
|  6 |    7 |  6 |    7 |

light · Answer 10 · 2022-03-18T07:26:02.450

0

The difference between operand1=operand2 and operand1<=>operand2 is that the prior will return null while any of the two operands are null. But the latter return 1 or 0 depends on the operands' value even if null.

As a contrast:

select null = null, return null ; but select null <=> null,return 1
select null = 'abc',return null; but select null <=> 'abc',return 0

edited Mar 18 '22 at 07:26

answered Oct 28 '21 at 13:52

light

113
4

False. It does not always return true if col1 is null! try `SELECT null <=> 'a'` – the_nuts Mar 15 '22 at 16:04
I have corrected it, thanks for your pointing out the problem. – light Mar 17 '22 at 14:50

What is this operator <=> in MySQL?

10 Answers10

TL;DR

Usefulness

Related operators

Support

NOT <=>

Linked

Related