Home > Articles

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

LAB 2.4: The WHERE Clause: Comparison and Logical Operators

Lab Objectives

After this lab, you will be able to:

  • Use Comparison and Logical Operators in a WHERE Clause

  • Use NULL in a WHERE Clause

The WHERE clause, also called the predicate, provides the power to narrow down the scope of data retrieved. In fact, most SQL statements you write will contain a WHERE clause.

Comparison Operators

Comparison operators compare expressions. An expression can be a column of any datatype, a string or text literal (sometimes referred to as a text constant or character literal), a number, or any combination of these. An expression can also be a function or mathematical computation, which you will learn about in Chapter 3, "Character, Number, and Miscellaneous Functions." An expression always results in a value.

Testing for Equality and Inequality

Comparison operators compare one expression with another expression. One of the most commonly used comparison operators is the equal operator, denoted by the = symbol. For example, if you are asked to provide the first name, last name, and phone number of a teacher with the last name of Schorin, you write the following SQL statement:

SELECT first_name, last_name, phone
  FROM instructor
 WHERE last_name = 'Schorin'

FIRST_NAME LAST_NAME PHONE ---------- ---------- ---------- Nina Schorin 2125551212 1 row selected.

Here, the column last_name is the left side of the equation and the text literal 'Schorin' is the right side. Single quotes are used around the text literal 'Schorin'. This statement will only retrieve rows from the instructor table that satisfy this condition in the WHERE clause. In this case, only one row is retrieved.

When you describe the instructor table, you see the datatype of the last_ name column is VARCHAR2. This means the data contained in this column is alphanumeric. When two values are compared to each other, they must be of the same datatype; otherwise, Oracle returns an error. You will learn more about converting from one datatype to another in Chapter 4, "Date and Conversion Functions."

SQL> DESCR INSTRUCTOR
 Name							 Null?	  Type
 ------------------------------- -------- ------------
 INSTRUCTOR_ID  				 NOT NULL NUMBER(8)
 SALUTATION								  VARCHAR2(5)
 FIRST_NAME								  VARCHAR2(25)
 LAST_NAME								  VARCHAR2(25)
 STREET_ADDRESS							  VARCHAR2(50)
 ZIP									  VARCHAR2(5)
 PHONE 									  VARCHAR2(15)
 CREATED_BY						 NOT NULL VARCHAR2(30)
 CREATED_DATE					 NOT NULL DATE
 MODIFIED_BY					 NOT NULL VARCHAR2(30)
 MODIFIED_DATE					 NOT NULL DATE

SQL is case insensitive when it comes to column names, table names, and keywords such as SELECT. (There are some exceptions with regard to column names and table names. For more information see Chapter 11, "Create, Alter, and Drop Tables.") When you compare a text literal to a database column, the case of the data must match exactly. The syntax of the following statement is correct, but it does not yield any rows because the instructor's last name is obviously not in the correct case.

SELECT first_name, last_name, phone

 FROM instructor
 WHERE last_name = 'schorin'

no rows selected

Just as equality is useful, so is inequality.

SELECT first_name, last_name, phone
 FROM instructor
 WHERE last_name <> 'Schorin'
FIRST_NAME LAST_NAME  PHONE
---------- ---------- ----------
Fernand	   Hanks	  2125551212
Tom		   Wojick	  2125551212
...
Marilyn	   Frantzen	  2125551212
Irene	   Willig	  2125551212

9 rows selected.

All rows except the one with the last name of 'Schorin', are retrieved. Inequality can also be expressed with the != notation.

The GREATER THAN and LESS THAN Operators

The comparison operators >, <, >=, and <= can all be used to compare values in columns. In the following example, the >=, or greater than or equal to, operator is used to retrieve a list of course descriptions whose cost is greater than or equal to 1195:

SELECT description, cost
 FROM course
 WHERE cost >= 1195

DESCRIPTION COST ---------------------------- ---- DP Overview 1195 Intro to Computers 1195 ... Database System Principles 1195 PL/SQL Programming 1195 26 rows selected.

The value 1195 is not enclosed in single quotes because it is a number literal.

The Between Comparison Operator

The BETWEEN operator tests for a range of values:

