Understanding the Use of WHERE 1=1 in Dynamic SQL

Programmers often see the pattern "WHERE 1=1" in generated SQL statements, especially when building dynamic queries in languages like Java.

Example

String sql = "select * from table_name where 1=1";
if (condition1) {
  sql = sql + " and var2=value2";
}
if (condition2) {
  sql = sql + " and var3=value3";
}

The purpose of "WHERE 1=1" is to avoid syntax errors that would occur if the first appended condition started with "AND".

Dynamic SQL AND concatenation

Adding "WHERE 1=1" guarantees that the WHERE clause always has a valid predicate, allowing subsequent conditions to be concatenated safely with "AND".

However, a query like "SELECT * FROM table WHERE 1=1" performs a full table scan and can be inefficient for large datasets.

It is recommended to add mandatory filter conditions after "WHERE 1=1" and create appropriate indexes on those columns to improve performance.

Copy Table

create table table_name as select * from Source_table where 1=1;

Copy Table Structure

create table table_name as select * from Source_table where 1 <> 1;

In summary, using "WHERE 1=1" simplifies dynamic SQL construction, but developers should still add selective predicates and indexes to avoid full scans.