What is the use of 'WHERE TRUE' in MySQL

Question

I'm working on my colleague's old project and I found on her code

WHERE TRUE AND ID='1' AND STAT='1'

.
I've tried to removed

TRUE AND

so the query become

WHERE ID='1' AND STAT='1'

and get the same result.

I know we can use

TRUE

as boolean to search 'where something is true' such as

WHERE FLAG = TRUE

and this MySQL documentation state that

The constants TRUE and FALSE evaluate to 1 and 0, respectively. The constant names can be written in any lettercase.

I also tried

SELECT * FROM MYTABLE WHERE TRUE

but it's just the same as

SELECT * FROM MYTABLE

what is the purpose of

TRUE

in her query?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

It has no specific functional purpose. Your colleague may have included it if they were adhering to a specific style guide that recommends that all SELECT queries have explicit WHERE clauses. If an explicit WHERE clause is not provided, the default is to select all rows. Adding a WHERE clause that is always true will have the same effect.

Another way this query could have come about is if it was generated by a code generation tool that always had to write in a WHERE clause due to the way it was written.

for example:

myQuery = "SELECT X FROM Y WHERE " + ConditionMet(data)?" AccountID = '" + AcctId + "'" : "1=1";

This means that if ConditionMet(data) is true, then only return rows where AccountID matches the AcctId you are passing in. If it is false, then return all rows.

Adding a "dummy" 1=1 makes the code generator simpler to write.

Similarly, adding a WHERE clause that is always false (e.g. "WHERE 1=0") will result in zero rows being returned.

What is the use of 'WHERE TRUE' in MySQL

Sign up