In Oracle, is starting the SQL Query's WHERE clause with 1=1 useful?

Question

I'm working with a client that starts almost all of their WHERE clauses in Oracle with 1=1. Forgive my ignorance, but isn't this a no-op? Are there any negative consequences of this usage?

Here's a scrubbed example:

SELECT   gpz.zname
         ,gpp.pname
FROM     table1 gpp INNER JOIN table2 gpz ON gpz.p_id = gpp.p_id
WHERE    1=1
         AND gpp.active = 1
         AND gpz.active = 1

Answer 1

It's done to simplify dynamic SQL generation. Basically each condition can be added as AND <condition> without treating the first condition as special (it's preceded by WHERE not AND) or even worrying if there should be a WHERE clause at all.

So just write it off as easy of use or, arguably, laziness.

Answer 2

Did they build this statement from substrings? It may be a way to build a query with just ANDs depending on the business rule match...

So you don't really have to care which is your first condition. Wouldn't do it that way myself, just wondering :-)

Answer 3

If they are building the query dynamically, you should check whether they're using bind variables. Building the query from literals requires extra parsing, potentially limiting scalability, and also can greatly increase the risk of SQL Injection attacks.

where 1 = 1 and my_id = :b1; (and then defining the value of the bind variable)

is generally much better than

where 1 = 1 and my_id = 123456;