Whats The Exact Meaning Of Having A Condition Like Where 0=0?

I'm having a bit confusion in using a statement like 'where 0=0' in Oracle procedures? Can someone please clarify it? Even though we do it for dynamic selection, why do we add this

Solution 1:

We use 0 = 0 or, usually, 1 = 1 as a stub:

select *
  from My_Table
 where1 = 1

So when you write filters you can do it by adding/commenting out single lines:

-- 3 filters addedselect*from My_Table
 where1=1and (Field1 >123) -- 1stand (Field2 =456) -- 2nd and (Field3 like'%test%') -- 3d

Next version, say, will be with two filters removed:

-- 3 filters added, 2 (1st and 3d) removedselect*from My_Table
 where1=1-- and (Field1 > 123) -- <- all you need is to comment out the corresponding linesand (Field2 =456)
   -- and (Field3 like '%test%')

Now let's restore the 3d filter in very easy way:

-- 3 filters added, 2 (1st and 3d) removed, then 3d is restoredselect*from My_Table
 where1=1-- and (Field1 > 123) and (Field2 =456)
   and (Field3 like'%test%') -- <- just uncomment

Solution 2:

When using dynamic sql, extra clauses may need to be added, depending upon certain conditions being met. The 1=1 clause has no meaning in the query ( other than it always being met ), its only use is to reduce the complexity of the code used to generate the query in the first place.

E.g. This pseudo code

DECLARE
v_text VARCHAR2(2000) :='SELECT * FROM table WHERE 1=1 ';
BEGIN

    IF condition_a = met THEN
    v_text := v_text ||' AND column_1 = ''A'' ';
    END IF;

    IF condition_b = also_met THEN
    v_text := v_text ||' AND column_2 = ''B'' ';
    END IF;

execute_immediate(v_text);

END;

is simpler than the pseudo code below, and as more clauses were added, it would only get messier.

DECLARE
v_text VARCHAR2(2000) := 'SELECT * FROM table  ';
BEGIN

    IFcondition_a= met THEN
    v_text := v_text ||' WHERE column_1 = ''A'' ';
    END IF;

    IFcondition_b= also_met AND 
       condition_a != met THEN
    v_text := v_text ||' WHERE column_2 = ''B'' ';
    ELSIFcondition_b= also_met ANDcondition_a= met THEN
    v_text := v_text ||' AND column_2 = ''B'' ';
    END IF;

execute_immediate(v_text);

END;

Solution 3:

It is usually used when you need to concatenate a String of the SQL Query, so you write the first part :

SELECT*FROMtableWHERE1=1

and then if some condition is true you can append more clause, otherwise leaving the query as it is, it will run without errors ...

It is generally used to add more clause at runtime appending directly to the string of the query.

Solution 4:

This is always true Condition i.e. '0' will always be equal to '0'. Which means your condition will always be executed.

Some people use this for ease in debugging of a query. They will put it in where clause and rest conditions with AND clause so that for checking purpose they can comment the unnecessary condition.

For ex

SELECT*fromTABLEWHERE1=1AND condition1
ANDcondition2
.....

.

Solution 5:

1=1 is also useful when you want join condition to be always true. For example something like that (adds b.value to all rows):

select a.code, a.name, b.value
  from tableA a
  LEFTJOIN (SELECTMAX(value) ASvalueFROM tableB) b
    ON1=1;