What does WHERE 1=1 do?

1

Hello!

I have seen WHERE 1=1, what does it mean?
Also, is AND NOT Col1 IN ('a','b') is the same as AND Col1 NOT IN ('a','b') ?

Thanks!

2

hi

i did some testing ..

it appears that both are the same

* insert into #sampledata select 'a'
* insert into #sampledata select 'b'
* insert into #sampledata select 'c'
* insert into #sampledata select 'd'
* go 
* select 'sample data', * from #sampledata 
* select 'AND NOT Col1 IN (''a'',''b'')', * from #sampledata where 1=1 AND NOT Col1 IN ('a','b')
* select 'AND Col1 NOT IN (''a'',''b'') ?', * from #sampledata where 1=1 AND Col1 NOT IN ('a','b') 

drop table #sampledata 
create table #sampledata
(
col1 varchar(10) 
)

image

3

Some developers like to add WHERE 1 = 1 as a default so they have a true condition. You also see this a lot with generated code...this helps build the WHERE clause so each additional condition only needs to include the 'AND' conditions as the clause is built up.

Others include that so it is easier to highlight just that section - but I find it to just clutter up a query with useless extras that don't really do anything.

As for using NOT vs NOT IN...that depends on the actual criteria. In the simple case - they are equivalent...but it really depends.

1 Like
4

Yes, they function the same. But never use the first style:

NOT Col1 IN ('a','b')

It's just needlessly confusing (as proven by the fact that a q here was needed to see if it functioned the same as the standard code. Stick with the standard and more straightforward:

Col1 NOT IN ('a','b')

1 Like