SQL Logical operators - AND, OR, NOT

There are 3 main Logical operators in SQL. These are –

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;

Note If you have to check the same field for multiple values with OR operator, then there is a better way of achieving the same result using the IN 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.

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;

Note If AND with OR operators are present, then SQL server will first process the AND operator and then executes the OR operator.

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';

SQL Logical operators – AND, OR, NOT

AND

OR

Combining the AND with OR operators

NOT

Example

Tutorials for all brains!