Use a variety of operators and conditions to write a query
Oracle SQL Operators and Conditions
Question: What is an SQL 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.
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.
Below are a few operators and conditions that you may not have seen and a few operators that are extensions added by Oracle.
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:
The two wildcards available are:
%. The percent sign can be used to represent any number of characters.
_. 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
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.
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
(P.LAST_UPDATE_DATE IS NULL
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
NOT Operator: 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 )
The next course in this series Extended DB Featurescovers operators in more depth. It also covers the standard hierarchy of evaluating comparisons
In the next lesson, you will learn how to write a query using the CONNECT BY feature.
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.