SELECT description, cost
 FROM course
 WHERE cost BETWEEN 1000 AND 1100

DESCRIPTION COST -------------------------------- ---- Unix Tips and Techniques 1095 Intro to Internet 1095 Intro to the Basic Language 1095 3 rows selected.

BETWEEN is inclusive of both values defining the range; the result set includes courses that cost 1000 and 1100 and everything in between. The lower end of the range must be listed first.

If you use iSQL*Plus then your result may look similar to Figure 2.20. Note that the result is identical; the only difference is the formatting.

BETWEEN is most useful for number and date comparisons, but it can also be used for comparing text strings in alphabetical order. Date comparisons are discussed in Chapter 4, "Date and Conversion Functions."

Figure 2.20Figure 2.20 iSQL*Plus result.

The IN Operator

The IN operator works with a list of values, separated by commas, contained within a set of parentheses. The following query looks for courses where the cost is either 1095 or 1595.

SELECT description, cost
 FROM course
 WHERE cost IN (1095, 1595)

DESCRIPTION COST -------------------------------------- ---- Structured Programming Techniques 1595 Unix Tips and Techniques 1095 Intro to Internet 1095 Intro to the Basic Language 1095 4 rows selected.

The LIKE Operator

Another very useful comparison operator is LIKE, which performs pattern-matching using the percent (%) or underscore (_) characters as wildcards. The percent wildcard is used to denote multiple characters, while the underscore wildcard is used to denote a single character. The next query retrieves rows where the last name begins with the uppercase letter S and ends in anything else:

SELECT first_name, last_name, phone
 FROM instructor
 WHERE last_name LIKE 'S%'
FIRST_NAME LAST_NAME PHONE
---------- --------- -----------
Nina 	   Schorin 	 2125551212
Todd 	   Smythe 	 2125551212

2 rows selected.

The % character may be placed at the beginning, end, or anywhere within the literal text, but always within the single quotes. This is also true of the underscore wildcard character, as in this statement:

SELECT first_name, last_name
  FROM instructor
 WHERE last_name LIKE '_o%'
FIRST_NAME 				  LAST_NAME
------------------------- ---------
Tom 					  Wojick
Anita 					  Morris
Charles 				  Lowry

3 rows selected.

The WHERE clause returns only rows where the last name begins with any character, but the second letter must be a lowercase o. The rest of the last name is irrelevant.

NEGATING using NOT

All the previously mentioned operators can be negated with the NOT comparison operator; for example, NOT BETWEEN, NOT IN, NOT LIKE.

SELECT phone
  FROM instructor
 WHERE last_name NOT LIKE 'S%'

In the SQL statement the last_name column used in the WHERE clause doesn't appear in the SELECT list. There is no rule about columns in the WHERE clause having to exist in the SELECT list.

The LIKE operator works well for simple pattern matching. If your pattern is more complex, you may want to consider using Oracle's regular expression functionality discussed in Chapter 15, "Regular Expressions and Hierarchical Queries."

Evaluating Null Values

Recall that NULL means an unknown value. The IS NULL and IS NOT NULL operators evaluate whether a data value is NULL or not. The following SQL statement returns courses that do not have a prerequisite:

SELECT description, prerequisite
 FROM course
 WHERE prerequisite IS NULL

DESCRIPTION PREREQUISITE ---------------------------- ------------ DP Overview Intro to Computers Java for C/C++ Programmers Operating Systems 4 rows selected.

Null values represent the unknown; a null cannot be equal or unequal to any value or to another null. Therefore, always use the IS NULL or IS NOT NULL operator when testing for nulls. There are a few exceptions when nulls are treated differently and a null can be equal to another null. One such example is the use of DISTINCT (see Lab 2.2). You will learn about the exceptions in the treatment of nulls throughout this book.

Overview of Comparison Operators

The comparison operators you have learned about so far are sometimes referred to as predicates or search conditions. A predicate is an expression that results in either a true, false, or unknown value. Table 2.2 provides you with a list of the most common comparison operators. You will learn about additional operators such as EXISTS, ANY, SOME, ALL in Chapter 7, "Subqueries," and the OVERLAPS operator in Chapter 4, "Date and Conversion Functions." All these operators can be negated with the NOT logical operator.

Table 2.2 n SQL Comparison Operators

Comparison Operator

Definition

=

Equal

!=, <>

Not equal

>, >=

Greater than, greater than or equal to

<, <=

Less than, less than or equal to

BETWEEN ... AND ...

Inclusive of two values

LIKE

Pattern matching with wildcard characters % and _

IN ( ...)

List of values

IS NULL

Test for null values


Logical Operators

To harness the ultimate power of the WHERE clause, comparison operators can be combined with the help of the logical operators AND and OR. These logical operators are also referred to as boolean operators. They group expressions, all within the same WHERE clause of a single SQL statement.

For example, the following SQL query combines two comparison operators with the help of the AND boolean operator. The result shows rows where a course costs 1095 and the course description starts with the letter I:

 FROM course
 WHERE cost = 1095
 AND description LIKE 'I%'

DESCRIPTION COST -------------------------------------- ---- Intro to Internet 1095 Intro to the Basic Language 1095 2 rows selected.

With just the = operator in the WHERE clause, the result set contains three rows. With the addition of the AND description LIKE 'I%', the result is further reduced to two rows.

Precedence of Logical Operators

When AND and OR are used together in a WHERE clause, the AND operator always takes precedence over the OR operator, meaning any AND conditions are evaluated first. If there are multiple operators of the same precedence, the left operator is executed before the right. You can manipulate the precedence in the WHERE clause with the use of parentheses. In the following SQL statement, the AND and OR logical operators are combined:

SELECT description, cost, prerequisite
  FROM course
 WHERE cost = 1195
   AND prerequisite = 20
    OR prerequisite = 25

DESCRIPTION COST PREREQUISITE ----------------------------------- ---- ------------ Hands-On Windows 1195 20 Structured Analysis 1195 20 Project Management 1195 20 GUI Programming 1195 20 Intro to SQL 1195 20 Intro to the Basic Language 1095 25 Database System Principles 1195 25 7 rows selected.

The preceding SQL statement selects any record that has either a cost of 1195 and a prerequisite of 20, or just has a prerequisite of 25 no matter what the cost. The sixth row, Intro to the Basic Language, is selected because it satisfies the OR expression prerequisite = 25. The seventh row, Database System Principles, only satisfies one of the AND conditions, not both. However, the row is part of the result set because it satisfies the OR condition.

Here is the same SQL statement, but with parentheses to group the expressions in the WHERE clause:

SELECT description, cost, prerequisite
  FROM course
 WHERE cost = 1195
  AND (prerequisite = 20
 	OR prerequisite = 25)

DESCRIPTION COST PREREQUISITE ----------------------------------- ---- ------------ Database System Principles 1195 25 Hands-On Windows 1195 20 Structured Analysis 1195 20 Project Management 1195 20 GUI Programming 1195 20 Intro to SQL 1195 20 6 rows selected.

The first expression selects only courses where the cost is equal to 1195. If the prerequisite is either 25 or 20, then the second condition is also true. Both expressions need to be true for the row to be displayed. These are the basic rules of logical operators. If two conditions are combined with the AND operator, both conditions must be true; if two conditions are connected by the OR operator, only one of the conditions needs to be true for the record to be selected.

The result set returns six rows instead of seven. The order in which items in the WHERE clause are evaluated is changed by the use of parentheses and results in a different output.

To ensure that your SQL statements are clearly understood, it is always best to use parentheses.

NULLs and Logical Operators

SQL uses tri-value logic; this means a condition can evaluate to true, false, or unknown. (This is in contrast to boolean logic, where a condition must be either true or false.) A row gets returned when the condition evaluates to true. The following query returns rows from the COURSE table starting with the words Intro to as the description and a value equal or larger than 140 in the prerequisite column.

SELECT description, prerequisite
 FROM course
 WHERE description LIKE 'Intro to%'
 AND prerequisite >= 140

DESCRIPTION PREREQUISITE ----------------------------------- ------------ Intro to Programming 140 Intro to Unix 310 2 rows selected.

Rows with a null value in the PREREQUISITE column are not included because null is an unknown value. This null value in the column is not equal or greater to 140. Therefore, the row Intro to Computers does not satisfy both conditions and is excluded from the result set. Following is the list of course descriptions with null values in the PREREQUISITE column. It shows the row Intro to Computers with a null value in the PREREQUISITE column.

select description, prerequisite, cost
 from course
 where prerequisite is null

DESCRIPTION PREREQUISITE COST ---------------------------- ----------------- ---- Operating Systems 1195 Java for C/C++ Programmers 1195 DP Overview 1195 Intro to Computers 1195 4 rows selected.

The AND truth table in Table 2.3 illustrates the combination of two conditions with the AND operator. Only if both conditions are true is a row returned for output. In this example, with the prerequisite being null, the condition is unknown and therefore the row not included in the result. The comparison against a null value yields unknown unless you specifically test for it with the IS NULL or is not operators.

Table 2.3 n AND Truth Table

AND

TRUE

FALSE

UNKNOWN

TRUE

TRUE

FALSE

UNKNOWN

FALSE

FALSE

FALSE

FALSE

UNKNOWN

UNKNOWN

FALSE

UNKNOWN


For the OR condition, just one of the conditions needs to be true. Again, let's examine how nulls behave under this scenario using the same query, but this time with the OR operator. The Intro to Computers course is now listed because it satisfies the 'Intro to%' condition only. In addition, you will notice that rows such as DB Programming in Java do not start with the 'Intro to' as the description, but satisfy the second condition, which is a prerequisite of greater or equal to 140.

SELECT description, prerequisite
 FROM course
 WHERE description LIKE 'Intro to%'
 OR prerequisite >= 140

DESCRIPTION PREREQUISITE ----------------------------------- ------------ DB Programming in Java 350 Database Design 420 Internet Protocols 310 Intro to Computers Intro to Internet 10 Intro to Java Programming 80 Intro to Programming 140 Intro to SQL 20 Intro to Unix 310 Intro to the Basic Language 25 JDeveloper Techniques 350 Oracle Tools 220 Structured Programming Techniques 204 13 rows selected.

Table 2.4 shows the truth table for the OR operator; it highlights the fact that just one of the conditions need be true for the row to be returned in the result set. It is irrelevant if the second condition evaluates to false or unknown.

Table 2.4 n OR Truth Table

OR

TRUE

FALSE

UNKNOWN

TRUE

TRUE

TRUE

TRUE

FALSE

TRUE

FALSE

UNKNOWN

UNKNOWN

TRUE

UNKNOWN

UNKNOWN


When you negate a condition with the NOT operator and the value you are comparing against is a null value, it also results in a null (see Table 2.5). The following query demonstrates that none of the null prerequisites are included in the result set.

SELECT description, prerequisite
 FROM course
 WHERE NOT prerequisite >= 140

DESCRIPTION PREREQUISITE ----------------------------------- ------------ Intro to Internet 10 GUI Programming 20 Intro to SQL 20 Hands-On Windows 20 Structured Analysis 20 Project Management 20 Intro to the Basic Language 25 Database System Principles 25 PL/SQL Programming 80 Intro to Java Programming 80 Intermediate Java Programming 120 Advanced Java Programming 122 JDeveloper 122 JDeveloper Lab 125 Basics of Unix Admin 130 Network Administration 130 Advanced Unix Admin 132 Unix Tips and Techniques 134 18 rows selected.

Table 2.5 n NOT Truth Table

NOT

TRUE

FALSE

UNKNOWN

NOT

FALSE

TRUE

UNKNOWN


Lab 2.4 Exercises

2.4.1 Use Comparison and Logical Operators in a WHERE Clause

  1. Write a SELECT statement to list the last names of students living either in zip code 10048, 11102, or 11209.

  2. Write a SELECT statement to list the first and last names of instructors with the letter "i" (either uppercase or lowercase) in their last name living in the zip code 10025.

  3. Does the following statement contain an error? Explain.

  4. SELECT last_name
      FROM instructor
     WHERE created_date = modified_by
  5. What do you observe when you execute the following SQL statement?

  6. SELECT course_no, cost
      FROM course
     WHERE cost BETWEEN 1500 AND 1000
  7. Execute the following query and determine how many rows the query returns.

  8. SELECT last_name, student_id
      FROM student
     WHERE ROWNUM <= 10

