Mysql where 1= 0 confusion

Question

In simply sql book, there is portion of code mentioning if I use where 1 = 0, i can safely add an OR conditions like:

WHERE
   1=0
   OR name LIKE '%Toledo%'
   OR billaddr LIKE '%Toledo%'
   OR shipaddr LIKE '%Toledo%'

I didnt get it why when i use where 1 = 0 i can safely add an OR statement

Can i just use something like below?

WHERE
   1=1
   OR name LIKE '%Toledo%'
   OR billaddr LIKE '%Toledo%'
   OR shipaddr LIKE '%Toledo%'

I understand that where 1=1 is a shortcut to add an And statement.
I understand that because where 1 =1 will return true.Can i use it for Or statement too?
Istill didnt get the part where 1 = 0
Any explaination would be great.

Answer 1

This depends on the type of boolean operations you're working on. If you want to add a variable number of AND statements, then you use a statement that invariably evaluates to true, such as 1 = 1. On the other hand, if you want to do the same with OR statements, then you should use a statement that evaluates to false, such as 1 = 0.

Let's say you have a boolean variable x with an indeterminate truth value (it might be true, or it might be false. You don't know.) Now, if you find the value of x AND false, you get false, regardless of what the value of x is.

On the other hand, if you look at x OR true, you'll get true. Again, this is regardless of the truth value of x.

In your statement, you want the hard-coded value to have no effect on the logic of the query. Since false OR a OR b OR c is logically equivalent to a OR b OR c, the hard-coded statement has no effect. In the other case, true AND a AND b AND c is equivalent to a AND b AND c.

Answer 2

MySQL uses short-circuit boolean evaluation.

As soon as the result resolves to a definite TRUE or FALSE MySQL stops evaluating the rest of the rows.

TRUE OR ? OR ? will always be TRUE, so MySQL will only look at the first item and not test anything else.
FALSE AND ? AND ? will always be FALSE, so again MySQL will not look at the other tests.

This has the (unintended) side-effect that MySQL will never look at your table with the LIKE, but instead return all rows in the following query:

SELECT * FROM atable
WHERE
   1=1                         /* <--- this makes the whole OR set always true*/
   OR name LIKE '%Toledo%'     /* therefore the like tests are never done     */
   OR billaddr LIKE '%Toledo%' /*and all rows are returned */
   OR shipaddr LIKE '%Toledo%'

In this query:

SELECT * FROM atable
WHERE
   1=0                          /* false OR ? has an unknown outcome so */
   OR name LIKE '%Toledo%'      /* MySQL has to evaluate the LIKE tests */
   OR billaddr LIKE '%Toledo%'  /*only some rows will be returned*/
   OR shipaddr LIKE '%Toledo%'

This works exactly in reverse with AND