Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Usually, if conditions are not required in our query statements, we don't use a WHERE clause. But I see a WHERE 1 clause being used in many places, even where other conditions are not present.

  • Why is this done?
  • Are there specific benefits to the execution time?
  • Does it enables other functionalities?
  • Is using WHERE 1=1 is similar to this?
share|improve this question
    
Can you provide the links of this question? I already searched and did not find. –  ursitesion Dec 13 '13 at 12:46
    
@Paul: Many thanks. Since next question, I will improve my question standard. Thank you very much. –  ursitesion Dec 13 '13 at 15:04
add comment

4 Answers 4

up vote 13 down vote accepted

Basically it is just for programmer convenience since you can just add additional conditions with AND... after that and it has no impact on execution time.

Check out these links to Stackoverflow:

Note that WHERE 1 is identical to WHERE 1=1; both mean WHERE TRUE but the former is rejected by many database management systems as not really being boolean.

share|improve this answer
    
Questions were related with "where 1=1" due to which I asked a new question here. –  ursitesion Dec 13 '13 at 13:34
add comment

I was programming a bunch of user defined functions in C++/C for PostgreSQL that were used by other people in a large company (10K+ people). My functions have an optional where parameter: if no value was given, the clause was not used. This was explicitly documented. Unfortunately, nobody used this feature and everybody where just supplying where 1=1 clause. Theoretically this seems unwise, practically all query optimizers rule these type of statements out. And it is difficult to educate 10K people.

share|improve this answer
2  
Nobody ever called your UDF with WHERE 1=1; DROP TABLE CUSTOMERS? –  Twinkles Dec 13 '13 at 12:26
    
Still, I am not able to find the concepts behind using Where 1 clause. Need more clarification? As I did not get any improvement in execution time. –  ursitesion Dec 13 '13 at 12:45
    
This has little todo with the execution time. It has todo with the culture how people programm. It happens sometimes that your code (programmed by IT people) is used by engineers. These guys have their own way to think and invoke your code –  arthur Dec 13 '13 at 12:48
    
@Twinkles: this is definitely an interesting issue. There are a few points points here to note. (i) the database is readonly for the UDF (ii) My function joins a few tables groups by a bunch of things and to make things worse use data warehousing windowing functions. This makes it challenging to drop anything, although not impossible, (iii) the UDFs are meant for pre-production code. Which means that it never gets into production: if the function survives the challenge another department completely rewrites everything we programmed –  arthur Dec 13 '13 at 14:23
3  
Actually it's pretty easy to educate 10,000 people. Make the application fail when they do it wrong. They will immediately stop doing it wrong. –  Jasmine Dec 13 '13 at 17:09
show 2 more comments

Why we do so?

Dynamic code generation written by not too competent programmers comes to my mind.

Generates SELECT.... WHERE and then you have to have SOMETHING.... so instead of adding the WHERE only when needed, they add a non-limiting condition when none is there. Seen that - responsible for firing the "specialist".

Or the guy just things WHERE is mandatory ;)

Anything else I fail to see.

share|improve this answer
    
Upvoted because yeah, lazy programming IS the most common cause of this structure –  Jasmine Dec 13 '13 at 17:04
1  
Don't forget "left-overs" from testing. Sometimes it's just not practicable to always "re-type". Or there are reasons for re-ordering the "arguments" (still, while in testing) and test the effects, which might include "shifting" the first "argument". I wouldn't call that lazy, but "practical thinking". Sure, once done with optimizing, that should be cleaned up. –  Izzy Dec 13 '13 at 19:36
    
Testing or not, it shows incompetence and laziness., missing code reviews and something like that. –  TomTom Dec 15 '13 at 12:44
add comment

My main use is that it makes it easier to comment out stuff during development of queries. I lead with ,'s and and's:

SELECT
     A
--  ,B
    ,C
    ,D
--  ,E
FROM TABLE
WHERE 1=1
--  and B='This'
    and C='That'
--  and D is not null

Also makes it easier to programmatically tack stuff unto the end.

this  = "SELECT * "
this += "FROM TABLE "
this += "WHERE 1=1 "
if user chooses option a then this += "and A is not null "
if user chooses option b then this += "and B is not null "
if user chooses option b then this += "and C is not null "
if user chooses option b then this += "and D is not null "

Otherwise you'd have to qualify the first option... and have each following option check the previous options. What if the user only chose Option D in the previous example? You'd have to make sure that if A, B and C aren't chosen then use WHERE else use and. With = at the start, you can just slap the qualifiers to the end of the statement.

share|improve this answer
    
And you wrote that code like 1980 in a language without object orientation, befor anyone came up with the idea of a object based sql generator? –  TomTom Dec 15 '13 at 12:44
    
I write code similar to the first block in SSMS or similar query tools. Researching data sets and getting the right results before it gets put into reporting tools (like Crystal Reports) for end users. The second block is what I've seen other people do, since my job hasn't been too much about raw sql access. I've troubleshot other peoples similar code and can understand the reasoning. (Code was in VB, C# and PHP). –  WernerCD Dec 15 '13 at 18:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.