More Complex WHERE Conditions

In this lesson, you will start learning how you can write more complex where conditions.

In the previous lesson, I mentioned that the WHERE clause was comprised of the WHERE keyword and ONE condition, and that was not a mistake or an over-simplification.  That is in fact what happens.

You can have a very complex WHERE clause with lots of conditions connected by ANDs and ORs, but even though all those smaller conditions are evaluated individually at the beginning, the evaluation process ends with only one condition which needs to evaluate to true for the row to be included in the result set.

I’m going to show you how this process works now.

Take a look a this query:

SELECT *
FROM products
WHERE
(
  product_id < 5 OR (product_name = 'Amoxicillin' and price > 10)
)
AND
(
  price <= 10
  OR expiration_date = date '2016-04-30'
);

When The database is evaluating if a row is to be included in the result set, all the text in the WHERE clause (lines 4 to 12) forms the one condition that must evaluate to true, but the process starts with the sub-conditions that are nested at the deepest levels, which are determined by the  parentheses.

Order of Evaluation of Conditions

So, in this case, the sub-condition in line 6 is the one evaluated first, because the pairs of parentheses that start in lines 4 and 9 are at the same level but the one in line 6 is deeper.

So, the sub-condition in line 6 needs to be evaluated first, but it actually includes 2 smaller sub-conditions, which are, number one: product_name equal to ‘Amoxicillin’, and number two: price greater than 10.

Since those 2 sub-conditions are at the same level, any of them could be evaluated first. That is up to the database.

The image below indicates the order in which conditions are evaluated in this case.




The conditions marked with red are the ones evaluated first.  Since they are at the same level, Oracle decides the order of evaluation.

Then, all of the conditions marked with blue have to be evaluated, individually.

As you see, the individual sub conditions in line 6 is evaluated first, and then, in the next step, the logical result of them is calculated, which is at the same level than the other conditions marked with blue.

Then, the result of each pair of the conditions in blue is evaluated.  Each of these pairs is now a bigger condition marked with green.

And finally, the logical result of the two green sub-conditions, now enclosed in orange, becomes the final condition that must evaluate to true for the row to be returned by the query.

And Oracle follows the same order of evaluation for every row in the table.

Now just a small clarification:  I said TRUE and might sometimes say FALSE to explain how the evaluation process works, but you cannot use TRUE and FALSE in SQL directly, because there is no LOGICAL datatype in SQL.  Conditions are evaluated logically, and thus they result in true or false internally, but you cannot use those values directly.

So, now you know how the evaluation process works, and you know that at the end of the day, everything is reduced to one single condition, which must evaluate to true for the row to be included in the result set.  It can seem somehow complicated by now, but it is actually very simple once you are used to thinking this way.

In the next lesson, you are going to learn about another way to filter your results, and in the next section, we are going to look a bit deeper into how this TRUEs and FALSEs are evaluated from the logical standpoint.

Alright, now here is your task.

Practice Challenge

The company is planning to give some employees a special gift, but they will consider only employees that work in the accounting and marketing departments.  For employees of those departments, the condition is that they must earn less than 3000 a month, or have been born before year 1985.  Your task consists in displaying the list of employees that can participate.

Expected Output:

Don’t continue reading if you have not solved the problem.

…
…
…

Good to see you are back!  Did you have any problems?

This time I’m going to solve the problem here, so that you can confirm if your approach was correct.

Just keep in mind that most of the times, a problem can have more than one correct solution, so, if your query doesn’t look the same as mine, it doesn’t necessarily mean it is incorrect.

For now, what matters is the result, so, just keep an eye on the results I get, and check yours to see if they match.

Solution to the Challenge

I’m going to include all of the columns, because there wasn’t any specification about the columns to include, so…

SELECT *
FROM employee

Ok, The first restriction in the problem description is that only employees from Accounting and Marketing can participate, so, we need a condition like this one:

SELECT *
FROM employee
WHERE department_id = 1 or department_id =2

Besides being from any of those departments, the other condition is that they earn less than 3000 OR were born before 1985, so, we need this other condition

SELECT *
FROM employee
WHERE department_id = 1 or department_id =2
  AND salary < 3000 or birthdate <  DATE ‘1985-01-01’

Easy, right?

But let’s run it.

Here is the result I got:

Accounting is department 1 and marketing is department 2, but I listed employees from departments 3 and 4 too, so, something is wrong with my query, because the company wants to include only employees from Accounting and Marketing.  They made it very clear.

So, what’s wrong?

The first condition mentioned in the problem description is the one about the departments, and that is an absolute condition that is not related to the other ones mentioned later.  That condition must be met, and only if that happens, the other conditions come into play, so, we need to enclose it in parenthesis, and this point we have:

SELECT *
FROM employee
WHERE (department_id = 1 or department_id =2)
  AND salary < 3000 or birthdate <  DATE '1985-01-01';

Now, the condition about making less than 3000 is not totally independent.  It is related to the one about the birthdate, because it is one OR the other, so, we need to enclose those two in parentheses also, and we have this:

SELECT *
FROM employee
WHERE (department_id = 1 or department_id =2)
  AND (salary < 3000 or birthdate <  DATE '1985-01-01');

And now we have two bigger conditions that must be met.  Employees must meet the compound condition in line 3 AND the one in line 4 to be included.

So, if I run this version of the query, I get the correct results.

Is that what you got also?

Please don’t continue to the next lesson until you understand how this problem was approached.

Okay, see you next in the next lesson.