T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL - AND, OR, & NOT Operators



The T-SQL (Transact-SQL) WHERE clause conditions can be combined using AND, OR, and NOT operators. These operators are used to handle more than one conditions.

  • AND - Used to combine multiple conditions and displays a record if all the conditions separated by AND operator are true.
  • OR - Used to combine multiple conditions and displays a record if any of the conditions separated by OR operator is true.
  • NOT - Used to display a record where the specified condition is not true.

Syntax

The syntax for using these operators in T-SQL (Transact-SQL) are given below:

/* AND Operator */
SELECT column1, column2, column3, ...
FROM table_name
WHERE condition1 AND condition2 AND condtion3 ....;

/* OR Operator */
SELECT column1, column2, column3, ...
FROM table_name
WHERE condition1 OR condition2 OR condtion3 ....;

/* NOT Operator */
SELECT column1, column2, column3, ...
FROM table_name
WHERE NOT condition;

Example:

Consider a database table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • AND Example: To select all records of the Employee table where Salary is greater than 2800 and Age is less than 30, the query is mentioned below:

    SELECT * FROM Employee
    WHERE Salary > 2800 AND Age < 30;
    

    This result of the following code will be:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    5RameshNew Delhi283000
  • OR Example: To select all records of the Employee table where Salary is greater than 2800 or Age is less than 25, the query is given below.

    SELECT * FROM Employee
    WHERE Salary > 2800 OR Age < 25;
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    2MarryNew York242750
    4KimAmsterdam303100
    5RameshNew Delhi283000
  • NOT Example: To select all records of the Employee table where City is NOT "New Delhi", the query is given below.

    SELECT * FROM Employee
    WHERE NOT City = 'New Delhi';
    

    This result of the query is shown below:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    2MarryNew York242750
    3JoParis272800
    4KimAmsterdam303100
    6HuangBeijing282800