Dynamic SQL Queries

Recently, I discovered an easy and quick method for creating dynamic SQL queries. This method has been useful in both working with SQL queries in PSQL and in programming logic.

Without further ado, this method is simply a condition that always evaluates to true—specifically, the 1=1 condition.

Examples

It is easier to be understood with some examples

SQL Example

SELECT name
FROM Students
WHERE 1=1
AND studId = '010101'
AND major = 'Computer Science'

In this example, 1=1 ensures that there is always a condition, allowing all other conditions to start with the AND or OR operators. This is especially helpful when a query has many conditions that may change over time.

JavaScript Example

// ...
const studentIdFilter = ' AND studId = 010101';
const majorFilter = ' AND major = Computer Science';

let studentSelectQuery = 'SELECT name FROM Students WHERE 1=1';

if(studentIdCondition) {
  studentSelectQuery += studentIdFilter;
}

if(majorContion) {
  studentSelectQuery += majorFilter;
}
// ...

In this example, 1=1 does exactly what it did in the first example: it ensures that there is always a true condition. This is very helpful here, as it would otherwise require extra code to check if both conditions are true before deciding whether to use the AND operator.

Additional Points

It is possible to use any other condition that always evaluates to true, such as 4 > 2, instead of 1=1, but using 1=1 is considered the standard.
This condition does not have any performance impact, as most (if not all) well-known RDBMS ignore it.

Kiriakos Naiskes

Examples

SQL Example

JavaScript Example

Additional Points