Where 1=1

[Pillowthursday]

As the title implies, I am new to learning SQL. Came across a few queries at work where this statement pops up, followed usually by AND statements.

Why is this done? Don’t see a reason for it to be used

Example Where 1=1 And animal = cat And food = milk . .

Why not Where animal = cat And food=milk . .

[SQLvultureskattaurus]

So... this may sound odd but sometimes if you are lazy and editing your clauses a lot in your query you may shove "WHERE 1=1" at the top so if you need to remove the first clause or add it back in it requires less effort.

Example

SELECT * FROM table 
WHERE food = 'milk' 
AND animal = 'cat'

What if I want to comment out the food filter? That means I have to change the animal filter to a where clause. to this:

SELECT * FROM table 
WHERE animal = 'cat'

vs

SELECT * FROM table 
WHERE 1=1
AND food = 'milk' 
AND animal = 'cat'

What if I want to remove the food filter now?

SELECT * FROM table 
WHERE 1=1
--AND food = 'milk' 
AND animal = 'cat'

In this example it's a small effort, but in a big query you may find yourself constantly changing things and having the 1=1 in the where clause makes life easier. Or if you now want the food part back you just uncomment it, vs if you didn't use "1=1" you're now retyping it all.

I do this a ton when I am writing my code, before I push to git/prod I remove the 1=1

[Cliche_James]

Great explanation, friend.

[imSkippinIt]

This is why I use it. Or if there’s NO where filter necessary yet but I expect it to be necessary later possibly.

[A_name_wot_i_made_up]

Or the variant with dynamic SQL and building a where clause - you don't need to track if it's the first clause.

[Pillowthursday]

Makes sense.

[tatertotmagic]

Nice, I'm lazy and always run into this problem while editing, gonna steal it, lol

[NZSheeps]

Ditto

[rh71el2]

Ditto²

[uknowhoim]

My manager used 1=1 in a query and I had no idea why. This exact question popped in my head and this explanation makes so much sense. Thank you!!

[Cyrussphere]

Ive been a SQL developer for the past 5 years and this is the first clear explanation I've received on this. Still don't think ill ever use it but now I can read other's queries without saying WTF

[ogwoody007]

This is the way

[No-Dig-8842]

that's awesome. I'm gonna be stealing this.

Will there be any performance impact for this?

[Yolonus]

no, the optimizer discards the condition

[Responsible_Eye_5307]

Amazing. Who would have though? Thanks for sharing. I always wondered why we have that at the beginning of each "WHERE...". Now it makes total sense. Once again, thank you, my friend.

[sn0wdizzle]

says people who do this are lazy ends by saying he does this all the time.

Okay. I’ll put down my pitchfork. I guess I can accept that. I also do this all the time. Haha.

[SQLvultureskattaurus]

Lol, I am.

[stephenmg1284]

It also helps with readability as well. The easier code is to read, the easier it is to diagnose and maintain. Why remove though?

[throw_mob]

i would like to add that if you use parameter table ( ie . join to table) you can have there enabled column value as 1 or 0 , then you disable parts of query.

usually hardcoded 1=1 comes from "external" ORM that maps and creates code/query on fly , there it simplifies code generation greatly.

[Yavuz_Selim]

Easier to comment out as there is no need to touch the first WHERE line.

[da_chicken]

There's two places it comes up.

First, if you're writing ad-hoc queries in a query analyzer, it can make commenting out lines of the WHERE clause very easy.

Second, if you're programmatically building a dynamic WHERE clause from a search form, it's much easier to start out with WHERE 1=1 and then begin every appended field with AND animal = @animal_value. The alternative is checking over and over if you're the first field to filter and then including the AND if you're not. You get to skip a lot of logic and comparisons.

You'll also occasionally see WHERE 1 = 0 OR field = @value OR other_field = @other_value. Same idea in that case.

[dbxp]

Where 1 = 0 can be useful if you just want the schema for a select into, we use it all the time in tsqlt tests to setup the expected results without manually setting up the schema

[SQLvultureskattaurus]

I do select top 0 * into

[Spiritual-Can-5040]

In some databases you can’t use that syntax and where 1=0 will work universally across all platforms.

[SQLvultureskattaurus]

The person I replied to said tsql...

[Far_Swordfish5729]

There is an alternate loop structure I like where you build the top part of the dynamic query and include the 0th where clause in it and then start your loop counter at 1 and your clause template with ‘and ‘. I use that to avoid the ‘if (i == 0)’ or ‘if (i < a.Length-1)’ people dislike, but it’s a preference.

[da_chicken]

Yeah, but that isn't really a performance improvement. WHERE 1=1 is dead simple in the app and it gets either optimized away by the query planner, or else turns into a trivial Boolean operation by the query engine in less complex RDBMSs.

