unnecessary 1=1 in select query

Question

Below query is used in one of my product. I am not able to understand why 1=1 is mentioned. it is always going to true.

select * from emp;
select * from emp where 1=1;

As per my understanding both are same. What could be the intention of developer to put 1=1 in below query? he can simply write where condition in one of the and condition.

SELECT   msi.concatenated_segments item_code,
mmt.transaction_date,
mtt.transaction_type_name,
mmt.transaction_quantity,
mcd.prior_cost,
mcd.new_cost
   FROM 
mtl_cst_actual_cost_details mcd,
mtl_system_items_kfv msi,
mtl_material_transactions mmt,
mtl_COST_types mtt
WHERE 1 = 1  -------------------------------------------------This I am refering
AND mcd.inventory_item_id = msi.inventory_item_id
AND mcd.organization_id = msi.organization_id
AND mcd.transaction_id = mmt.transaction_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mmt.transaction_type_id = mtt.transaction_type_id
ORDER BY mcd.creation_date DESC;

But – as Guy has correctly observed – the people who now do this day have probably become conditioned by what they've seen by *programmatically generated* SQL statements in the past. — Mike Robinson, Sep 01 '20 at 21:08

score 2 · Accepted Answer · answered Sep 01 '20 at 21:04

where 1 = 1 is always true, so both queries are equivalent. Most databases are able to identify that when parsing the query, so there is no performance penalty involved.

A common reason why applications use this dummy where clause is that it makes it easy to further concatenate conditions in the where clause.

Starting from string 'where 1 = 1', the application can concatenate as many and predicates as necessary. Otherwise, it would need to check if a condition was already added in the query string before deciding if it is necessary to put 'and' before the condition.

score 2 · Answer 2 · answered Sep 01 '20 at 21:05

If the query is generated dynamically, the logic to add the where conditions is a bit simpler if there is a first 1=1. You can just append them like:

STATIC QUERY
AND WHERE_CONDITION_1
AND WHERE_CONDITION_2
AND WHERE_CONDITION_3
...

And they are not depending on the position.

I think some ORM systems use this approach as well.

score 1 · Answer 3 · answered Sep 01 '20 at 21:12

Like I commented above – many programmers today are entirely used to "ORM's," and the sometimes-nonsensical yet grammatically-correct SQL clauses that are sometimes generated by them. And actually: "no harm, no foul."

WHERE 1=1 is of course a perfectly-valid WHERE clause if your internal logic (buried somewhere within the bowels of the ORM in question ...) was designed in such a way that you have to produce one. ‍♂️

.. or, maybe, if you decide that your programming somehow needs to join(' AND ') a whole bunch of clauses, and you decide that you need to stitch a "dummy clause" in front of them. (Although, of course, you actually don't.)

unnecessary 1=1 in select query

3 Answers3