Whats the exact meaning of having a condition like where 0=0?

Question

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 statement even though we append the actual condition in the query? Will this where condition make any difference to the result set?..

I went through How can I Select all rows where column contain any words of a string?

but I didn't exactly understand the reason for using where 0=0. Can some one please give me the proper reason for using such a condition?

Answer 1

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

select *
  from My_Table
 where 1 = 1

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

-- 3 filters added
select *
  from My_Table
 where 1 = 1
   and (Field1 > 123) -- 1st
   and (Field2 = 456) -- 2nd 
   and (Field3 like '%test%') -- 3d

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

-- 3 filters added, 2 (1st and 3d) removed
select *
  from My_Table
 where 1 = 1
   -- and (Field1 > 123) -- <- all you need is to comment out the corresponding lines
   and (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 restored
select *
  from My_Table
 where 1 = 1
   -- and (Field1 > 123) 
   and (Field2 = 456)
   and (Field3 like '%test%') -- <- just uncomment

Answer 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

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

    IF condition_b = also_met AND 
       condition_a != met THEN
    v_text := v_text ||' WHERE column_2 = ''B'' ';
    ELSIF condition_b = also_met AND 
       condition_a = met THEN
    v_text := v_text ||' AND column_2 = ''B'' ';
    END IF;

execute_immediate(v_text);

END;