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?
0

6 Answers 6

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.

0
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.

2
  • 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, 2013 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, 2013 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.

0
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.

7
  • 3
    Nobody ever called your UDF with WHERE 1=1; DROP TABLE CUSTOMERS?
    – Twinkles
    Dec 13, 2013 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, 2013 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, 2013 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, 2013 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, 2013 at 17:09
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.

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

1
  • This is a wrong query. Your query will be like where 1=1 AND OR ...
    – hakkikonu
    Dec 27, 2019 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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