SQL AND, OR, NOT Operator with example

 The WHERE clause can be combined with AND, OR and NOT operators

The AND and OR operators are used to filter records based on more than one condition:

• The AND operator displays a record if all the conditions separated by AND are TRUE.
• The OR operator displays a record if any of the conditions separated by OR is TRUE.
• The NOT operator displays a record if the condition(s) is NOT TRUE.

Lets go through one by one.

SQL AND Operator:

When multiple conditions are joined using AND operator, only those rows will be fetched from the database which meets all the conditions.

AND Syntax:

SELECT column_name1, column_name2, ...
FROM [table_name]
WHERE condition_1 AND condition_2 ...

	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

AND Operator Example:

SELECT BusinessEntityID,JobTitle,MaritalStatus FROM
    HumanResources.Employee
WHERE
    MaritalStatus = 'M'
AND JobTitle='Marketing Specialist'

Result:

SQL OR Operator:

When multiple conditions are joined using OR operator, all those rows will be fetched from the database which meet any of the given conditions.

SQL OR Operator Syntax:

SELECT column_name1, column_name2, ...
FROM [table_name]
WHERE condition_1 OR condition_2 ...

	TRUE	FALSE
TRUE	TRUE	TRUE
FALSE	TRUE	FALSE

Example:

SELECT BusinessEntityID,JobTitle,MaritalStatus FROM
    HumanResources.Employee
WHERE
    MaritalStatus = 'M'
AND JobTitle='Marketing Specialist'

Result

SQL NOT Operator

When multiple conditions are joined using OR operator, all those rows will be fetched from the database which meet any of the given conditions.

Syntax:

SELECT column_name1, column_name2, ...
FROM [table_name]
WHERE NOT CONDITION

Example:

SELECT BusinessEntityID,JobTitle,MaritalStatus FROM
    HumanResources.Employee
WHERE
   NOT MaritalStatus = 'M'

Result