Why use WHERE 1 or WHERE 1=1?

Question

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?

Answer 1

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:

• Why would someone use WHERE 1=1 AND in a SQL clause?
• “where 1=1” statement

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

Here you go... from programming point of view one use of 1=1...

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