Select Statement  «Prev  Next»

Lesson 2 Operators and conditions
Objective Use a variety of operators and conditions to write a query.

Oracle SQL Operators and Conditions

Operators


What is an operator? An operator is simply a method of comparing or combining two expressions. The plus sign and the equals sign are simple examples of operators. You have already used many operators in your queries when you have worked with SQL.

Conditions

A condition is a phrase within the WHERE clause that determines whether a row is chosen. A condition is made up of a pair of expressions with a comparison operator between them. The following MouseOver shows a few examples of operators and conditions.

  1. This condition means "is not equal".
  2. This is the addition operator for adding two values together.
  3. This condition means the CUST_ID is not found in the list generated by the sub-query.
  4. This condition means the CUST_ID is not found in the list generated by the sub-query.
  5. This condition compares a column to characters. The percent sign is a wild card.
  6. This condition compares the LAST_UPDATE_DATETIME column value with an expression that subtracts three days from the SYSDATE pseudocolumn. SYSDATE contains the current date and time.
SELECT * FROM PRODUCT 
WHERE PRODUCT_ID <> 25

SELECT TAX_AMOUNT + TOTAL_ITEM_AMOUNT
FROM CUSTOMER_SALE
WHERE CUST_ID
NOT IN (SELECT CUST_ID
	FROM CUSTOMER
	WHERE LASTNAME LIKE 'S%')

SELECT CREATED_BY_USER 
FROM PET_CARE_LOG
WHERE LAST_UPDATE_DATETIME > SYSDATE -3

PL/SQL Operators, Conditions
Below are a few operators and conditions that you may not have seen and a few operators that are extensions added by Oracle.

LIKE

The LIKE operator, when combined with two wildcards, allows you to compare a value with a partial value. For example, let us say that you know your customer's last name begins with L, but you cannot recall how the rest of the name is spelled. To list all customers whose last names begin with the letter L, you would use this query:

The results look like this:
SELECT FIRSTNAME, LASTNAME
FROM CUSTOMER
WHERE LASTNAME LIKE 'L%'

The two wildcards[1] available are:
  1. %. The percent sign can be used to represent any number of characters.
  2. _. The underscore represents a single character.

You can use both wildcards within a single expression if you wish. In fact, you can use either wildcard more than one time within the same expression.
You can concatenate them to a column, so that the expression evaluated contains data from a column. For example, imagine that you want to know which of your customers have the same first letter in the first and last names (such as Lois Lane), the query would be:

SELECT FIRSTNAME, LASTNAME
FROM CUSTOMER
WHERE LASTNAME LIKE SUBSTR(FIRSTNAME,1,1) || '%'

Notice that the WHERE clause uses the substring function , SUBSTR, to strip off the first letter of the first name. Then the literal, ‘%’ was concatenated (||) to this expression. If the person's first name was Sammy, the expression in the where clause would become ‘S%’.
The following graphic shows the results of the query.

Results of the Like Substring

IS NULL

The IS NULL comparison allows you to determine whether an expression contains a null value. For example, you can find all the rows that contain null values in the PACKAGE_ID column by using this query:

PACKAGE_ID IS NULL

You can also use IS NULL to compare (indirectly) two expressions that are both null. Look at a simple phrase such as:
P.LAST_UPDATE_DATE = PCL.LAST_UPDATE_DATETIME
If both values happen to be null values, the comparison is false.
What if you want to include rows where both values are null? The following set of comparisons does the trick:
(P.LAST_UPDATE_DATE  = PCL.LAST_UPDATE_DATETIME
 OR
(P.LAST_UPDATE_DATE IS NULL 
  AND 
  PCL.LAST_UPDATE_DATETIME IS NULL))

Enclose the entire set of comparisons in parentheses, to ensure correct sequence of evaluation.
You can use IS NOT NULL to determine that an expression does not contain a null value. For example, to check the LAST_UPDATE_DATE use this code:
P.LAST_UPDATE_DATE IS NOT NULL
The next course in this series covers operators in more depth. It also covers the standard hierarchy of evaluating comparisons.

NOT

Reverse the logic of any comparison or group of comparisons by adding NOT and enclosing the comparison in parentheses.
You can place NOT at the beginning of any simple comparison:

NOT TOTAL_ITEM_AMOUNT > 30 

Oracle Exclusive

Within Oracle, you can use parentheses to group more than one comparison together, place NOT in front of the whole thing, and reverse the resulting logic. For example, here is a comparison that finds rows where total sales are less than $45 and the subtotal of items is over $30.

TOTAL_ITEM_AMOUNT > 30 AND TOTAL_SALE_AMOUNT < 45
If you want to see sales that do not fit these criteria, add the NOT and place all in parentheses:
NOT ( TOTAL_ITEM_AMOUNT > 30 AND TOTAL_SALE_AMOUNT 
< 45 ) 

In the next lesson, you will learn how to write a query using the CONNECT BY feature.
[1] Wildcard: A symbol that is used for pattern matching when using the LIKE operator. For example, % and _ are the two wildcards used in Oracle.
The % wildcard can match any number of characters. The _ wildcard can match any single character.