MS SQL Server: 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

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

For instance, - 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 instance, - 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 instance, - use the NOT operator

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

Next lesson: IS NULL condition in SQL Server