The roles of where 1 = 1 and 0 = 1 in SQL

Where 1 = 1 & where 0 = 1 is often seen in SQL statements. After seeing it, I don't know what it means. I don't know the role of this statement, here I will give you one by one ......

Where 1 = 1; this condition is always True. In the case of an indefinite number of query conditions,1=1You can easily standardize statements.

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 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 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";
  
  
 
  
  
//SelectThe statement is mainly used to read the structure of the table without considering the data in the table. This saves the memory, because you do not need to save the result set.
  
  
 
  
  
Create TableNewtableAs Select*FromOldtableWhere1 = 0;
  
  
 
  
  
// Create a new table with the same structure as the queried table.
 
 

The above section briefly introduces the roles of where 1 = 1 and 0 = 1 in SQL.

Source: http://www.cnblogs.com/junyuz/archive/2011/03/10/1979646.html

1. SQL WHERE clause
2. SQL Where clause
3. Roles of the where 1 = 1 Statement in SQL
4. Some suggestions for setting search values in the WHERE statement
5. Oracle Where clause research conclusion