Operators (Transact-SQL)

Null Operator :
It is a very special operator in Sql server.The NULL operator is used to compare a value with NULL value.

Ex:
Select * from employee
where comm is NULL;
The above statement only return those many employees are not getting commission from company.

NOT Operator:
It is used for negative statement.
The NOT operator reverses the meaning of the logical operator with which it is used.

Ex:
Select * from employee
where comm IS NOT NULL;
The above statement only return those many employees are getting commission from company.

EXISTS:
The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.

Ex:
IF EXISTS (Select 1 from employee where name like '%N%')
Block 1
ELSE
Block2

IN:
The IN operator is used to compare a value to a list of literal values that have been specified.

Ex:
Select * from employee
where Location IN ('Bangalore', 'Bhubaneswar', 'Mumbai');
The above statement only return those many employees are working under Bangalore or Bhubaneswar or Mumbai.

BETWEEN:
The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.

Ex:
Select * from employee
where salary between 30000 and 70000;

NOTE:

SET ANSI_NULLS ON/OFF
Specifies ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values

Ex:
SET ANSI_NULLS OFF;
SELECT * from employee
WHERE comm <>  NULL;