Exam Topic: Restricting and Sorting Data – Limit the rows that are retrieved by a query

Returning the right data by restricting rows to answer questions is at the heart of SQL Development. As shown in The Capabilities of a SQL SELECT Statement, the WHERE clause is used to specify conditions for which rows should be returned. If the condition is true, the row is returned. This is the SELECT capability known as selection.

Let’s say this is your data:

CUSTOMER_ID    FIRST_NAME     LAST_NAME    STATE
-------------  ------------   ------------ --------
1              YNGIWULF       ORMWYRD      FL
2              FITHINDEL      ORMWYRD      VA
3              THYRI          RAVENCHILDE  VA
4              TERRA          ROSE         FL
5              FIONA          RISE         MS

We want customers from Florida. A “blind” query, such as select * from customers; will return all of the data shown and we can count the ones with “FL” for STATE. But what happens when we have hundreds or thousands of rows? It is no longer practical to count them up by hand.

Here comes the WHERE clause!

SQL> SELECT first_name, last_name FROM customers WHERE state = 'FL';

FIRST_NAME     LAST_NAME
------------   ------------
YNGIWULF       ORMWYRD
TERRA          ROSE

Notice the condition in the WHERE clause is an equality. Any operator can be used in a WHERE clause:

We can return rows where state does not equal FL. Not Equal can be expressed as either <> or !=.

SQL> SELECT first_name, last_name FROM customers WHERE state != 'FL';

FIRST_NAME     LAST_NAME
------------   ------------
FITHINDEL      ORMWYRD
THYRI          RAVENCHILDE
FIONA          RISE

Any condition can be used in a WHERE clause can be used to control what is returned as long as it can evaluate to true or false. Here are a few examples.

SQL> SELECT first_name, last_name 
2  FROM customers
3  WHERE (CASE WHEN first_name != 'FITHINDEL' THEN 1 ELSE 0 END) = 1;

SQL> SELECT first_name, last_name
2  FROM customers
3  WHERE first_name = 'THYRI' or last_name = 'ORMWYRD';

SQL> SELECT first_name, last_name
2  FROM customers
3  WHERE state = 'V' || 'A';

SQL> SELECT first_name, last_name
2  FROM customers
3  WHERE state=chr(86) || chr(65);

The IN Set Comparison Operator

The IN operator allows you to enter a list of values to be compared. This list is a set and the comparison is between the set of data queried and the set listed in the set list. If any value in the IN statement is a match, the row is returned. It can also be used as NOT IN.

SQL> SELECT first_name, last_name
2  FROM customers
3  WHERE state IN ('VA','FL');

The IN condition would be equivalent to the following query. Using the IN simplifies the query and avoids the use of ORs.

SQL> SELECT first_name, last_name
2  FROM customers
3  WHERE state = 'VA' or state = 'FL';

The LIKE Condition

The LIKE condition allows for partial matches using wildcards. It can also be used as NOT LIKE.

The “all” wildcard for Oracle is the percent sign (%). It is a wildcard for as many characters as exist.

SQL> SELECT first_name, last_name
2  FROM customers
3  WHERE last_name LIKE 'ORM%';

FIRST_NAME     LAST_NAME
------------   ------------
YNGIWULF       ORMWYRD
FITHINDEL      ORMWYRD

The partial match of ORM finds both customers with the last name of ORMWYRD.

The “single character” wildcard for Oracle is the underscore (_). It allows for one character to be wild. Everything else must match.

SQL> SELECT first_name, last_name
2  FROM customers
3  WHERE last_name like 'R_SE';

FIRST_NAME     LAST_NAME
------------   ------------
TERRA          ROSE     
FIONA          RISE    

Note: LIKE conditions can be performed on number datatypes. Oracle will cast the number as a string to perform the match.

The TO_DATE Function

When comparing dates, you should always convert your strings to dates. This will be the case any time the column in the WHERE clause is a DATE datatype or a TIMESTAMP datatype. To do this, use the TO_DATE function.

Consider the following table. When querying ORDER_DATE, convert the right side of the condition to a date using TO_DATE. See Gotchas with DATEs for more information.

SQL> DESC orders;

 Name                                      Null?    Type
 ----------------------------------------- -------- -----------
 ORDER_ID                                           NUMBER(38)
 CUSTOMER_ID                                        NUMBER(38)
 PRODUCT_ID                                         NUMBER(38)
 ORDER_DATE                                         DATE

SQL> SELECT order_id
2  FROM orders
3  WHERE order_date = TO_DATE('01-JAN-2015');

The BETWEEN…AND Condition

Numerical, character and date data can be filtered for ranges.

If we want to search to see if a value falls between two numbers, we can use the BETWEEN condition.

SQL> select 'true' from dual where 4 between 1 and 4;
'TRU
----
true

BETWEEN can be used to find a value between two characters, as well, like looking  through a phone book or dictionary.

SQL> select 'true' from dual where 'apple' between 'ape' and 'art';

'TRU
----
true

If we want to search orders that have happened two weeks ago, we can filter for a range between two dates. This would be equivalent to running a query WHERE order_date >= sysdate - 14 and order_date <= sysdate - 7. (To see more about intervals, see INTERVAL: Clock Math.)

SQL>  SELECT order_id
2  FROM orders
3  WHERE order_date BETWEEN trunc(sysdate)-14 
4  AND trunc(sysdate-7)+interval '0 23:59:59' day to second;

Numbers can also be queries using BETWEEN…AND.

The IS NULL Condition

Nulls are funny things. Null can never equal anything, not even another Null. Null is an unknown value, so even if two things are unknown, they can still, in reality, have two different values once they are known. Because of this, something is either null or not null.

To search for this value, use IS NULL or IS NOT NULL. Anytime an insert is performed and a value is not supplied for a column, that column is populated with a Null by default.

SQL> SELECT order_id
2  FROM orders
3  WHERE order_date IS NULL;

Boolean Operators

A WHERE clause can have multiple conditions. They are combined using the operators AND and OR. For the following examples, we’ll use a pseudo-table of information we know about states.

AND means that all conditions must be true for the row to be returned.

SQL> select state_name from state 
2  where location = 'SOUTH' AND capital = 'TALLAHASSEE';

FLORIDA

OR means that one of the conditions included must be true for the row to be returned.

SQL> select state_name from state 
2  where state_name like 'NEW%' OR state_name LIKE 'NORTH%';

NEW HAMPSHIRE
NEW JERSEY
NEW MEXICO
NEW YORK
NORTH CAROLINA
NORTH DAKOTA

NOT can be used to return the opposite of what is stated. WHERE NOT (order_id = 4) would return all but order_id 4.

When combining conditions, put conditions separated by OR in parentheses together. Otherwise they will combine unexpectedly with other conditions.