July 27, 2004 at 9:34 am
Hi there,
I'm not a SQL hotshot and sometimes I see constructions I don't understand. Here is 1 I encountered in a SP:
SELECT .........enormous selectstatement with functions, joins & whatnots....
WHERE 1 = 1
What is the purpose of this WHERE clause? Why is this a good thing to use, and under what circumstances?
TIA
Greetz,
Hans Brouwer
July 27, 2004 at 10:05 am
1=1 is true, so it always returns something.
select 'test'
from orders
where 1=1 will return all the rows in the orders table.
July 28, 2004 at 12:47 am
Tnx for the info, Steve. I'm trying to think why you would want to add a qualification, where you will always get something returned.
Greetz,
Hans Brouwer
July 28, 2004 at 6:22 am
Hello Hans,
offhand and not knowing your database I can think of two reasons for such construct (although I'm not sure whether it can work this way in a SP... but it could)
- query is used as a part of a dynamically changing statement; example: report on PHP intranet pages, users can choose whether to display all records or only those matching some criteria. The "WHERE 1=1" allows you to skip any checks, wheter some criteria were entered or none - based on selection made by user, the code simply adds "AND partner_id = 1659" etc. Without the 1=1, if no criteria are specified, you would end up with just WHERE and nothing more, resulting in an error (or you have to check for criteria and add the WHERE only if there are some, decide where to put "WHERE" and where to put "AND"... as you see, 1=1 is for lazy people who don't want to bother with it)
- if 1=1 is a part of a longer string of conditions with several AND/OR (which it isn't here, as I understand), it can be used during debug phase to switch certain parts of the WHERE clause off (AND 1=2) and on (AND 1=1), to see what happens. If at the end you let it be at 1=1, it is the same as if you delete it... so it could hide there unobserved for a long time when it isn't necessary any more.
Well, I didn't comment on whether it is a good practice to use such statements, just explain why anyone could think of using them... which was your question, if I'm right. Cheers, Vladan
July 28, 2004 at 6:46 am
Tnx for the info, Vladan. Your first explanation is correct, it is part of a dynamically build statement with optional qualifications.
Tnx again.
Greetz,
Hans Brouwer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply