The Basics of SQL and Oracle Databases

This chapter is from the book

Oracle SQL by Example, 3rd Edition

Learn More Buy

This chapter is from the book

This chapter is from the book 

Oracle SQL by Example, 3rd Edition

Learn More Buy

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.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

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

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

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

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.

SELECT last_name
  FROM instructor
 WHERE created_date = modified_by

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

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

5. Execute the following query and determine how many rows the query returns.

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.

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.

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.

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."

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

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.)

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

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.

5. Execute the following query and determine how many rows the query returns.

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.

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.

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

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:

SELECT first_name, last_name
  FROM student
 WHERE employer = NULL

1. True

2. False

4. The following statement is incorrect:

SELECT description
  FROM course
 WHERE cost NOT LIKE (1095, 1195)

1. True

2. False

5. The following statement is incorrect:

SELECT city
  FROM zipcode
 WHERE state != 'NY'

1. True

2. False

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

SELECT last_name, first_name
  FROM student
 WHERE last_name = 'SM%'

1. True

2. False

Answers appear in Appendix A, Section 2.4.