can you explain more about this trick
Bob, August 09, 2001 - 9:18 pm UTC
You have put us in suspense about this trick
can you take an example of your own, and show the use of 1=1 , and what is it providing.
and how does 1=1 help in adding an "AND <condition>" to the end of the query easily.
Still Confusing
Sikandar Hayat Awan, August 09, 2001 - 11:19 pm UTC
Sorry, I have not got your point that someone is adding where 1=1 to help in future that he/she will enter only AND. Now we will compere these two,
Select * from emp where 1=1 and idno =10;
Select * from emp where idno =10;
Here second query is short and developed with little efforts.
What r ur comments.
Thanks.
useful trick "where 1=1"
Ora_Coder, August 10, 2001 - 1:57 am UTC
The trick is that the query already has a "select * from emp where 1=1". This helps us in concatenating the query
dynamically without adding the "where" clause the first time & checking/avoiding it for all additional clauses.
You just keep adding the clause " and x=y" for all additional clauses.
respond
A reader, August 10, 2001 - 12:07 pm UTC
can you please respond to comment one
re: how this trick works?
Bob, August 10, 2001 - 1:41 pm UTC
That's easy. Imagine you have a search engine that you want to be able to perform a full search and a narrowed down search by a number of predicates. You write a query like this:
stmt := 'select something from somewhere where 1=1';
begin
if predicate1 then stmt := stmt||' and pred1='||pred1value; end if;
....
execute immediate stmt using ....;
end;
you can add as much predicates as you want simply skipping those not used and if none were used you will still have a valid query, so you don't have to check if where has any arguments before executing it.
Missing the point
Mark J. Bobak, August 10, 2001 - 1:42 pm UTC
I think you guys are missing the point. The 'trick' is
that when you have a SQL w/ a dynamic 'where' clause,
it's lots easier if you can guarantee that there will
always be at least one where condition. So, use '1=1' as
that one where condition. Example:
Suppose 'select * from emp' is the base query, and the
user can dynamically plug in where conditions.
Suppose he adds two, 'deptno=5' and 'sal>5000'. So,
here, you have to keep track of whether 'deptno=5' is
the first condition, in which case you need to append
'where ' or not the first condition, in which case
you need to append 'and '. By starting w/ a base
SQL of 'select * from emp where 1=1', you can avoid that
logic and just add each user supplied where condition, and
tack a 'and ' in front of it, and it will work.
And the '1=1' will not have any effect on the data selected,
it's sole purpose in life is to make it simpler to tack on
dynamic where conditions.
Several Meanings
Holger Spangardt, August 10, 2001 - 3:54 pm UTC
We use this clause for forcing full table scan.
Usually no index is used with "where 1=1".
This easy helps finding corrupt indexes :-)
Another use
Still learning, August 10, 2001 - 10:37 pm UTC
I think we can use that feature to create a table based
on another table, say:
create table temp as select * from all_objects
1) where 1=1 ----- insert data as well from all_objects;
2) where 1=0 ----- don't import data from all_objects, only create table structure based on all_objects.
correct me if i'm wrong
where 1=1
kannan, August 11, 2001 - 5:33 am UTC
Even if i studied in material i can't understand this much.
Excellent TOM
Can you explain this please
Sonali Kelkar, August 13, 2001 - 4:16 pm UTC
We do lot of HTML coding and we use this 1= 1 all the time, but they say that it is for validation. How does it work any example.
thanks
Sonali
sonaliak@hotmail.com
1=1
Badhri, August 14, 2001 - 6:27 am UTC
Is it that a where clause 1=1 will be helpful to the developer to add his additional where clauses without typing a keyword WHERE. This doesn't solve a great problem. Do we have any performace change in doing the same... How does the indexes are involved if we use the same...
The timing of execution of the SQL is faster compared to without 1=1.. I dont understand the logic behind.. Can anyone help
Not really that hard.,
Andrew Gilfrin, January 14, 2003 - 10:09 am UTC
It seems people are finding this hard to understand, but its really rather a easy and neat trick.
Say your developing an application which accepts parameters from a user. He could enter 0, 1, 2 or n parameters and based on this you need to return records.
You already know the main select something like
select * emp
You come to the part in your program where you need to build the where clause. The problem is we need to know which parameter the user has entered is the first as this needs a preceeding where and subsequent ones need an 'and'
To do this you could use code like this.
first_val varchar2(1) := 'Y';
select_code varchar2(1000) := 'select * emp';
If param1 <> NULL
If first_val = 'Y'
select_code := 'where '||param1
Else
select_code := 'and '||param1
End If
End If
If param2 <> NULL
If first_val = 'Y'
select_code := 'where '||param2
Else
select_code := 'and '||param2
End If
End If
.
.
.
And so on.
Using 1=1 we dont need to do the secondary checks to check if this is the first time we have a parameter as by defualt we have the 1=1.
So it becomes
select_code varchar2(1000) := 'select * emp';
If param1 <> NULL
select_code := 'and '||param1
End If
If param2 <> NULL
select_code := 'and '||param2
End If
.
.
.
Doubt cleared up
Robert Wood, January 14, 2003 - 11:00 am UTC
Tom,
Thanks for verifying that the 'where 1=1' clause does in no shape, form, fashion, manner, etc. affect the optimizer.. I will have no qualms in using this from now on.
Thanks,
Robert
Validation
Lewis Cunningham, January 14, 2003 - 11:01 am UTC
I think instead of being used for validation, the developers said it was for validation so they wouldn't have to spend a day explaining how and why they were building dynamic queries.
Not only have I used the 1 = 1, I have also, depending on the skill level of the person I was speaking to, said it was for validation (and occassionally for better performance). In general it's an answer that makes people's eyes glaze over and they walk away. Only useful on people who won't then ask why.
Oops
Andrew Gilfrin, January 14, 2003 - 11:58 am UTC
Yeah sorry I meant to say the code was sort of meant to be pseudo code rather than actual code. It just looks a bit PL/SQL like so it was a bit more meaningfull for Oracle people.
I wanted to keep it simple to get the core message across rather than confusing people more by introducing bind variables and the like ;)
why is this happening
umesh kasturi, April 21, 2003 - 11:36 pm UTC
select ''''||decode( TRIM(dname) ,'ACCOUNTS',10)||'''' FROM DEPT
the query returns '10'
'10'
select * from dept
WHERE TO_CHAR(DEPTNO) IN (
select ''''||decode( TRIM(dname) ,'ACCOUNTS',10)||'''' FROM DEPT
)
no rows returned ??
thanks in advance
April 22, 2003 - 7:38 am UTC
what does
select * from dept where to_char(deptno) in ( '''10''' )
return -- that is what the second query is. the first query is
select '''10''' from dept;
since deptno (a number) would never have quotes around it the second query returns NOTHING.
perhaps you meant:
select * from dept where deptno in ( select decode( dname, 'ACCOUNTS', 10 ) from dept )
datatype returned by the decode
andrew, April 22, 2003 - 1:05 pm UTC
I guess it's not obvious that the decode would return a number, but it's easy to confirm:
SQL> create or replace view v1 as ( select decode( dname, 'ACCOUNTS', 10 ) deptno from dept);
View created.
SQL> descr v1
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER
SQL>
April 22, 2003 - 9:34 pm UTC
it is obvious?
decode is documented to return the datatype of the FIRST returned datatype
decode( x, y, Z, .... )
^^ datatype of Z detmermines the return type.
One more on that!
DeeeBeee Crazeee, April 23, 2003 - 2:11 am UTC
Hi Tom,
I'd used 'WHERE TRUE AND....' for that when I used to work in SQL Server...that however doesn't work out in Oracle...I guess thats where 1=1 can be used.
I had another question in those lines. As a part of my filtering query, I need to filter out records based on certain conditions if provided. Say for example, if the user selects 'MANAGER' for designation, the the resultant should return me managers, else all the records. My query goes something like...
SELECT ...
FROM ....
WHERE ....
AND manager = NVL(l_mgr, manager)
This is returning me the correct results, but just wanted to check the overhead it may have when l_mgr is NULL. The condition would be like 'manager = manager'....is there any implications or is the condtion ignored?
thanks in advance
April 23, 2003 - 7:19 am UTC
1=1
Abdul Wahab Ahmed Shaikh, December 16, 2003 - 11:43 pm UTC
The topic of 1=1 was extremely useful in terms that the concept was not covered in any standard documentation and it really helps in maintability and ease of adding predicates.
1=1 Does it Evaluated per each row
Raj Ganga, August 13, 2004 - 1:28 am UTC
Hi Tom,
when i speify 1=1 and suppose the table has 1 million rows,
does the predicate is evaluated for each row,
or
it does for only once and returns rows
since values are not going to change per row(constant Values 1=1).
Thanks and Regards
August 13, 2004 - 10:02 am UTC
"constant facts" like that are optimized away.
Another reason
Kevin, August 13, 2004 - 5:05 pm UTC
I personally use it because I'm an extremely lazy developer who will do anything to reduce key-strokes.
And when developing a query, it is much easier to work with WHERE conditions (move them around, temporarily comment a few out, etc.) in this format:
SELECT ... FROM ...
WHERE 1=1
AND cond1
AND cond2
AND cond3
;
than in this format:
SELECT ... FROM ...
WHERE cond1
AND cond2
AND cond3;
In my format, moving cond1 to the end of the query requires only one cut-n-paste... in the second format, two cut-n-paste's are required. In my format, commenting out cond1 is a snap... in the latter, a little more work is required.
Of course, when I explain this, most people just look at me blankly... at which point I say, "umm ... and it helps performance, too."
:)
A reader, April 03, 2006 - 4:30 pm UTC
Hi Tom,
I have to dynamically form an update statement and can I use a similar trick like " where 1= 1" in forming the SET part of the update clause ?
Thanks.