Expression1 AND Expression2
Assume that you wish to know all the products where productid < 600 and standard cost must be greater than 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 –
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 –
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 –
Combining the AND with OR operators
You can also combine AND with 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
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.
I wish to get the products excluding product id 4, then i can code –
You can use this NOT operator to check for any other fields like date, name etc.