Oracle Magazine - March/April 2012

Code Listing 11: Query using IS NULL operator

SQL> select first_name, last_name, manager 2 from employee

3 where manager IS NULL;

FIRST_NAME LAST_NAME MANAGER ——————————————————————————————————————————— ——————————————————————————————————————————— —————————— Frances Newton Emily Eckhardt

include two more rows, as shown in Listing 13, available in the online version of this article at bit.ly/z3k0qJ.

The online article also includes information and examples on the precedence of the AND and OR logical operators and how grouping expressions in parentheses helps to control the order of SQL operation.

2 rows selected.

Code Listing 12: Query using AND logical operator to combine multiple predicates

SQL> select first_name, last_name, manager, salary 2 from employee

3 where salary > 75000 4 AND manager IS NULL;

FIRST_NAME LAST_NAME MANAGER SALARY ——————————————————————————————— ————————————————————————————— ——————————————————————— ———————— Emily Eckhardt 100000

1 row selected.

EXISTENCE OR ABSENCE OF VALUES

Recall from Part 1 in this series that the absence of a value is referred to as a null value. A null value cannot be equal or unequal to another null value or to any non-null value. Therefore, you must always use the IS NULL or IS NOT NULL comparison operators to evaluate whether a data value is null or not. For example, the query in Listing 11 returns employees who do not yet have an assigned manager.

Note that the DISTINC T keyword (which you learned about in Part 3 of this series) recognizes and returns NULL values:

2 from employee

3 where manager IS NOT NULL;

CONCLUSION

Only rarely will you write a query without a WHERE clause, and this article has shown you how to use the WHERE clause to expand upon simple SQL SELECT statements and filter data of interest to receive a more meaningful result set. You’ve seen how comparison operators are used in conjunction with the WHERE clause to help you specify your desired result. You’ve also seen how logical operators can be used to further filter your data by grouping predicates.

The next installment of SQL 101 will examine the ORDER BY clause of a SQL statement and take a closer look at Oracle’s SQL*Plus tool. 

MANAGER —————————————

28

1 row selected.

Melanie Caffrey is
a senior development
manager at Oracle. She is
a coauthor of Expert
PL/SQL Practices
for Oracle Developers
and DBAs (Apress, 2011) and Expert Oracle
Practices: Oracle Database Administration from
the Oak Table (Apress, 2010).

SQL> select DISTINCT manager 2 from employee;

NEXT STEPS

MANAGER —————————————

28

2 rows selected.

To eliminate null values from a result set derived from a query that uses the DISTINC T keyword in its SELECT list, you can use the IS NO T NULL operator in your WHERE clause:

SQL> select DISTINCT manager

TRU TH IN LOGIC

WHERE clauses with only one predicate are rare. The logical operators AND and OR are used to group multiple predicates contained within the same WHERE clause of a single SQL statement. Each added predicate further filters your result set. If two conditions are combined via the AND operator, both conditions must evaluate to true to produce a result. If two conditions are combined with the OR operator, only one of the conditions needs to evaluate to true to yield a result.

For example, the SQL statement in Listing 12 combines two comparison operators by using the AND logical operator. The result displays employees who do not have an assigned manager (according to the EMPLOYEE table) and whose salary is greater than $75,000.