Skip to content Skip to main navigation Skip to footer

Logical Comparisons and Precedence Rules

Objectives

This tutorial covers the following objectives:

  • Evaluate logical comparisons to restrict the rows returned based on two or more conditions
  • Apply the rules of precedence to determine the order in which expressions are evaluated and calculated

Purpose

  • Not too many things in life depend on just one condition.
  • For instance, if you want to go to college, you probably need good grades and the tuition money to pay for it.
  • If you have extra money, you could either save it or spend it.
  • If you want to go to a movie, you may not want to go this weekend and you may not want to sit in the first 10 rows of the theater.
  • In SQL, it is often desirable to be able to restrict the rows returned by a query based on two or more conditions.
  • As the manager of a fast food business, you may need to know the names of your staff who are either cooks or order takers.
  • You don’t need or want the entire staff list, you just want a subset of it.
  • Conditional operators such as AND, NOT, and OR make these types of requests easy to do.

Logical Conditions

  • Logical conditions combine the result of two component conditions to produce a single result based on them.
  • For example, to attend a rock concert, you need to buy a ticket AND have transportation to get there.
  • If both conditions are met, you can go to the concert.
  • What if you can’t get transportation, can you go?
  • Another logical condition combines two component conditions with OR.
  • All employees will receive a raise either by having a perfect attendance record OR by meeting their monthly sales quota.
  • If an employee meets either of these two conditions, the employee gets a raise.

Logical Operators

  • A logical operator combines the results of two or more conditions to produce a single result.
  • A result is returned ONLY IF the overall result of the condition is true.
  • AND — Returns TRUE if both conditions are true.
  • OR — Returns TRUE if either condition is true.
  • NOT — Returns TRUE if the condition is false.

AND Operator

  • In the query below, the results returned will be rows that satisfy BOTH conditions specified in the WHERE clause.
SELECT last_name, department_id, salary
FROM employees
WHERE department_id > 50 AND salary > 12000;
AND Operator
  • Another example of using AND in the where clause.
SELECT last_name, hire_date, job_id
FROM employees
WHERE hire_date > '01-Jan-1998' AND job_id LIKE 'SA%';
Using AND in the where clause.

Reminder

Dates must be entered in single quotes, and the default format is dd-Mon-yyyy

OR Operator

  • If the WHERE clause uses the OR condition, the results returned from a query will be rows that satisfy either one of the OR conditions.
  • In other words, all rows returned have a location_id of 2500 OR they have a manager_id equal to 124.
SELECT department_name, manager_id, location_id
FROM departments
WHERE location_id = 2500 OR manager_id=124;
OR Operator

Rules of Precedence or What Happens First?

  • Consider the following SELECT statement.
  • In what order are the expressions evaluated and calculated?
SELECT last_name||' '||salary*1.05 As "Employee Raise"
FROM employees
WHERE department_id IN(50,80) AND first_name LIKE 'C%'
OR last_name LIKE '%s%';
  • Luckily, when things get this complicated, SQL has a few basic rules that are easy to follow.
  • Notice that the AND operator is evaluated before the OR operator.
  • This means that for the example above, if either of the conditions in the AND statement are not met, then the OR operator is used to select the rows.
  • This is an important concept to remember.
Rules of Precedence or What Happens First?
  • First, the AND condition is evaluated, so all employees working in dept 80 or 50, AND who have a first name starting with “C” are returned.
  • The OR clause is then evaluated and returns employees whose last name contains “s”.
SELECT last_name||' '||salary*1.05
As "Employee Raise",
department_id, first_name
FROM employees
WHERE department_id IN(50,80)
AND first_name LIKE 'C%'
OR last_name LIKE '%s%';
Rules of Precedence or What Happens First?

Employee Curtis Davis is in department 50 and his first name starts with a “C”, so is returned by the AND clause.

The OR then returns any employees whose last name contains the letter “s”.

If either of the conditions in the AND statement are not met, then the OR condition would have to evaluate to true to return any rows.

  • In this example, the order of the OR and AND have been reversed from the previous query.
The order of the OR and AND have been reversed
  • Adding parenthesis changes the way the Where clause is evaluated, and the rows returned.
The order of the OR and AND have been reversed

Terminology

Key terms used in this tutorial included:

  • AND
  • OR
  • NOT
  • Precedence Rules

Summary

In this tutorial, you should have learned how to:

  • Evaluate logical comparisons to restrict the rows returned based on two or more conditions
  • Apply the rules of precedence to determine the order in which expressions are evaluated and calculated

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *