WHERE CLAUSE

The WHERE clause is used to filter data to rows that meet a condition or set of conditions. Rows are often evaluated for conditions using comparison operators, logical operators, or both. On rarer occasions, methods for validating a value exists or is known for an attribute across rows are employed. Our exploration of the WHERE clause will begin by introducing the types of operators commonly used in SQL through simple examples. These examples will build onto your existing knowledge of the SELECT and FROM clauses. I will then showcase more complex examples of the WHERE clause incorporating operands or things involved in the testing of some condition.

Before reading further, consider reviewing the Example Data section below to familiarize yourself with the data being used throughout the examples on this page. If you are already familiar with the data, then skip to the Comparison Operators section

Example Data
The schemas, corresponding tables, and data used in the example problems can be found at livesql.oracle.com. To more easily follow the examples covered on this page, consider first reading the table descriptions below and reviewing each table’s contents. To review the data maintained in each table, click on the icon next to the table name. To hide the data, click the icon again.

Comparison Operators
Based on my experiences in teaching SQL fundamentals, comparison operators are the most easily understood by those beginning to learn SQL. This is likely because most of us will have seen and used these operators in mathematics and spreadsheet software (e.g., Microsoft Excel). Below is a table of comparison operators and their plain English translation.

Let’s put an operator to work by filtering to those products in the product information table with a category ID value of 12. For this example, we’ll retrieve the product ID, product name, and category ID attributes. See the code and corresponding output below.

SELECT
	oe.product_information.product_id,
    oe.product_information.product_name,
    oe.product_information.category_id
FROM
	oe.product_information
WHERE
    oe.product_information.category_id = 12;

In the output above, only those products (9) with a product category of 12 are included. Why? Let’s break the example code down to acquire a better understanding.

In the example code, the operand on the left side of the comparison operator is the category ID attribute. On the right side of the comparison operator is the integer value, 12. Each row’s category ID value is compared to that value. Where the category ID is equal to 12 the condition evaluates to TRUE and the row is included in the output. For those rows where the category ID is not equal to 12 or is unknown, the condition evaluates to FALSE and is not included in the output. If requirements change, say, the interest is now in those products with a category value not equal to twelve, then the equals comparison operator can simply be swapped for the not equal to comparison operator (i.e., !=).

Let’s look at another example, but one where the comparison being made involves an attribute of character or string data type instead of numeric. Suppose there is a need to retrieve the order ID, order date, and order total for all orders made online. Using the orders table, the attribute “order_mode” can be used to apply this filter. See the example code and corresponding output below.

SELECT
	oe.orders.order_id,
	oe.orders.order_date,
	oe.orders.order_total
FROM
	oe.orders
WHERE
	oe.orders.order_mode = 'online';

In the output above, only those orders (32) with an order mode value of “online” are included. Notice, I’ve intentionally not included the order mode attribute in the output. I did this for two reasons. One, to showcase attributes used in the WHERE clause are not required to also be included in the SELECT clause. And two, in most cases, there is no benefit to be had by including the attribute as the value will read the same for all rows. Minimize the amount of data returned by your queries when and where it makes sense to.

In the example above, the operand on the right-hand side of the comparison operator was a sequence of characters. Conditions utilizing a sequence of characters, in this case the word “online”, require the sequence be enclosed in single quotes (i.e., ‘online’). While some relational database management systems will allow you to also use double quotes, it is important to get in the habit of using single quotes to develop portable solutions. Double quotes should be reserved for identifiers, such as columns and tables.

Logical Operators
Logical operators allow us to filter data based on more complex conditions. They allow us to chain multiple conditions together, compare row values to a set or range of specified values, negate conditions, and implement pattern-matching conditions. Below is a table of logical operators and their behavior when used to evaluate conditions. In the absence of concrete examples, how and when to use each operator can be confusing, so let’s develop a better understanding of each operator through examples.

IN Operator
In the previous examples, comparison operators were used to compare each row’s value for a specified attribute to a single, user-specified value. In many instances, you will need to retrieve those rows where each row’s value for a specified attribute matches one of many specified values or doesn’t match any specified values. For example, suppose instead of retrieving those rows corresponding to products with a category ID value of 12, the requirement in the first example above was slightly altered to include those products with a category ID value of 12, 19, or 31. To satisfy the updated requirement, the IN operator can be used. The IN operator allows you to test whether each row’s value is a member of the user-specified list of values. The list of values provided must be enclosed in parentheses and the values must be of uniform data type. The use of expression lists is a special exception to this rule but are not critical to understand at this point. See the code and corresponding output below capturing the products with a category ID value of 12, 19, or 31.

SELECT
    oe.product_information.product_id,
    oe.product_information.product_name,
    oe.product_information.category_id
