*AND**OR**NOT*

## AND

**Syntax**

Expression1 AND Expression2

Assume that you wish to know all the products where productid < 600 and standard cost must be greater than 50.

SELECT ProductId, Name, StandardCost, ListPrice, SafetyStockLevel FROM Production.Product WHERE ProductID < 600 AND StandardCost > 50;

In the above query, if you also want to add another condition that the safety stock level should fall between 500 and 1200, then I can code like –

SELECT ProductId, Name, StandardCost, ListPrice, SafetyStockLevel FROM Production.Product WHERE ProductID < 600 AND StandardCost > 50 AND SafetyStockLevel BETWEEN 500 AND 1200;

## OR

**Syntax**

Expression1 OR Expression2

Now, I wish to know the products where productid is either less than 600 or standard cost > 50, then i can code like –

SELECT ProductId, Name, StandardCost, ListPrice, SafetyStockLevel FROM Production.Product WHERE ProductID < 600 OR StandardCost > 50;

Here, the SQL server engine will show all those records in output where any one of the expressions is true or both the expressions are true.

You can have multiple OR operators in a SQL depending on your requirement. Suppose, you want to get the products where product id is either 1, 10, 15 or 20.

Then you can code –

SELECT ProductId, Name, StandardCost, ListPrice, SafetyStockLevel FROM Production.Product WHERE ProductID = 1 OR ProductID = 10 OR ProductID = 15 OR ProductID = 20;

**operator. I will show you how easy it is to write the same query with**

*IN***operator when we will learn the**

*IN***operator. I do not want to confuse you now so I will show you the**

*IN***operator after 3-4 articles.**

*IN*## Combining the AND with OR operators

You can also combine AND with OR operator-

SELECT ProductId, Name, StandardCost, ListPrice, SafetyStockLevel FROM Production.Product WHERE ProductID = 800 OR ProductID < 600 AND StandardCost > 50 AND SafetyStockLevel BETWEEN 500 AND 1200;

**with**

*AND***operators are present, then SQL server will first process the**

*OR***operator and then executes the**

*AND***operator.**

*OR*Here, first it will evaluate the products where productid is less than 600 and standard cost is greater than 50 and safety stock level between 500 and 1200. So this is the first expression which will be evaluated.

The other expression is ProductID = 800 and it will be evaluated next. So, if any one of the expressions are true or both are true, then it will process those records in the output.

So, we understood that AND is evaluated before the OR operator.

Now, if you want to get the products where the first expression is that ProductID must be either 800 or it is less than 600 then it should be our first expression. Please note that it uses OR operator here.

The next expression is Standard cost > 50 and safety stock level between 500 and 1200. So, this is expression 2

SELECT ProductId, Name, StandardCost, ListPrice, SafetyStockLevel FROM Production.Product WHERE (ProductID = 800 OR ProductID < 600) AND StandardCost > 50 AND SafetyStockLevel BETWEEN 500 AND 1200;

Expression 1 and 2 are combined using this ** AND** operator which means both the expressions must be true and that is the reason product id 800 is excluded in the output because it fails to satisfy the expression 2.

## NOT

**Syntax**

NOT Expression1

I wish to get the products excluding product id 4, then i can code –

SELECT ProductId, Name, StandardCost, ListPrice, SafetyStockLevel FROM Production.Product WHERE NOT ProductID = 4;

You can use this NOT operator to check for any other fields like date, name etc.

## Example

SELECT ProductId, Name, StandardCost, ListPrice, SafetyStockLevel FROM Production.Product WHERE NOT Name = 'Headset Ball Bearings';