MySQL – How to Test a Constant Boolean Expression

MySQL

Suppose I have a simple boolean expression involving only constants, like

4 + 5 = 9

Maybe (for debugging purposes – maybe the expression is a complicated LIKE expression) I want to just check to see if this expression is true or false. Is there a simple trick for doing this?

Currently I'm just using

SELECT * FROM my_schema.my_table WHERE 4 + 5 = 9;

and seeing if I get anything back. I thought SELECT 1 WHERE expression might work, but no luck (MySQL 5.6.10). To be more precise about what I mean by "simple trick", let's say we want a command which will run without modification on any server. My current command doesn't fit the bill since we need to know the name of a schema and table in the server.

Best Answer

You can use

SELECT (4 + 5 = 9) AS expression_result ;

It will either give you 1 (TRUE), 0 (FALSE) or NULL (UNKNOWN).

If you prefer string output instead of numbers:

SELECT CASE (4 + 5 = 9)
         WHEN TRUE  THEN 'TRUE'
         WHEN FALSE THEN 'FALSE'
         ELSE            'UNKNOWN'
       END AS expression_result ;

Alternatively:

SELECT 1 FROM dual WHERE (4 + 5 = 9) ;

It will give you either 1 (TRUE) or no rows in the results (FALSE or UNKNOWN).

I'd prefer to use the first method as it distinguishes between all three possible results of a boolean expression in SQL (TRUE / FALSE / UNKNOWN).

Related Question