FROM
    oe.product_information
WHERE
    oe.product_information.category_id IN (12, 19, 31);

For most problems solved programmatically, there exists multiple candidate solutions. The use of the IN operator in the solution above was just one method to retrieve the desired products. Another method is to use multiple OR operators to evaluate each row’s value against a series of conditions. It is less elegant and concise but works all the same. The code below will return the same output as that shown above.

SELECT
    oe.product_information.product_id,
    oe.product_information.product_name,
    oe.product_information.category_id
FROM
    oe.product_information
WHERE
    oe.product_information.category_id = 12
    OR oe.product_information.category_id = 19
	OR oe.product_information.category_id = 31;

NOT Operator
In the previous example, the goal was to retrieve products with a category ID value of 12, 19, or 31. The category IDs of interest were known in advance and easy to list out following the IN operator. There will be times where the values of interest are not entirely known in advance, or it is simply unreasonable and messy to enumerate them. In such cases, the NOT operator can be used in conjunction with the IN operator. See the code and corresponding output below where those products with category IDs other than 12, 19, and 31 are captured.

SELECT
    oe.product_information.product_id,
    oe.product_information.product_name,
    oe.product_information.category_id
FROM
    oe.product_information
WHERE
    oe.product_information.category_id NOT IN (12, 19, 31);

BETWEEN Operator
The BETWEEN operator can be used to filter data based on user-specified lower-bound and upper-bound values. For example, suppose you need to generate a report containing high-level information on each of the orders placed by customers between two dates. Using the query below, all orders between August 1, 2007 and August 31, 2007, inclusive, are returned. The order ID, customer ID, order date, and order total attributes are included in the output.

SELECT
    oe.orders.order_id,
    oe.orders.customer_id,
	oe.orders.order_date,
	oe.orders.order_total
FROM
	oe.orders
WHERE
    oe.orders.order_date BETWEEN '01-AUG-07' AND '31-AUG-07';

As seen in the example code above, when using the BETWEEN operator, the two user-specified values must be separated by the logical operator, AND.

When exploring the IN operator, I covered how there was another way to achieve the same outcome by using multiple conditions chained together by the logical operator, OR. Similarly, the logic captured in the WHERE clause above can be captured using the combination of two compound comparison operators. The code below will return the same output as seen above.

SELECT
    oe.orders.order_id,
    oe.orders.customer_id,
	oe.orders.order_date,
	oe.orders.order_total
FROM
	oe.orders
WHERE
    oe.orders.order_date >= '01-AUG-07'
    AND oe.orders.order_date <= '31-AUG-07';

Although the code above requires the order date attribute to be specified on each side of the AND operator and is seemingly less succinct, I find it improves readability and consciously avoids using the BETWEEN operator in production code. The reader of the code and your future self needs to recall how endpoints are treated (i.e., both endpoints are included). When comparison operators are used, the treatment of endpoints is explicitly stated through the operators themselves and can be customized to meet requirements.

LIKE Operator
When filtering on string attributes, it can sometimes be difficult to explicitly state the values of interest. In some instances, all desired values are not known in advance and in others, it is unreasonable to list them due to the number or complexity of the values. These obstacles can be overcome by using the LIKE operator.

The LIKE operator allows users to implement filters based on user-specified patterns. Patterns are specified using SQL’s pattern-matching characters. The two main pattern-matching characters and their meanings can be found in the table below.

Using the pattern-matching characters above, users can implement filters based on a row’s attribute value starting with, ending with, and containing a specified sequence of characters. Let’s walk through a couple of examples to better understand the LIKE operator and supporting pattern-matching characters.

In this first example, suppose you need to generate a report containing information related to all product categories starting with the word or sequences of characters, “hardware.” In the categories table shown at the beginning of this page, there exists nine product categories with a category name starting with that sequence. We can use the LIKE operator along with this specified sequence of characters and the pattern-matching character, “%”, to implement a clear and concise filtering condition. The pattern following the LIKE operator in the code below translates to “starts with hardware followed by zero or more characters.” This pattern will match product category names such as, hardware, hardware1, hardware2, etc. If a category named hardwaresqlshortreads existed, it would match that, too! The code’s corresponding output is also provided below.

SELECT
	oe.categories_tab.category_name,
    oe.categories_tab.category_description,
    oe.categories_tab.category_id,
    oe.categories_tab.parent_category_id
FROM
	oe.categories_tab
WHERE
	oe.categories_tab.category_name LIKE 'hardware%';

