Warm tip: This article is reproduced from stackoverflow.com, please click
mysql

What is the use of 'WHERE TRUE' in MySQL

发布于 2020-04-23 18:35:23

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?

Questioner
Darjeeling
Viewed
9
Robert Columbia 2016-06-16 10:46

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.