2.4.2 Use NULL in a WHERE Clause

  1. Write a SELECT statement to list descriptions of courses with prerequisites and cost less than 1100.

  2. Write a SELECT statement to list the cost of courses without a prerequisite; do not repeat the cost.

Lab 2.4 Exercise Answers

2.4.1 Answers

  1. Write a SELECT statement to list the last names of students living either in zip code 10048, 11102, or 11209.

  2. Answer: The SELECT statement selects a single column and uses the IN comparison operator in the WHERE clause.

    SELECT last_name
      FROM student
     WHERE zip IN ('10048', '11102', '11209')
    LAST_NAME
    ---------------
    Masser
    Allende
    Winnicki
    Wilson
    Williams
    McLean
    Lefkowitz
    
    7 rows selected.

The statement can also be written using the equal operator (=), in combination with the logical operator OR, and yields the same result set:

SELECT last_name
  FROM student
 WHERE zip = '10048'
 	OR zip = '11102'
 	OR zip = '11209'

There will be times when a SELECT statement can be written more than one way. The preceding statements are logically equivalent.

  1. Write a SELECT statement to list the first and last names of instructors with the letter "i" (either uppercase or lowercase) in their last name living in the zip code 10025.

  2. Answer: The SELECT statement selects two columns and uses the LIKE, =, and the AND and OR logical operators, combined with parentheses, in the WHERE clause.

    SELECT first_name, last_name
      FROM instructor
     WHERE (last_name LIKE '%i%' OR last_name LIKE '%I%')
     AND zip = '10025'
    FIRST_NAME 				  LAST_NAME
    ------------------------- ---------
    Tom						  Wojick
    Nina					  Schorin
    
    2 rows selected.

The LIKE operator must be used twice in this example because there is no way of knowing whether there is an upper or lowercase 'i' anywhere in the last name. You must test for both conditions, which cannot be done using a single LIKE operator. If one of the OR conditions is true, the expression is true.

If you need to search for the actual % symbol within a column value, you can use a SQL function or an escape character. You learn more about this in Chapter 3, "Character, Number, and Miscellaneous Functions."

  1. Does the following statement contain an error? Explain.

  2. SELECT last_name
      FROM instructor
     WHERE created_date = modified_by

    Answer: Yes. The two columns in the WHERE clause are not the same datatype and the Oracle database returns an error when this statement is executed.

You will get an error similar to the following when you execute the statement.

SQL> SELECT last_name
 2     FROM instructor
 3    WHERE created_date = modified_by
 4  /
 WHERE created_date = modified_by
					  *
ERROR at line 3:
ORA-01858: a non-numeric character was found where a numeric was expected

There are times when the datatypes of columns do not agree and you need to convert from one datatype to another. You will learn about these circumstances in Chapter 4, "Date and Conversion Functions." (In this exercise example, data conversion is not fruitful because the data in these two columns is of a very different nature.)

  1. What do you observe when you execute the following SQL statement?

  2. SELECT course_no, cost
     FROM course
    WHERE cost BETWEEN 1500 AND 1000
    no rows selected

    Answer: The query returns no rows. Although there are courses that cost between 1000 and 1500, the BETWEEN clause requires the lower end of the range to be listed first. If the query is rewritten as follows, it returns rows.

    SELECT course_no, cost
     FROM course
    WHERE cost BETWEEN 1000 AND 1500

BETWEEN AND TEXT LITERALS

As mentioned previously, BETWEEN is most often used for numbers and dates, which you will learn about in Chapter 4, "Date and Conversion Functions." You can apply the BETWEEN functions to text columns as you see in the next example, which utilizes the BETWEEN operator with text literals W and Z. The query lists the student's ID and the last name. Notice any students whose last name begins with the letter "Z" are not included, because the STUDENT table has no student with a last name of the single letter "Z". If a student's last name was spelled "waldo", this student would not be included in the result, because the WHERE clause is only looking for last names that fall between the uppercase letters of W and Z.

SELECT student_id, last_name
 FROM student
 WHERE last_name between 'W' AND 'Z'
STUDENT_ID LAST_NAME
---------- ---------
 142 	   Waldman
...
 241 	   Yourish

