WHERE Clause & Filtering

The WHERE clause is how Oracle decides which rows to include in a result set. It evaluates a condition for every row in the table and returns only rows where that condition is true. Rows where the condition is false or unknown — which happens when NULL is involved — are excluded. This lesson covers every filtering tool available in Oracle: comparison operators, range and list tests, pattern matching, null handling, and compound conditions built with AND, OR, and NOT.

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

Comparison Operators

The six standard comparison operators form the foundation of most WHERE conditions. They work on numeric, character, and date columns — Oracle compares values of the same type directly and performs implicit conversion when types are compatible. Comparing a VARCHAR2 column to a number literal triggers implicit conversion, which can prevent index use and cause unexpected results. Always compare like to like.

-- Numeric comparisons
SELECT product_name, unit_price, stock_qty
FROM   products
WHERE  unit_price > 100;

SELECT product_name, unit_price
FROM   products
WHERE  unit_price != 149.99;   -- != and <> are both valid in Oracle

-- String comparisons are case-sensitive in Oracle
-- 'gold' matches but 'Gold' does not
SELECT customer_id, full_name, loyalty_tier
FROM   customers
WHERE  loyalty_tier = 'gold';

-- Date comparisons — always use DATE literals or TO_DATE for clarity
SELECT order_id, order_date, status
FROM   orders
WHERE  order_date >= DATE '2024-02-01';

-- Not equal — both operators are equivalent
SELECT product_name, active_flag
FROM   products
WHERE  active_flag <> 'Y';

• String comparisons in Oracle are case-sensitive by default — 'gold' and 'Gold' are different values; use UPPER(column) = 'GOLD' or LOWER(column) = 'gold' to make a comparison case-insensitive, though this prevents index use on the column
• Always use DATE 'YYYY-MM-DD' literals or TO_DATE('2024-02-01', 'YYYY-MM-DD') for date comparisons — never rely on Oracle's implicit date string conversion, which depends on the session's NLS_DATE_FORMAT setting and varies between environments
• Both != and <> mean not equal in Oracle — <> is the ANSI standard form and is preferred for portability

BETWEEN, IN, and NOT IN

BETWEEN tests whether a value falls within an inclusive range. IN tests whether a value matches any member of a list. Both are shorthand for compound conditions — BETWEEN a AND b is equivalent to >= a AND <= b, and IN (x, y, z) is equivalent to = x OR = y OR = z. The shorthand forms are cleaner and easier to read.

NOT IN has an important trap: if the list contains a NULL value, NOT IN returns no rows at all. This is because NOT IN is internally evaluated as a series of AND <> comparisons, and any comparison involving NULL produces UNKNOWN, which causes the entire condition to fail.

-- BETWEEN — inclusive on both ends
SELECT product_name, unit_price
FROM   products
WHERE  unit_price BETWEEN 50 AND 200
ORDER BY unit_price;

-- Date range with BETWEEN
SELECT order_id, order_date, total_amount
FROM   orders
WHERE  order_date BETWEEN DATE '2024-01-01' AND DATE '2024-02-28'
ORDER BY order_date;

-- IN — match against a list of values
SELECT customer_id, full_name, country
FROM   customers
WHERE  country IN ('USA', 'UK', 'Canada')
ORDER BY country;

-- NOT IN — excludes listed values
SELECT product_name, category
FROM   products
WHERE  category NOT IN ('Electronics', 'Accessories')
ORDER BY category;

-- NOT IN with NULL trap — if manager_id list could contain NULL, use NOT EXISTS instead
-- This returns no rows if any value in the subquery is NULL
SELECT employee_id, first_name
FROM   employees
WHERE  employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

• BETWEEN is inclusive — BETWEEN 50 AND 200 includes rows where the value is exactly 50 or exactly 200
• For date ranges, BETWEEN DATE '2024-01-01' AND DATE '2024-01-31' includes all rows on 31-JAN-2024 only up to midnight — rows with a time component on that day (e.g. 31-JAN-2024 14:30:00) are excluded; use < DATE '2024-02-01' to capture the full last day
• Always add WHERE col IS NOT NULL to any subquery used with NOT IN — this is the safest way to avoid the NULL trap

LIKE and Pattern Matching

LIKE matches character column values against a pattern. Two wildcard characters are available: % matches any sequence of zero or more characters, and _ matches exactly one character. LIKE is case-sensitive in Oracle — LIKE 'S%' matches names starting with a capital S but not a lowercase s.

When the pattern itself contains a literal % or _ character, use the ESCAPE clause to define an escape character that neutralises the wildcard meaning of the next character.

-- % matches any sequence of characters
SELECT full_name, email
FROM   customers
WHERE  email LIKE '%@gmail.com';

-- Names starting with a specific letter
SELECT full_name, country
FROM   customers
WHERE  full_name LIKE 'S%';

