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

SQL Server Where Clause

The SQL Server WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. It is used to extract only those records that fulfill a specified condition

The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement

Where Syntax 

SELECT column1, column2, columnN 
FROM [table_name]
WHERE [condition]

Where condition can also be specified with below operators.

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<>	Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN	Between a certain range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

Where Clause example:

SQL requires single quotes around text values (most database systems will also allow double quotes).

However, numeric fields should not be enclosed in quotes:

USE AdventureWorks2017;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product 
WHERE ProductLine = 'R' AND DaysToManufacture < 4
ORDER BY Name ASC;

SELECT ID, NAME, SALARY 
FROM CUSTOMERS
WHERE SALARY > 2000;