Logical Operators (AND, OR, NOT)

In this very important lesson, we are going to look again at the logical operators.

You might remember that we talked about ANDs and ORs in the lesson about more complex where conditions, right? but I’m going to clarify a couple of things about them here, and besides that, I’m going to introduce you to the NOT operator.

So, the AND and OR operators are used to CONNECT two conditions, to create a bigger one.  You have seen several examples of them at this point, but I left something very important in “pending” state, which is the explanation about how they actually work and the results they produce depending on the values of the sub-conditions they connect.

You probably don’t need an explanation about them, because to know what the results are going to be you just apply simple logic.  The kind of logic you use in every-day life.

Let me illustrate it with an example.

The AND Operator

Let’s talk about your cell phone.  You know that in order to be able to make a normal phone call, it needs to be turned on, and it has to have a SIM card or any kind of chip that makes it connect to a phone company.

So, being turned on and being connected to a phone company network are the 2 conditions needed to be able to make a call.  These conditions are connected by an AND in this case.

Okay.  If your phone is turned on, but it doesn’t have a SIM card, Can you make a call?  You know the answer, right?  You don’t need to know some rule about it.

Now, if the phone has a SIM card, but it is not turned on.  Can you make a call?  No!

And, if the phone doesn’t have a SIM card and it is not turned on.  Can you make a call?

Of course not.

So, when can you make a call?

The answer to “Can I make a call?” will be TRUE, or YES, only when both sub-conditions are true

And that is the only “rule” you need to know, but as I said a before, that is not a rule, that’s simple every-day logic.

Now, let’s talk about OR.

The OR Operator

Let’s use a laptop computer as an example.

In order to be able to turn it on, it has to have some kind of power source, right?  We can turn it on if it is connected to a power outlet or if it has enough charge in the battery to make it work.  In this case, these conditions are connected with an OR.

So, if it is connected to the power outlet, but the battery is not charged.  Can you turn it on?

Yes.

And if it is not connected to the power outlet, but the battery is charged, can you turn it on?

Yes, you can.

And if it is connected, and also the battery is charged, can you turn it on?

Yes, of course.

So, when is it not possible to turn it on?

Only when both conditions are false, that is, when it is not connected to the power outlet, and the battery is not charged.

So, to summarize:  The result of 2 conditions connected with an AND is true only when both conditions are true.  In every other situation, the result is false.

And the result of 2 conditions connected with an OR is false only when both conditions are false.  In every other situation, the result is true.

Now, what about NOT?

The NOT Operator

It is used to negate or invert or reverse the logical value of a condition.

So, if the condition evaluates to TRUE, NOT converts it to FALSE, and if it evaluates to FALSE, NOT converts it to TRUE.

And it can also be used with LIKE, IN and BETWEEN.

I’m going to show you with some examples:

If I write a query like this one:

SELECT *
FROM PRODUCTS
WHERE price > 10;

It returns the only product whose price is greater than 10, so if I add a NOT to the condition in line 3 the result is the opposite. It gives me the products whose price is NOT greater than 10.

And if I have this query:

SELECT *
FROM PRODUCTS
WHERE product_name LIKE '%llin';

It gives me these products:

And if I add a NOT before the LIKE keyword, it gives the opposite result:

And I could also put the NOT before the condition, like below, and the result would be the same:

SELECT *
FROM PRODUCTS
WHERE NOT product_name LIKE '%llin';

And I can also write conditions like:

...
WHERE PRODUCT_ID not in ()
AND product_id NOT between  and ;

And I can also apply the NOT operator to a composite condition! 😎

Let me show you.

If I write the following query:

SELECT *
FROM PRODUCTS
WHERE price < 10 and product_id > 1;

I get these 2 rows:

But if I want to invert the whole condition, I can enclose it in parenthesis and add the NOT operator before it, and I will get all of the rows that DO NOT comply with the whole condition.

SELECT *
FROM PRODUCTS
WHERE NOT(price < 10 and product_id > 1);

Now, let me change something.  I’m going to remove the parenthesis, so the query now looks like this:

SELECT *
FROM PRODUCTS
WHERE NOT price < 10 AND product_id > 1;

The results are now different.  Do you know why?

Operator Precedence

I’m going to conclude this lesson with an explanation about something that is called Operator Precedence.

Precedence is the importance or priority, that is given to operators when conditions are evaluated.

Operators precedence comes into play when we don’t specify the order in which conditions must be evaluated by using parentheses.

In the query of the last example, we have 2 logical operators, which are the NOT in line 3 and the AND in line 4.

If we don’t specify that the AND must be evaluated first by enclosing the condition in parentheses, then the order is decided based on the operators’ precedence, and among logical operators, NOT is the one with the highest precedence or priority.

So, in this case, the NOT is applied first, and it is applied to the condition that is next to it, and then after the NOT is applied, the AND operator is applied to the two resulting conditions.

But if we add the parentheses around the condition, as I initially did, we are telling the database that we want the AND to be evaluated first, and then we want to apply the NOT to the result.

Does it make sense?

You might remember that in the lesson about complex where conditions, I mentioned that when 2 or more conditions were at the same level, they were evaluated in order, from left to right.  But that is only true when the operators have the same precedence.

In that lesson, I mentioned that ANDs and ORs were evaluated in order when parentheses were not involved, but that is actually not true.

I said that at that moment because I didn’t want to confuse you or complicate things more than necessary, but the truth is, that AND and OR don’t have the same precedence, and thus, they are not evaluated in order.

Among these two, AND is the one with more precedence, so if you have several ANDs and ORs and no parentheses, ANDs are evaluated first.

So, to summarize, this is the precedence of the logical operators:

• The one with the highest precedence is the NOT operator.
• After NOT, the next one with higher precedence is AND.
• And finally, OR.

Got it?

Ok, here is your task for this lecture.

Practice Challenge

Write a query to list all employees who were born before 01-jan-1980 or after 01-jan-1995 and earn more than 2000 a month, and whose name does not start or end with an “N”.

 

When evaluating the condition about how much they earn, please take into account the BONUS column too.

Expected Output:

Were you able to solve it?

Great!

Let’s move to the following lesson!