-- _ matches exactly one character — useful for fixed-format codes
-- Matches job codes with exactly 6 characters followed by anything
SELECT job_id, job_title
FROM   jobs
WHERE  job_id LIKE '______'   -- six underscores: exactly 6-character job IDs
ORDER BY job_id;

-- NOT LIKE — exclude pattern matches
SELECT product_name, category
FROM   products
WHERE  product_name NOT LIKE '%Pro%'
ORDER BY product_name;

-- Case-insensitive LIKE using UPPER
SELECT full_name, email
FROM   customers
WHERE  UPPER(email) LIKE '%@EXAMPLE.COM';

-- ESCAPE clause — find product codes containing a literal underscore
SELECT product_name
FROM   products
WHERE  product_name LIKE '%\_%' ESCAPE '\';

• Leading wildcards — LIKE '%smith' — prevent Oracle from using an index on the column; the entire table must be scanned; use them only when necessary
• _ matches exactly one character including spaces and special characters — LIKE 'A_' matches AB, A1, and A (A followed by a space)
• For high-volume text search across large tables, Oracle Text (a full-text indexing engine) is far more efficient than LIKE with leading wildcards

IS NULL and IS NOT NULL

NULL represents an unknown or absent value. It is not a value itself — it cannot be compared with = or !=. Any comparison involving NULL using standard operators returns UNKNOWN, which Oracle treats as false in a WHERE clause. The only way to test for NULL is IS NULL or IS NOT NULL.

-- IS NULL — find employees with no manager (top of the hierarchy)
SELECT employee_id, first_name, last_name, manager_id
FROM   employees
WHERE  manager_id IS NULL;

-- IS NOT NULL — find employees who do have a manager
SELECT employee_id, first_name, last_name, manager_id
FROM   employees
WHERE  manager_id IS NOT NULL
ORDER BY manager_id;

-- IS NULL — find customers with no phone number on record
SELECT customer_id, full_name, phone
FROM   customers
WHERE  phone IS NULL;

-- Common mistake — this returns no rows even if NULL values exist
-- NULL = NULL evaluates to UNKNOWN, not TRUE
SELECT employee_id FROM employees WHERE manager_id = NULL;

-- NVL substitutes a default when a column is NULL — useful in SELECT but not a filter
SELECT employee_id,
       first_name,
       NVL(TO_CHAR(manager_id), 'No Manager') AS manager_display
FROM   employees
ORDER BY employee_id;

• WHERE column = NULL never returns rows — this is one of the most common SQL mistakes; always use IS NULL
• NVL(expr, default) returns default when expr is NULL and expr when it is not — use it in SELECT expressions to substitute display values, not as a filter
• COALESCE(a, b, c) is the ANSI standard equivalent of NVL for two or more alternatives — it returns the first non-null value in the list and works in any Oracle version that supports SQL:1999

Compound Conditions — AND, OR, NOT

Multiple conditions are combined with AND, OR, and NOT. Oracle evaluates NOT first, then AND, then OR — the same precedence as most programming languages. When mixing AND and OR without parentheses the results are often not what the developer intended. Always use parentheses to make precedence explicit.

-- AND — both conditions must be true
SELECT product_name, category, unit_price, stock_qty
FROM   products
WHERE  unit_price > 100
AND    active_flag = 'Y'
ORDER BY unit_price DESC;

-- OR — either condition must be true
SELECT customer_id, full_name, country
FROM   customers
WHERE  country = 'USA'
OR     country = 'UK'
ORDER BY country;

-- NOT — negates a condition
SELECT product_name, category
FROM   products
WHERE  NOT (category = 'Electronics')
ORDER BY category;

-- Mixing AND and OR — parentheses make intent clear
-- Without parentheses AND binds tighter than OR, which can produce wrong results
SELECT order_id, status, total_amount
FROM   orders
WHERE  (status = 'pending' OR status = 'processing')
AND    total_amount > 500
ORDER BY total_amount DESC;

-- Three-condition filter on the HR schema
SELECT employee_id, first_name, last_name, job_id, hire_date
FROM   employees
WHERE  department_id = 20
AND    hire_date >= DATE '2023-01-01'
AND    job_id <> 'IT_MGR'
ORDER BY hire_date;

• AND has higher precedence than OR — WHERE a = 1 OR b = 2 AND c = 3 is evaluated as WHERE a = 1 OR (b = 2 AND c = 3), which is almost never what was intended; always use parentheses when mixing the two
• NOT IN, NOT LIKE, and NOT BETWEEN are the negated forms of their operators — they are cleaner than wrapping the positive form in NOT (...)
• Each additional AND condition narrows the result set; each additional OR condition widens it — when a query returns unexpectedly many or few rows, check whether AND and OR are in the right positions

Summary

Practice Questions

Quiz

Next up — ORDER BY & FETCH FIRST — A deep dive into sorting results, multi-column ordering, NULLS FIRST and NULLS LAST, and Oracle's full pagination syntax.