For the second example, let us focus on the idea of searching for a sequence of characters within a sequence of characters. In other words, we want to determine if a row’s value for an attribute contains a specified sequence of characters. Suppose there is a need to generate a report containing all printer products from the product information table. Further suppose the product categories table shown at the beginning of this page does not exist, so we cannot simply filter to those products in the product information table with a product category ID value of 12. Instead, we must rely on each product’s description. The code below will return all products with a description containing the word, “printer.” If you examine the corresponding output, you will notice that the solution is not perfect. Printer accessories, such as cables and paper are also included. Nonetheless, the solution provides a reasonable output with products limited to printers and their accessories.

SELECT
	oe.product_information.product_id,
    oe.product_information.product_name,
    oe.product_information.product_description
FROM
	oe.product_information
WHERE
    oe.product_information.product_description LIKE '%printer%';

While the solution above is viable, use caution when writing queries containing filters translating to “contains.” Searching for a specific sequence of characters within values varying in length from row to row, especially the lengthy ones observed within the description attribute, can be computationally expensive. Every character within each row’s description value must be traversed to determine whether the value does or does not contain the target sequence of characters.

Having briefly explored the task of creating filters translating to starts with and contains, let us move onto an example where the target sequence is required to exist at the end of a row’s value. In other words, we want a filter that translates to, ends with. In the code below, the condition in the WHERE clause specifies to retrieve all product categories from the categories table with category names ending in the sequence of characters, “ware.” Without looking at the corresponding output, can you guess which rows will be returned by the query?

SELECT
	oe.categories_tab.category_name,
    oe.categories_tab.category_description,
    oe.categories_tab.category_id,
    oe.categories_tab.parent_category_id
FROM
	oe.categories_tab
WHERE
	oe.categories_tab.category_name LIKE '%ware';

The two product categories with names ending in “ware” are software and hardware. Product category names, such as hardware1, hardware2, software1, and software2 were not returned by the query because the category names do not end with the exact specified sequence of characters.

In each of the three examples above, the “%” character was used to represent zero or more characters in the target pattern. We can use the “_” (underscore) character to be represent exactly one character. To showcase how this pattern-matching character can be used, suppose we are tasked with generating the same report above, but only for the child categories of the software category. The child categories of the software category are those categories with names starting with “software” followed by exactly one character (e.g., 1, 2, 3, etc.). This requirement translates to the pattern “software_”. See the code and corresponding output below.

SELECT
	oe.categories_tab.category_name,
    oe.categories_tab.category_description,
    oe.categories_tab.category_id,
    oe.categories_tab.parent_category_id
FROM
	oe.categories_tab
WHERE
	oe.categories_tab.category_name LIKE 'software_';

Please note, the LIKE operator was used in the example above strictly for demonstrative purposes. The task can be achieved in a more straightforward manner by relying on the contents of the parent category ID attribute. Filtering to those product categories with a parent category ID value of 20 will result in the same output.

AND & OR Operators
The AND and OR logical operators allow us to implement filters based on multiple conditions. Understanding these two operators is critical to developing queries refined to retrieve the minimal amount of data required for a specific task. Let’s start with the AND operator, continue onto the OR operator, and then build an understanding of how these two operators can be used together.

During the processing of the WHERE clause, each row is evaluated for the conditions specified. When the AND operator is used to chain conditions together a row must satisfy the condition on each side of the operator to be included in the output. The basic syntax can be seen below.

condition_1 AND condition_2

Given the syntax and the data within the orders table shown at the top of this page, suppose there is a need to generate a report containing the order ID, customer ID, order date, and order total attributes for those orders placed by the customer with an ID value of 101 and a corresponding order total value greater than or equal to $50,000. The query to generate this report along with the corresponding output can be found below. Take a moment to review both and then continue for a discussion that further examines the solution and its output.

SELECT
	oe.orders.order_id,
    oe.orders.customer_id,
	oe.orders.order_date,
	oe.orders.order_total
FROM
	oe.orders
WHERE
    oe.orders.customer_id = 101
    AND oe.orders.order_total >= 50000;

The output above indicates a single row within the orders table satisfied both conditions. Let’s break down how the database arrived at that output. For the sake of this explanation, assume the orders table has four attributes, order ID, customer ID, order date, and order total.

For each row in the orders table, the database used the row’s values when evaluating the conditions specified. Rows where both conditions evaluated to TRUE are kept and included in the output while the others are discarded. In the table below, I’ve added a column for each condition indicating whether the row satisfies the corresponding condition. If you were to traverse the 105 orders in this table manually, then you would arrive at the same conclusion the database did. That is, there is only one row where both conditions are satisfied. Consider giving it a try before moving onto the OR operator.

