CODEWITHSUNDEEP
mysqlsqloptimizationindexing
Siddharth Bulia
Siddharth Bulia

Reputation: 23

What is difference between `flag is TRUE` vs `flag = TRUE` in MySQL?

I ran the following queries in MySQL -

SELECT * from table
WHERE valid is TRUE
ORDER BY priority DESC 
limit 10 
offset 0;

Time taken = 1 second.

vs

SELECT * from table
WHERE valid = TRUE 
ORDER BY priority DESC 
limit 10 
offset 0;

Time taken = 66 ms.

I have indexes on (valid, priority) and (valid). Why is there such a huge difference? What is the difference between Is TRUE vs = TRUE ?

Upvotes: 2

Views: 1698

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

There is a very important difference:

  • IS TRUE only trues "true" or "false"
  • = TRUE can return NULL.

In particular NULL IS TRUE returns "false".

Actually, this is not so important for IS TRUE. It is a substantial difference for IS NOT TRUE versus NOT or <> true.

That is IS TRUE and IS NOT TRUE is "NULL-safe":

where NULL IS NOT TRUE  --> evaluates to true and all rows are returned
where NOT NULL          --> evaluates to NULL and no rows are returned
where NULL <> TRUE      --> evaluates to NULL and no rows are returned

The NULL here could be an expression that returns a NULL values.

These semantics are clearly explained in the documentation.

Upvotes: 3

Pavan Patil
Pavan Patil

Reputation: 677

As per the Mysql Doc for IS operator

IS boolean_value

Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.

In SQL, a boolean_value – either TRUE , FALSE or UNKNOWN – is a truth value. When using IS operator, the value you are testing against must be expressed/cast as one of these truth values, and then the expression is evaluated.

In your first query:

SELECT * from table WHERE valid is TRUE ORDER BY priority DESC limit 10 offset 0;

depending on the datatype of the valid column, the truth value is evaluated for each row which would result in a full table scan, hence you would see higher times.

In your second query:

SELECT * from table WHERE valid = TRUE ORDER BY priority DESC limit 10 offset 0;

when you use = operator, you are comparing the valid column to Boolean Literal TRUE, which is just a MySQL constant for 1.

Upvotes: 3

Sid
Sid

Reputation: 349

There is a semantic difference between the two.

  1. The IS operator serves as a way to test against a certain value as to whether it is true, false or unknown.

From documentation: IS boolean_value

Tests a value against a boolean value, where boolean_value can be TRUE, FALSE, or UNKNOWN.

mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN; -> 1, 1, 1

For the "=" operator, it is merely a way to equate something to compare. In your query, you are using valid to be set to True.

So, depending on your use case, you would use the operators. In your current query, they look as if they do the same thing.

Upvotes: 0

Related Questions