Smart Pandian

Its everywhere 1 is equal to 1, unless one does a incorrect math.  You may wonder “why would one write an extra condition in a SQL which doesn’t make any difference, and that adds burden to oneself and to others with wierd extra condition?”

Consider the following SQL,

select * from dept where 1=1 and id = 5000;

This sql query is, of course same as

select * from dept where id = 5000;

Basically, this condition “1=1″ is used by lazy programmers, who wants to create an sql on the fly,i.e, dynamically through scripting, For example, Consider the below vb script.

strSqlStatement =  "Select * from Debt where 1=1"
For Each Obj in Conditions
strSqlStatement = strSqlStatement & " and " & Obj
Next

This is a lazy approach, as programmer could have identified the first condition and prefixed it with “where” , instead of adding “and” to every conditions, i.e. as following script.

strSqlStatement =  "Select * from Debt "
i = 0
For Each Obj in Conditions
If i  = 0  then
strSqlStatement = strSqlStatement & " where " & Obj
Else
strSqlStatement = strSqlStatement & " and " & Obj
End If
i = i + 1
Next

Similar to “where 1=1″, there is another lazy programmer approach, its “where 1=2″. The purpose of “where 1=2″ is to extract the list of column names in a table, and dynamically create “CREATE ” or “INSERT ” sql statements without fetching a record. Similar to the previous, There were better ways to do the same.

Ref:
* http://www.novicksoftware.com/TipsAndTricks/tip-sql-server-where-1-equals-2.htm
* http://stackoverflow.com/questions/242822/why-would-someone-use-where-11-and-conditions-in-a-sql-clause

No related posts.