The OR operator differs from the AND operator in that it is not all or nothing. If either condition separated by the OR operator evaluates to TRUE, then the row is included in the output. Using the same example above but with the OR operator instead of the AND operator, can you guess what the output might look like? For starters, it is a given that the single row returned when using the AND operator will also be returned when using the OR operator because both conditions evaluate to TRUE for the given row. In addition to that row, any row for which either condition evaluates to TRUE will also be returned. For clarity, see the code and corresponding output below. You will notice the output has expanded from a single row to twenty-seven rows. Based on our knowledge of the previous example, we can infer an additional twenty-six orders were placed by customers other than the customer with an ID value of 101 with an order total greater than or equal to $50,000.

SELECT
	oe.orders.order_id,
    oe.orders.customer_id,
	oe.orders.order_date,
	oe.orders.order_total
FROM
	oe.orders
WHERE
    oe.orders.customer_id = 101
    OR oe.orders.order_total >= 50000;

In the previous two example queries, either the AND or OR operator was used, but not both. A task may require you to incorporate filtering criteria utilizing both operators. For example, suppose you are tasked with generating a report with the same layout seen in the previous example; however, this time it needs to contain only those orders placed in the year 2006 or orders placed by the customer with an ID value of 101 with an order total value greater than or equal to $50,000. The code below fulfills the reporting requirements by using two compound conditions separated by an OR operator. The first compound condition tests whether or not the order was placed in the year 2006. The second compound condition tests whether or not the order was placed by the customer with an ID value of 101 with an order total value greater than or equal to $50,000. For each order in the output, at least one of these compound conditions evaluated to TRUE. Take a moment to review the code’s output. We’ll dive into the code’s output to better understand why each order exists in the output, next.

SELECT
    oe.orders.order_id,
    oe.orders.customer_id,
	oe.orders.order_date,
	oe.orders.order_total
FROM
	oe.orders
WHERE
    (
    	oe.orders.order_date >= '01-JAN-06'
    	AND oe.orders.order_date < '01-JAN-07'
    )
    OR (
    	oe.orders.customer_id = 101
    	AND oe.orders.order_total >= 50000
    );

In the output above, order ID 2458 exists because the row’s values result in the second compound condition evaluating to TRUE. The customer ID value is 101 and the order total value associated with the order is greater than $50,000. Remember, both conditions on each side of the logical operator, AND, must evaluate to TRUE for the compound condition to be TRUE. In other words, it is not enough that the customer ID value is 101 or the order total is greater than or equal to $50,000. Both things must be true. Aside from this row, all other rows exist in the output because the orders were placed in the year 2006.

Missing or Unknown Values
When working with data, it is somewhat common to encounter rows with unpopulated attributes. This is especially true for tables with definitions not requiring all attributes to be populated and/or the upstream applications where the data originates being fast and loose on data quality. Table definitions and application controls are seldom managed by those writing queries against the data. Most of the time, the best we can do is to learn methods for identifying those rows with unpopulated values. Specific tasks may require you to zero-in on those rows or exclude them. Let us walk through a couple of examples to build an understanding on how to handle both scenarios.

Let us start with retrieving those rows with unpopulated values for a specific attribute. The example task is to identify those rows within the orders table with unpopulated sales representative ID values. Rows with unpopulated sales representative ID values indicate the customer did not work with a sales representative while creating and subsequently placing the order. The code below fulfills this task by using the combination of the IS keyword and NULL. It is important to understand the use of a comparison operator and NULL (e.g., = NULL) or the IN operator would not work here. Why? NULL represents the absence of a value. It is impossible to know whether any given sales representative ID value is equal to or unequal to NULL because we do not know what NULL is. This same rule applies when performing mathematical operations in SQL. A known value, say 10, plus an unknown value is NULL. Again, the outcome of an mathematical operation can’t be determined when one of the operands is NULL. The code’s output is included, capturing the thirty-five orders with unpopulated sales representative ID values. Note that Oracle’s Live SQL platform’s external representation of NULL, or what you see visually, in the output is a the hyphen character.

SELECT
	oe.orders.order_id,
    oe.orders.customer_id,
    oe.orders.sales_rep_id,
    oe.orders.order_date,
    oe.orders.order_total
FROM
	oe.orders
WHERE
    oe.orders.sales_rep_id IS NULL;

Now, let us turn to the task of excluding those rows from the output where the sales representative ID value is not populated. In other words, the goal is to retrieve those orders where a sales representative worked with the customer during the ordering process. This can be achieved by simply using the NOT operator to negate the IS keyword. For clarity, see the code and corresponding output below.

SELECT
	oe.orders.order_id,
    oe.orders.customer_id,
    oe.orders.sales_rep_id,
    oe.orders.order_date,
    oe.orders.order_total
FROM
	oe.orders
WHERE
    oe.orders.sales_rep_id IS NOT NULL;