61

Usually, if conditions are not required in our query statements, we don't use a WHERE clause. But I've seen 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 enable other functionalities?
  • Is using WHERE 1=1 similar to this?
D M
  • 433
  • 5
  • 8
ursitesion
  • 2,071
  • 7
  • 32
  • 45

6 Answers6

61

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.

Community
  • 1
Twinkles
  • 2,361
  • 1
  • 17
  • 25
23

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.

WernerCD
  • 1,225
  • 3
  • 11
  • 19
  • 1
    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
  • 1
    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
10

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.

TomTom
  • 4,648
  • 1
  • 16
  • 19
5

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.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
arthur
  • 888
  • 3
  • 13
  • 25
  • 3
    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
  • 6
    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
  • @Jasmine: Making an application fail, when they do it wrong has a high price. The 10K+ people first will start calling, then 1K+ people will start discussing why the function got unusable and 1 person will eventually fire you :) – arthur Dec 15 '13 at 12:16
  • USERS are responsible for knowing how to use their software. Seriously, they need to be involved in testing, requirements generation, and all levels of development, AND they need to be trained. Spending money on development instead of training is usually going to put you behind. If users have a culture of being lazy, that can be tricky to turn around, but soooo, soooooo worth it! – Jasmine Dec 16 '13 at 16:51
4

Using "where 1=1" reduces the complexity of the code needed in dynamic sql 'where' clause generation. Otherwise, when creating the 'where' clause you would need to check if this is the first component for each component added. This is a simple code pattern to reduce code complexity and reducing code complexity, where possible, is nearly always the right decision.

Niall
  • 41
  • 1
0

Here you go... from programming point of view one use of 1=1...enter image description here

In a situation like this when i need to construct a query on run time and it can be short or long , i use 'where 1=1 AND'

string criteria =string.Empty;
        if (txtc1.Text != "")
        {
            criteria += "criteria1=" + "'" + txtc1.Text + "' ";
        }
        if (txtc2.Text != "")
        {
            criteria += "OR criteria2=" + "'" + txtc2.Text + "' ";
        }
        if (ddl1.SelectedItem.Text != "")
        {
            criteria += "OR criteria3=" + "'" + ddl1.SelectedItem.Text + "' ";
        } if (ddl2.SelectedItem.Text != "")
        {
            criteria += "OR criteria4=" + "'" + ddl2.SelectedItem.Text + "' ";
        }
        if (ddl3.SelectedItem.Text != "")
        {
            criteria += "OR criteria5=" + "'" + ddl3.SelectedItem.Text + "' ";
        }
        if (ddl4.SelectedItem.Text != "")
        {
            criteria += "OR criteria6=" + "'" + ddl4.SelectedItem.Text + "' ";

        } 
        if (txtc1.Text == "")  
        {
            //criteria = criteria.Substring(2);
            criteria = criteria.Substring(2,criteria.Length-3);
        }
        if (criteria != string.Empty)
        {
            criteria = "where 1=1 AND " + criteria;
            //Response.Write("<script>alert('query constructed by you is : '+'" +criteria+ "');</script>");
            Response.Write(criteria);
        }

Make things easier

Saurabh
  • 101
  • 1