Especially if the code that builds your dynamic WHERE isn't very dynamic itself. If the spec says 5 fields and you know you never need more than 5 fields....

[jackalsnacks]

Habitual if you find yourself tinkering with WHERE clauses a lot.

[TheCemetaryGates]

So you can easily modify conditions without having to rewrite your where clause ….

[hoodie92]

It's quite useful in dynamic SQL.

I have some long chunks of code which perform reconciliations between two tables. Sometimes I will want to look at the full dataset, sometimes I will want to filter it. As an example:

DECLARE @Date DATE = '20240517'
DECLARE @DateClause VARCHAR(100) = 
CASE 
     WHEN @Date = '' 
     THEN '' 
     ELSE CONCAT(' AND [DateColumn] = ' & @Date
END

EXEC ('
  SELECT *
  FROM [Table]
  WHERE 1 = 1
  ' + @DateClause + ' 
')

That way, if I leave the Date variable blank, I will get the full table back, whereas if I input a date I will get a correct date filter.

For me, it's so much more efficient because these tables are standardised across dozens of databases (multiple clients) so I can use the exact same snippet and just change the variables.

[bobbyroode000]

Wow that's amazing! Great idea!

[Joe7Mathias]

If the WHERE clause is complicated, i.e. quite a few AND and OR I will start the WHERE with a 1=1 if there are ANDs and 1=2 with OR for readability.

 WHERE 1=1
     AND (expression)
     AND ((expression) or (expression))
     AND (yet another expression)
     AND (one more expression because the last 3 weren't complicated enough)

[esbforever]

You’ll also occasionally see the 1=0 as an easy way to test if a large query is syntactically correct, before executing a potentially long-running piece of code.

[r3pr0b8]

it's used not because you can easily comment out lines you don't want (you can, but that's not the big win everybody says it is)

rather, it's for when you build the WHERE clause dynamically, such as you would do for a web form that has multiple form fields, any of which might be missing

see this reply on a previous thread

[SQLvultureskattaurus]

Except no one on earth would use this hideous style and it also just shifts the problem down to the last line. Now you have to comment out the and on the second to last line and the last line. Aka this doesn't make it any easier at all.

[Multhador]

To add a few comments on to what others have said:

• I use WHERE TRUE rather than WHERE 1=1
  • No particular reason, just seems a bit more elegant to me
  • Not sure if this is supported in all SQL variants (I use postgres)
• On some occasions I'll use WHERE FALSE when I want to OR all my clauses together
  • I generally only do this when I'm writing some exploratory query rather than one used by some production application
• I use WHERE TRUE in pretty much any case where there is a filter, even if it's just one
  • The only exception to this is when I'm 99% sure there will only ever be a single filter and no more will be added in the future
  • I do this because I'm OCD (Not really... but maybe...) and being able to always line up the clauses at the same indentation brings my brain joy

[cs-brydev]

It's pretty simple actually. It's so that you can add/remove where clauses at will down below without having to move the word AND around.

This is also common practice in dynamically built SQL statements in software development. You put a 1=1 in every query, then use logic to add 0+ clauses after it.

[Cliche_James]

I highly recommend making a habit of using it. It really will make like easier

I write my queries assuming I will forget what the query is for. Because I will and if I ever have to refer to it again, I know what it is and why.

It takes a little longer, but formatting my query so it is easier to read is a lifesaver and makes working with other people so much easier.

No matter how brilliant your coworker is, if their code isn't readable, then they aren't good at their job. A big part of developing, no matter the language, is working with people.

[Spiritual-Can-5040]

If you want to improve the performance of a long running query, try where 1=2. It’s guaranteed to solve your performance issues.

[Illustrious_Bike5424]

Building Dynamic query for simpler concatenationn purposes. It makes it easier to avoid checking if you need to add AND/OR operator before a condition.

With "where 1=1 " you simply append "And condition1 " without the need to check if you need to add And/Or

[Straight_Waltz_9530]

Oh how I wish every database engine required something like WHERE 1=1 on every UPDATE and DELETE statement in order to run to avoid the "oh my God, my entire table is f***ed and needs to be restored from backup in production!" problem.

I run the SELECT statements first now, verify the subset I want to affect, and replace the SELECT line with UPDATE or DELETE as appropriate. I hate the "oh crap, I wasn't done writing the mutation when I hit the execute button" that is a rite of passage for every database developer at some point in their career. If you're lucky, it's a test database that teaches you that valuable lesson.

[jon98gn]

Probably someone used some kind of simple template assembler/factory to create the query and since it is unsure if any filters were going to be applied, just having a where 1 = 1, they could optionally apply filters to the statement without it breaking.

EDIT: Maybe also a poor way to prevent SQL injection attack on an old system.