where 1 = 1 or 0 = 0

sql where 1 = 1 0 = 1 and the role of

where 1 = 1; this condition is always True, in the case of variable number of query, 1 = 1 can be easily standardized statement.

First, no where 1 = 1 in a multi-criteria query trouble

For example, if you do the query page, and can have multiple query options, while also allowing users to choose and enter the keyword query, then query by the usual dynamic structure, the code roughly as follows:

string MySqlStr = "select * from table where";

if (Age.Text.Lenght> 0)
{
MySqlStr = MySqlStr + "Age ="+"' Age.Text '";
}

if (Address.Text.Lenght> 0)
{
MySqlStr = MySqlStr + "and Address ="+"' Address.Text '";
}

① kinds of assumptions

Determine if these two IF statements are True, the user inputs a query word, then, the final MySqlStr dynamically constructed statement becomes:

MySqlStr = "select * from table where Age = '18 'and Address =' ​​wide Wenshan County village of wavelet do '"

Can see, this is a complete SQL query correctly, can be executed correctly and in accordance with the existence of the database record and return data.

② kinds of assumptions

IF determine if the above two statements does not hold, then the final MySqlStr dynamically constructed statement becomes:

MySqlStr = "select * from table where"

Now, we look at this statement, because the conditions where words need to use the back, but this statement does not exist conditions, so the statement is a false statement, certainly can not be executed, not only error, but also does not query any data.

The above two assumptions, represent real-world applications, description, statement of construction problems, insufficient to meet the flexible query.

Second, the use of the benefits where 1 = 1

If we change the above statement:

string MySqlStr = "select * from table where 1 = 1";

if (Age.Text.Lenght> 0)
{
MySqlStr = MySqlStr + "and Age ="+"' Age.Text '";
}

if (Address.Text.Lenght> 0)
{
MySqlStr = MySqlStr + "and Address ="+"' Address.Text '";
}

Now, there are two hypotheses

① kinds of assumptions

If the two IF are true, then the statement becomes:

MySqlStr = "select * from table where 1 = 1 and Age = '18 'and Address =' ​​wide Wenshan County village of wavelet do '", it is clear that the statement is a correct statement, correctly implemented, if database record is sure to be queried.

② kinds of assumptions

If the two IF not tenable, then the statement becomes:

MySqlStr = "select * from table where 1 = 1", now we look at this statement, because where 1 = 1 the statement is True, so, which statement is syntactically correct, can be properly implemented, its role rather in: MySqlStr = "select * from table", which returns all the data in the table.

The implication is: If the user in a multi-criteria query page, do not select any field, do not enter any keywords, then, will return to the table all the data; if the user page, select the part of the field and enter part of the query words, then, according to the conditions set by the user query.

Here, do not know whether you have to understand, in fact, where 1 = 1 application, the application is not high, nor is the construction of the so-called intelligent, just more conditions to satisfy the query page the various factors of uncertainty The use of a properly constructed one can run dynamic SQL statements in a way.

where 1 = 0; this condition is always false, the result will not return any data, only the table structure, the table can be used to quickly build

"SELECT * FROM strName WHERE 1 = 0"; the select statement used to read the table without taking into account the structure of data in the table, this saves memory, because you can not save the result set.

create table newtable as select * from oldtable where 1 = 0; create a new table, and the new table structure and query the table structure is the same.

转自：http://www.codeweblog.com/where-1-1-or-0-0/

注：利用create table newtable as select * from oldtable where 1 = 0;创建的新表只是拷贝的旧表的结构，其中的索引是不会拷贝的。在oracle中where 1=0 等价于where rownum = 0.