SQL where 1 = 1 and 0 = 1 (Multi-condition query error), sqlwhere

SQL where 1 = 1 and 0 = 1 (Multi-condition query error), sqlwhere

Where 1 = 1; this condition is always True. In the case of an indefinite number of query conditions, 1 = 1 can be a convenient standard statement.

I. Do not use where1 = 1Troubles in Multi-condition Query

For example, if you query a page and have multiple options to query, you can also select and enter the query keyword on your own, the code is 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'";
}

① Assumptions

IF both of the above two IF judgment statements are True, that is, IF you have entered query words, the final MySqlStr Dynamic Construction statement becomes:

MySqlStr = "select * from table where Age = '18'And Address = 'wavelet village, guangnan County, Wenshan Prefecture, Yunnan Province '"

It can be seen that this is a complete and correct SQL query statement that can be correctly executed and data is returned based on whether the database has records.

② Assumptions

IF the above two IF judgment statements are not true, the final MySqlStr Dynamic Construction statement becomes:

MySqlStr = "select * from table where"

Now, let's take a look at this statement. Because the where keyword must be followed by conditions, but this statement does not have any conditions, this statement is an incorrect statement and cannot be executed, not only is an error reported, but no data is found.

The preceding two assumptions represent the actual application. They indicate that the statement structure is not sufficient to cope with flexible and variable query conditions.

Ii. Use where1 = 1Benefits

If we change the preceding statement:

String MySqlStr = "select * from table where1 = 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 assumptions:

① Assumptions

IF both of the IF statements are true, the statement becomes:

MySqlStr = "select * from table where1 = 1 and Age = '18'And Address = 'xiaobo youcun, guangnan County, Wenshan Prefecture, Yunnan Province '". Obviously, this statement is a correct statement that can be correctly executed. If the database has records, it will certainly be queried.

② Assumptions

IF neither of the two IF statements is true, the statement becomes:

MySqlStr = "select * from table where 1 = 1". Now, let's look at this statement. Because where 1 = 1 is a True statement, the syntax of this statement is correct, it can be correctly executed, and its function is equivalent to: MySqlStr = "select * from table", that is, returning all data in the table.

The implication is: if you do not select any fields or enter any keywords on the Multi-condition query page, all data in the table will be returned. If you are on the page, if some fields are selected and some query keywords are entered, the query is performed based on the conditions set by the user.

Speaking of this, I don't know whether you understand it. In fact, the application where 1 = 1 is neither an advanced application nor an intelligent construction, it is just a method used to construct a dynamic SQL statement that can run correctly to meet the uncertainties in the Multi-condition query page.

Where 1 = 0; this condition is always false, and no data is returned. Only the table structure is available for quick table creation.

"SELECT * FROM strName WHERE 1 = 0"; this select statement is mainly used to read the table structure without considering the data in the table. This saves the memory, because you do not need to save the result set.

Create table newtable as select * from oldtable where 1 = 0; create a new table with the same structure as the queried table.

What are the differences between SQL query conditions where 1 = 1, 1 = 2 and 1 = 0?

When the database is querying, it is often seen that some people use the where 1 = 1 and 1 = 2 queries, such conditions before execution, true or false IS calculated. 1 = 2 IS actually interpreted as (null is not null) true, false will not scan mainly because some programmers try to piece together dynamic SQL statements. If they are not used well, it will play a side effect. It is based on some of my habits, it is to avoid the syntax error caused by the first word after the where keyword is "and" directly, to append and... convenience for program logic processing.

SQL query condition where 1 <> 2

<> 2 is always true, so all records can be found.
2: The con variable is a string of query conditions. The query conditions are different Based on Different status values. The return con clause should look like it is in a function. The implementation process of this function is to return different query condition strings Based on Different status values and combine them into an executable SQL statement, if you do not guess correctly, return con is required.

Contact you for two questions. I guess most of them are based on different choices on the interface, and data needs to be retrieved based on different conditions, so there will be a sentence 1 <> 2 to retrieve all records, then select the appropriate data based on the different options.