why we give in query sql 1=0

Question

I have seen in some programmers use 1=0 in where condition for select query , why they use this condition.

Answer 1

in sql server, you can quickly create a copy of a table without any data like this

select * into Newtable
from Oldtable
where 1 = 0

this will create a new table with the same structure as the old table

Another option is to return an empty resultset

Answer 2

The only possible use for that would be to prevent the query from returning any rows. I've done it occasionally for testing "no results found" type logic.

Answer 3

I have seen some utilities use that syntax to gather metadata about a table. They may run a query such as select * from sometable where 1=0 to "efficiently" get the columns from the table without any data.

Answer 4

I've used this in code when generating the SQL based on a checkbox list of values. For example, if I allow the user to pick from a list of fruit:

Apples Oranges Pears

...and they select Apples and Oranges, my pseudo code looks like:

string sqlWhere = "select * from fruit where 1 = 0"
if (FruitList.Any()) {
   sqlWhere += " or code in ("
   foreach (Fruit in FruitList)
      sqlWhere += string.Format("'{0}',");
   /*Add the close parens*/
   sqlWhere = sqlWhere.Substring(0,sqlWhere.Length-2) + ")";
}

which will only return the values the user selected. If they don't select any fruit, then no results are returned.

*Please note this code does not account for SQL Injection. Just an example of why you might see the

where 1 = 0

Answer 5

You could use a query like this as a 'ping' to check if your connection to the database is still open and the database is healthy and so on - if you get no results, it is, but if you get some sort of error, there's a problem. You'd write the query against some sort of system table. Depending on your database, you might be able to do something simpler - in PostgreSQL, you can just say select 1;, and in Oracle, i've seen select 1 from dual;. If your database doesn't have the ability to handle free-floating expressions in selects, and has no equivalent of dual, you could do something like select * from sometable where 0 = 1;.