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?

http://stackoverflow.com/questions/242822/why-would-someone-use-where-1-1-and-conditions-in-a-sql-clause — Gaurav Soni, Aug 19 '13 at 07:43
0=0 is like TRUE. This is used when you want to dynamically append different conditions in the where clause of a select statement. In case there are no where clauses all records will be retured from the query. So in all 0=0 is not going to affect the results. It is only going to help as a first placeholder condition and after that you can attach as many conditions as you like using "AND" or "OR" — Nilesh, Aug 19 '13 at 08:00

Dmitry Bychenko · Answer 1 · 2016-10-26T22:33:42.827

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

score 3 · Accepted Answer · answered Aug 19 '13 at 08:03

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;

Hello Stevo, Cant I have like the where clause takin up the first condition like 'SELECT * FROM table WHERE column_1 = ''A'''; and then append the other conditions dynamically rather than having a extra "where 0=0"?.. — user2622662, Aug 19 '13 at 09:13
As long as the first where clause is always needed, then that's fine. — steve godfrey, Aug 19 '13 at 09:39

score 1 · Answer 3 · answered Aug 19 '13 at 07:44

1

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

SELECT * FROM table WHERE 1=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.

answered Aug 19 '13 at 07:44

aleroot

71,077
30
176
213

so deep, for me there are just simple boolean condition checking =( – Low Chee Mun Aug 19 '13 at 07:51
@user2622662 yes, you can but the always true condition avoid to have to modify the first part of the query, with more complex checking ... – aleroot Aug 19 '13 at 08:01
@user2622662 more complex checking means that you can't only append to the string but you could ave to modify the original base start point SQL string ... – aleroot Aug 19 '13 at 08:07

score 0 · Answer 4 · answered Aug 19 '13 at 08:12

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 * from 
TABLE
WHERE 1=1
AND condition1
AND condition 2
.....

.

score 0 · Answer 5 · answered Sep 01 '17 at 13:35

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
  LEFT JOIN (SELECT MAX(value) AS value
               FROM tableB) b
    ON 1 = 1;

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

5 Answers5

Linked