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 TRUEas 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?

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. More generally, 1=1 is as legitimate a boolean clause as any other, and can be "dropped" into a conditional expression without having to special-case the query to omit the WHERE clause.

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

Do note that the example code here is vulnerable to SQL Injection, so it should not be used in cases where you are dealing with AccountID's that you did not produce yourself. There are multiple ways to secure it that are beyond the scope of this answer.