11 rows selected.

If you are looking for "waldo", regardless of the case, use the OR operator to include both conditions.

SELECT student_id, last_name
  FROM student
 WHERE last_name between 'W' AND 'Z'
    OR last_name BETWEEN 'w' AND 'z'

Here is another example of how you can use the BETWEEN and the >= and <= operators with text literals.

SELECT description
  FROM grade_type
 WHERE description between 'Midterm' and 'Project'

This would be equivalent to:

SELECT description
 FROM grade_type
 WHERE description >= 'Midterm'
 AND description <= 'Project'
DESCRIPTION
----------------
Midterm
Participation
Project

3 rows selected.
  1. Execute the following query and determine how many rows the query returns.

  2. SELECT last_name, student_id
      FROM student
     WHERE ROWNUM <= 10

    Answer: The query returns ten rows. The WHERE clause uses the pseudocolumn ROWNUM, which restricts the result to the first ten or less rows. A pseudocolumn is not a real column that exists on a table; you can select the column, but you cannot manipulate its value.

    LAST_NAME                 STUDENT_ID
    ------------------------- ----------
    Kocka 						230
    Jung 						232
    Mulroy 						233
    Brendler 					234
    Carcia 						235
    Tripp 						236
    Frost 						237
    Snow 						238
    Scrittorale 				240
    Yourish 					241
    
    10 rows selected.

The next statement shows the value of the ROWNUM pseudocolumn column in the SELECT list. The first row displays the ROWNUM value of 1,the second the ROWNUM value of 2, and so on. The ROWNUM pseudocolumn is useful if you want to limit the number of rows returned by a query. You will see additional examples of this and other pseudocolumns in subsequent chapters.

SELECT ROWNUM, last_name, student_id
  FROM student
 WHERE ROWNUM <= 10
 ROWNUM 	LAST_NAME 				 STUDENT_ID
---------- ------------------------- ----------
 1 		   Kocka					 230
 2		   Jung						 232
 3		   Mulroy					 233
 4		   Brendler					 234
...
 9 		   Scrittorale				 240
 10 	   Yourish					 241

10 rows selected.

2.4.2 Answers

  1. Write a SELECT statement to list descriptions of courses with prerequisites and cost less than 1100.

  2. Answer: The SELECT statement uses the IS NOT NULL and less than (<) comparison operators in the WHERE clause.

    SELECT description, cost, prerequisite
      FROM course
     WHERE prerequisite IS NOT NULL
      AND cost < 1100 
     DESCRIPTION 							COST PREREQUISITE
     -------------------------------------- ---- ------------
     Intro to Internet 						1095 10
     Intro to the Basic Language 			1095 25
     Unix Tips and Techniques 				1095 134 
    
    3 rows selected.

Both conditions need to be true for the row to be returned. If the one of the conditions is not met, the row simply is not selected for output.

  1. Write a SELECT statement to list the cost of courses without a prerequisite; do not repeat the cost.

  2. Answer: The SELECT statement selects a single column in combination with DISTINCT, and uses the IS NULL comparison operator in the WHERE clause.

    SELECT DISTINCT cost
      FROM course
     WHERE prerequisite IS NULL
    

    COST ---------- 1195 1 row selected.

Lab 2.4 Self-Review Questions

In order to test your progress, you should be able to answer the following questions.

  1. Comparison operators always compare two values only.

    1. True

    2. False

  2. The BETWEEN operator uses a list of values.

    1. True

    2. False

  3. The following statement is incorrect:

  4. SELECT first_name, last_name
      FROM student
     WHERE employer = NULL
    1. True

    2. False

  5. The following statement is incorrect:

  6. SELECT description
      FROM course
     WHERE cost NOT LIKE (1095, 1195)
    1. True

    2. False

  7. The following statement is incorrect:

  8. SELECT city
      FROM zipcode
     WHERE state != 'NY'
    1. True

    2. False

  9. The following statement returns rows in the STUDENT table where the last name begins with the letters SM.

  10. SELECT last_name, first_name
      FROM student
     WHERE last_name = 'SM%'
    1. True

    2. False

Answers appear in Appendix A, Section 2.4.

  • + Share This
  • 🔖 Save To Your Account