1

Can anyone please explain Why we are using where 1=1 in SQL select query?

Select * from <TableName>
Where 1=1
<cfif isdefined('Something)>
AND  columnName = value
</cfif>
Rajesh Manilal
  • 1,104
  • 9
  • 20

2 Answers2

9

Tools often use this to let them append AND some_other_condition to the query, without having to worry whether this is the first predicate or not.

If you write

SELECT * FROM A

...and want to add a predicate you need to append "WHERE C1=1", but then adding a second predicate would append "AND C2=1"

But if you write 

SELECT * FROM A WHERE 1=1

...you can just append "AND C1=1" for the first, and "AND C2=1" for the second

mc110
  • 2,825
  • 5
  • 20
  • 21
3

I have seen and used 'Where 1 = 0' to just create a new table with the same schema but without any data. But this one escapes me. The where clause is just not required here and the query would give the same result with or without the where clause.

anonxen
  • 794
  • 1
  • 8
  • 24
  • 1
    This is a comment rather than an answer. You need to wait until you have 50 reputation to comment on posts other than your own. – JJJ Jul 03 '14 at 11:26
  • 1
    I am a new here and it does not help that you people keep bringing me down to zero !! – anonxen Jul 03 '14 at 11:31
  • If you post comments as answers, they will be downvoted and eventually deleted. You need to post actual answers until you have enough reputation to post comments; before that you just can't comment. – JJJ Jul 03 '14 at 11:33
  • I vote it up, his answer helps me know other aspect as well about 1 = 0 – CleanBold Jul 03 '14 at 11:44
  • 1
    I'm voting it up because I'm a bad Stack Overflower. The other use of where 1 = 0 is that it enables you to follow it with "or this = that" conditions. – Dan Bracuk Jul 03 '14 at 12:28
  • *the query would give the same result with or without the where clause* Not quite. Due to the `cfif`, the actual sql generated depends on whether or not the `something` variable exists. If it does exist, the sql becomes `..WHERE 1 = 1 AND columnName = value`, which is not the same as `...WHERE 1 = 1`. – Leigh Jul 03 '14 at 12:49
  • @DanBracuk Thank you for explaining the use of 1 = 0 too – Rajesh Manilal Jul 03 '14 at 15:24
  • @anonxen Thank u too for making a start on the concept of 1 = 0. – Rajesh Manilal Jul 03 '14 at 15:26