IN conditions in SQL Server

The IN condition is used in SQL Server (Transact-SQL) to minimize the need to use too many OR conditions in SELECT, INSERT, UPDATE, or DELETE statements.

Syntax of IN conditions

 biểu thức IN (giá trị 1, giá trị 2, … giá trị n); 

Variable name or variable value

expression

Value to check

value 1, value 2, . value n

Values ​​to check with expressions

Note

1. The IN condition in SQL Server will return records when the expression has a value of 1, value 2, . or n value.
2. The IN condition in SQL Server is also called the IN operator.

For example - with string value

 SELECT  * 
 FROM nhanvien 
 WHERE ho I N ('Smith', 'Anderson', 'Johnson'); 

The result will be rows from the table if the employee's surname is Smith, Anderson or Johnson. Due to using * in the SELECT statement, all fields in the table of contents will be in the result set.

The above example is similar to the SELECT command below.

 SELEC T * 
 FROM nhanvien 
 WHERE ho = 'Smith' 
 OR ho = 'Anderson' 
 OR ho = 'Joh nson'; 

Using the IN condition helps the command look shorter and easier to understand.

For example - with numerical values

 SELEC T * 
 FROM nhanvien 
 WHERE nha nvien_id IN (1, 2, 3, 4, 10); 

The returned result is the employee whose ID is 1, 2, 3, 4 or 10. The above command is equivalent to the following command.

 SELECT  * 
 FROM nhanvien 
 WHERE nhanvien_id = 1 
 OR nhanvien_id = 2 
 OR nhanvien_id = 3 
 OR nhanvien_id = 4 
 OR nhanvien_i d = 10; 

For example - use the NOT operator

 SELE CT * 
 FROM nhanvien 
 WHERE t en NOT IN ('Sarah', 'John', 'Dale'); 

In the above example, the result set consists of rows from the table that have the employee name other than Sarah, John or Dale. Sometimes it is easier to find values ​​that are not values ​​you want. The above example is equivalent to the command below.

 SELECT * 
 FROM nhanvien 
 WHERE ten <> 'Sarah' 
 AND ten <> 'John' 
 AND ten <> 'Dale'; 

Previous article: DISTINCT clause in SQL Server

Next lesson: IS NULL condition in SQL Server