MS SQL Server: Conditions NOT in SQL Server

The NOT condition in SQL Server (Transact-Server) is also called the NOT operator, which helps negate conditions in SELECT, INSERT, UPDATE, and DELETE statements.

Syntax of NOT condition

 NOT '?i?u ki?n' 

Variable name or variable value

condition

Conditions must be negative.

Note

The NOT condition requires that the record be contrary to the 'condition' to be returned in the result set.

Example - combined with IN conditions

 SELE CT * 
 FROM nhanvien 
 WHERE te NOT IN ('John', 'Dale', 'Susan'); 

This example will return all rows from the nhanvien table whose name is not John, Dale or Susan. Sometimes finding values that are not worth looking for is faster.

For instance, - combine condition IS NULL

This is an example of a combination of NOT and IS NULL conditions in SQL Server.

 SEL ECT * 
 FROM nhanvien 
 WHERE ho IS NOT NULL; 

The returned result is all records in the table nhanvien that they contain no NULL value.

Example - combine LIKE condition

Combine LIKE and NOT to find values against what you want.

 SELECT nhanvien_id, ho, ten 
 FROM nhanvien 
 WHERE ho NOT LIKE 'A%'; 

When placing the NOT operator before the LIKE condition, the result will be for employees whose last name does not start with the letter 'A'.

For instance, - combine BETWEEN conditions

 SELEC T * 
 FROM nhanvien 
 WHERE nh anvien_id NOT BETWEEN 200 AND 250; 

In this example, the returned result is the rows in the employee table that have nanvien_id not in the range of 200 and 250, including both the first and last values. It will be equivalent to the SELECT command below.

 SELEC T * 
 FROM nhanvien 
 WHERE nhanvien_id < 200 
 OR nhanvien _id> 250; 

For example - combine EXISTS conditions

 SELE CT * 
 FROM nhanvien 
 WHERE NOT EXISTS (SELECT * 
  FROM danhba 
  WHERE nhanvien.ho = danhba.ho 
  AND nhanvi en.ten = danhba.ten); 

Next lesson: ALIAS in SQL Server