logical OR in MySQL

Question

How to make a condition with logical OR in SQL (MySQL), so that the sub-conditions would be carried out on an as-needed basis ?

For example:

SELECT * FROM \`mytable\` WHERE (\`a\` || \`b\` || \`c\`)

`a` is FALSE
`b` is TRUE
`c` is FALSE or TRUE

I want to MySQL:

1) get `a`                                 // ok, `a` is FALSE
2) if (`a` IS TRUE) break        // no break
3) get `b`                               // ok, `b` is TRUE
4) if (`b` IS TRUE) break;        // ok, break

Looking at the last part of your question, are you asking whether the expression `a OR b OR c` [short-circuits](http://stackoverflow.com/questions/6960767/do-modern-dbms-include-short-circuit-boolean-evaluation)? — onedaywhen, Mar 26 '12 at 10:13

score 1 · Accepted Answer · answered Mar 26 '12 at 10:06

Logical OR already behaves that way.

However what is considered evaluation of those expressions is not necessarily what you expect, e.g. database may still need to fetch all rows related to the expression. Specifically, MySQL can't always use indexes if you use OR.

BTW: You should use OR, which is a standard SQL syntax. || in ANSI SQL (other databases and ANSI mode in MySQL) concatenates strings.

score 0 · Answer 2 · answered Mar 26 '12 at 10:07

0

If they are boolean fields Try using this

SELECT * FROM `mytable` WHERE `a` OR `b` OR `c`

answered Mar 26 '12 at 10:07

Neeraj

8,625
18
60
89

my `c` is sub-query (SELECT * FROM `mytable2` WHERE ... LIMIT 1) – user1292621 Mar 26 '12 at 10:09

score 0 · Answer 3 · answered Mar 26 '12 at 10:18

To get what you want, you just have to reorder your operands. As soon, as an operand of OR is TRUE, the condition is not further evaluated. To achieve high performance, you must put the operands that are most likely true in front of the others.

You don't want a break, if a is TRUE? Try this for a start:

SELECT * FROM `mytable` WHERE `c` OR `b` OR `a`

logical OR in MySQL

3 Answers3