Sql And Or

In Sql And Or, we show you how to deal with complex condition in an Sql Where.


Two or more conditions can be joined together using the logical operator AND, OR and NOT. They are called boolean operators.

The AND operator returns results only when all of the conditions are true.
The OR operator returns results when any of the condition is true.
The NOT operator negates an expression.

Operators Precedence

To have a better understanding of Sql And Or, you need to know the Operator Precedence. There are two operators, arithmetic and logical operators. When these operators occur in the same statement, the arithmetic operators are handled before the logical operators. This is the hierarchy:

Operator Precedence

Operators Hierarchy

The syntax of AND:

WHERE  condition1 AND condition2

If both, condition1 and condition2, are true then the result is true

The syntax of OR:

WHERE  condition1 OR condition2

If either, condition1 or condition2, is true then the result is true

The syntax of NOT:

WHERE  NOT(condition)

If condition is false, then the result is true

Or a combination

WHERE  condition1 AND condition2 OR condition3 OR NOT(condition4)

We need to know the operators hierarchy to figure out the result. This is how to evaluate it:

NOT(condition4)=Result1
condition1 AND condition2=Result2
Result2 OR condition3=Result3
Result3 OR Result1=Where clause result

It is better to use parentheses in a complex conditions so that you can avoid misinterpretation.

Truth Tables
To evaluate the logical operators we use a mathematical table called truth table.The Sql And Or is all about this truth table. The condensed form of the table is as follow:

AND
ANDFT
F FF
T FT
 
OR
ORFT
F FT
T TT
 

Example:

Let's use the following Menu table to illustrate.

MenuIdMenuPrice
2.1Singapore Fried Noodle9.00
2.2Beef Fried Soy Noodle8.50
2.3Claypot Noodle11.00
2.4Salted Fish Fried Noodle9.50
2.5Fried Koay Teow10.50

Your task is to answer this question: Is there any dish that cost between $9 and $10? Although that task is better handled by Sql Between, we are going to use AND operator.

SQL Statement:

SELECT *
FROM Menu
WHERE  Price >= 9 AND Price <= 10

Result:

MenuIdMenuPrice
2.1Singapore Fried Noodle9.00
2.4Salted Fish Fried Noodle9.50

Interesting example of Sql And Or
If you want to know any dishes that cost less than $9, as well as any dished with the price greater than $10, how do you construct the Sql?. The question suggests the use of operator AND, but because you want to find values in two different categories of the same field then you need to use OR.

Sql-Or

Two different categories

SQL Statement:

SELECT *
FROM Menu
WHERE  Price < 9 OR Price > 10

Result:

MenuIdMenuPrice
2.2Beef Fried Soy Noodle8.5
2.3Claypot Noodle11
2.5Fried Koay Teow10.5

Combination of AND and OR
Supposed you want to find out any dishes that cost between $8 and $9, as well as any dished with the price between $10 and $11. Here we also have two categories of the same field, but now in each category there is an enclosed boundary. The presence of enclosed boundary dictates the use of AND, and joining with other category we use operator OR.

Sql-And-Or

Two different categories with boundary

SQL Statement:

SELECT *
FROM Menu
WHERE  Price >= 8 AND Price <= 9
  OR Price >= 10 AND Price <= 11

I left the parentheses out to show the operators precedence once more time.

Result:

MenuIdMenuPrice
2.1Singapore Fried Noodle9
2.2Beef Fried Soy Noodle8.5
2.3Claypot Noodle11
2.5Fried Koay Teow10.5

You need to understand all the above Sql And Or examples and try them yourself, as it is often confusing when to use AND and when to use OR when joining two conditions together.

Quick link. From here, you can learn the following:

Sql Select
The core of Sql, selecting data from table.

Sql Distinct
Eliminating duplicate values in the output.

Sql Where
Conditionally selecting rows from table.

Sql Like
Pattern searching for conditions in Where or Having clause

Sql Between
Added to conditions to show results between two values.

Sql In
It enables us to specify multiple values, either as a list or a subquery.

Sql Count
Counting number of rows.

Sql Sum
Getting the sum of a field

Sql Max
This function will return the highest value.

Sql Min
This function will return the lowest value.

Sql Avg
This function will return the average value.

Sql Order By
Sorting the output

Sql Group By
Grouping rows into sets.

Sql Having
To filter the result of aggregation

Journey
Buy at AllPosters.com
A bend in the road is not the end of the road... unless you fail to make the turn

Positive Resources

All of us have at least two or three things that excite us. We could talk about these subjects for hours. Good news!. Did you know that Site Built It! can turn this type of deep interest and enthusiasm into a revenue-generating business?

Review some of the Site Build It! case studies and you'll absolutely discover something that ignites more positive ideas into your mind.

SBI! Video Tour


Invest the most
important 30 minutes
you will spend online.

Click, sit back, and
understand why SBI!
makes you succeed.