student080705639 student080705639 - 5 months ago 7
SQL Question

What is the use of 'WHERE TRUE' in MySQL

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?

Answer

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.

Comments