Importance of WHERE 1 in MySQL queries

Question

When making a MySQL query with no WHERE constraints, most people use WHERE 1 in the query. However, omitting WHERE 1 does not influence the query. Is there a difference between the two? Is one considered to be the best practice?

Answer 1

I don't think it's a matter of best practice, but people sometimes use it to make building dynamic queries a bit easier.

string sql = "SELECT * FROM mytable WHERE 1 ";
if ( somecondition ) {
   sql += "AND somefield = somevalue ";
}

if ( someothercondition ) {
   sql += "AND someotherfield = someothervalue ";
}

... etc

Without the WHERE 1 in there I would need to check in each if block whether I needed to put in a WHERE or an AND.

Answer 2

It's not necessary. 99.9% of the time it just means the query was dynamically constructed and putting in WHERE 1 leads to simpler logic for dynamic conditional clauses, meaning you can just keep adding AND id = 3 to the end of the query and it won't break the syntax. If you don't have WHERE 1 you have to worry about whether or not there's a WHERE clause and whether to prepend your condition with AND or not.

So it's just laziness basically.

Answer 3

It's one less corner case for automated code generators and other SQL statement manipulators. By starting the filtering section with where 1 (or where 1 = 1), your automated code generator can just add new filters with the and prefix.

Otherwise you tend to end up with code like:

query = "select * from tbl"
sep = " where "
foreach clause in all_clauses:
    query = query + sep + clause
    sep = "and "

which is not as clean as:

query = "select * from tbl where 1 = 1"
foreach clause in all_clauses:
    query = query + " and " + clause

It should make very little difference to any decent DBMS since the execution engine should strip out those sort of clauses before the query is executed.

Whether it's best practice or not depends on whether you'd rather have "cleaner" code in your query generators, or whether you'd rather have the company DBAs trying to track you down and beat you to death for using such silly clauses :-) Of course, if you're using MySQL, you may be the DBA so that may not be a problem.