Filtering Data with the WHERE Clause

The WHERE clause in Oracle 19c SQL is a fundamental component of the SELECT statement. It allows you to specify conditions that rows must meet to be included in the result set. This is crucial for retrieving precise and relevant data from your tables, enhancing data analysis and reporting. Without the WHERE clause, a SELECT statement would return all rows from the specified table, which is often not desirable. It's used in conjunction with various operators to build powerful filtering criteria.

Example 1: Oracle 19c SQL SELECT statement

-- Selecting employees with a specific job ID
SELECT
    employee_id,    -- Unique identifier for the employee
    first_name,     -- Employee's first name
    last_name,      -- Employee's last name
    email,          -- Employee's email address
    phone_number,   -- Employee's phone number
    hire_date,      -- Date when the employee was hired
    job_id,         -- Identifier for the employee's job
    salary          -- Employee's current salary
FROM
    employees       -- The table containing employee information
WHERE
    job_id = 'IT_PROG'; -- Filtering condition: only select employees whose job_id is 'IT_PROG'

Explanation This SQL SELECT statement retrieves specific columns (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary) from the employees table. The WHERE clause is then applied to filter these records, ensuring that only those employees with a job_id equal to 'IT_PROG' are returned. This is a common practice in database queries to narrow down results based on exact matches, making it easier to find specific data like "Oracle 19c employee records" or "SQL query job role."

Comparison Operators (=, != / <>, >, <, >=, <=)

Comparison operators are used in the WHERE clause to compare two expressions. These operators are essential for defining conditions that evaluate to true, false, or unknown, thereby determining which rows are included in your query results. They form the backbone of most filtering operations in SQL. Understanding these "Oracle 19c comparison operators" is key for "SQL data filtering."

Example 1: Using the Equal Operator (=)

-- Retrieve all products that are exactly priced at 19.99
SELECT
    product_id,     -- Unique identifier for the product
    product_name,   -- Name of the product
    category,       -- Category the product belongs to
    price,          -- Price of the product
    stock_quantity  -- Current stock quantity of the product
FROM
    products        -- The table containing product information
WHERE
    price = 19.99;  -- Filters for products where the price is exactly 19.99

Explanation This query fetches products from the products table where the price column is exactly 19.99. The = operator is used for an exact match, which is useful for finding specific items based on their precise value, a common requirement in "Oracle 19c SQL exact match" queries.

Example 2: Using the Not Equal Operator (!= or <>)

-- Find all employees who are not 'AD_PRES' (President)
SELECT
    employee_id,    -- Employee's unique ID
    first_name,     -- Employee's first name
    last_name,      -- Employee's last name
    job_id,         -- Employee's job ID
    salary          -- Employee's salary
FROM
    employees       -- Employee data table
WHERE
    job_id != 'AD_PRES'; -- Filters out the President, showing all other job roles.
    -- Alternatively, you could use WHERE job_id <> 'AD_PRES';

Explanation This statement selects employees whose job_id is not 'AD_PRES'. Both != and <> are valid "Oracle 19c not equal operators" for this purpose. This helps in excluding specific categories of data from your result set, common in "SQL exclude filter" scenarios.

Example 3: Using the Greater Than Operator (>)

-- List all orders placed after January 1, 2024
SELECT
    order_id,       -- Unique identifier for the order
    customer_id,    -- Identifier for the customer who placed the order
    order_date,     -- Date when the order was placed
    total_amount    -- Total amount of the order
FROM
    orders          -- Table containing order information
WHERE
    order_date > TO_DATE('2024-01-01', 'YYYY-MM-DD'); -- Filters for orders placed after 2024-01-01

Explanation This query retrieves orders that were placed after January 1, 2024. The > operator is used for "Oracle 19c greater than" comparisons, allowing you to filter data based on values exceeding a certain threshold, often used in "SQL date range queries."

Example 4: Using the Less Than Operator (<)

-- Retrieve products with stock quantity less than 50
SELECT
    product_id,     -- Product identifier
    product_name,   -- Name of the product
    stock_quantity  -- Current stock level
FROM
    products        -- Products inventory table
WHERE
    stock_quantity < 50; -- Shows products that are low in stock

Explanation This SQL query identifies products with a stock_quantity less than 50. The < operator is used for "Oracle 19c less than" conditions, helping to pinpoint items needing replenishment or review, a practical application in "SQL inventory management."

Example 5: Using Greater Than or Equal To (>=) and Less Than or Equal To (<=)

-- Find employees whose salary is between 5000 and 10000 (inclusive)
SELECT
    employee_id,    -- Employee ID
    first_name,     -- First name
    last_name,      -- Last name
    salary          -- Employee's salary
FROM
    employees       -- Employee data
WHERE
    salary >= 5000 AND salary <= 10000; -- Filters for salaries within the specified range

Explanation This query fetches employees whose salary falls within the range of 5000 and 10000, inclusive. The >= and <= operators are used for "Oracle 19c range queries," which are commonly used for filtering data within a specific numerical or date boundary, such as "SQL salary range filter."

Logical Operators (AND, OR, NOT)

Logical operators combine multiple conditions in the WHERE clause, allowing you to create complex filtering criteria. These operators determine how the individual conditions are evaluated together to produce a final true or false result for each row. Mastering "Oracle 19c logical operators" is crucial for writing sophisticated "SQL multi-condition queries."

Example 1: Using the AND Operator

-- Find employees in 'IT_PROG' job category with a salary greater than 7000
SELECT
    employee_id,    -- Employee's unique ID
    first_name,     -- Employee's first name
    last_name,      -- Employee's last name
    job_id,         -- Employee's job ID
    salary          -- Employee's salary
FROM
    employees       -- Employee data table
WHERE
    job_id = 'IT_PROG' AND salary > 7000; -- Both conditions must be true

Explanation This query retrieves employees who are both 'IT_PROG' and earn more than 7000. The AND operator ensures that "Oracle 19c multiple conditions" are met simultaneously, which is excellent for narrowing down search results based on several criteria.

Example 2: Using the OR Operator

-- Select products that are either in the 'Electronics' category or have a stock quantity less than 20
SELECT
    product_id,     -- Product identifier
    product_name,   -- Name of the product
    category,       -- Product category
    stock_quantity  -- Current stock level
FROM
    products        -- Products inventory table
WHERE
    category = 'Electronics' OR stock_quantity < 20; -- At least one condition must be true

Explanation This statement selects products that are either in the 'Electronics' category or have a stock_quantity less than 20. The OR operator allows you to return rows that satisfy "Oracle 19c alternative conditions," broadening your result set when any of the specified criteria are met.

Example 3: Using the NOT Operator

-- Find all employees who are NOT in the 'Sales' department
SELECT
    employee_id,    -- Employee's unique ID
    first_name,     -- Employee's first name
    last_name,      -- Employee's last name
    department_id   -- Employee's department ID
FROM
    employees       -- Employee data table
WHERE
    NOT department_id = 80; -- Excludes employees from department ID 80 (Sales typically)
    -- Alternative: WHERE department_id != 80; or WHERE department_id <> 80;

Explanation This query returns employees who are not in department_id 80. The NOT operator negates a condition, useful for "Oracle 19c excluding data" or finding rows that do not match a specific criterion.

Example 4: Combining AND and OR

-- Find orders placed by customer 101 AND (total amount is greater than 500 OR order date is after '2025-01-01')
SELECT
    order_id,       -- Order identifier
    customer_id,    -- Customer ID
    order_date,     -- Date of the order
    total_amount    -- Total amount of the order
FROM
    orders          -- Order transactions table
WHERE
    customer_id = 101 AND (total_amount > 500 OR order_date > TO_DATE('2025-01-01', 'YYYY-MM-DD')); -- Combines AND and OR with parentheses for precedence

Explanation This query demonstrates how AND and OR can be combined. It retrieves orders for customer_id 101, where the total amount is greater than 500, or the order date is after January 1, 2025. Parentheses are used to control the order of evaluation, a crucial aspect of "Oracle 19c complex WHERE clauses."

Example 5: Using NOT with IN

-- Select products that are not in the 'Books' and 'Movies' categories
SELECT
    product_id,     -- Product identifier
    product_name,   -- Product name
    category        -- Product category
FROM
    products        -- Products table
WHERE
    NOT category IN ('Books', 'Movies'); -- Excludes products from these two categories

Explanation This query uses NOT IN to select products that are not categorized as 'Books' or 'Movies'. This is an efficient way to filter out multiple values, showcasing "Oracle 19c NOT IN clause" functionality for "SQL exclusion lists."

BETWEEN (Inclusive Range)

The BETWEEN operator is used to test if an expression is within a specified range, including both the start and end values. It provides a more concise and readable way to define range conditions compared to using >= and <=. This operator is highly effective for "Oracle 19c range filtering" and "SQL date range queries."

Example 1: Numeric Range

-- Find employees with salaries between 5000 and 10000 (inclusive)
SELECT
    employee_id,    -- Employee ID
    first_name,     -- First name
    last_name,      -- Last name
    salary          -- Employee's salary
FROM
    employees       -- Employee data
WHERE
    salary BETWEEN 5000 AND 10000; -- Selects salaries within this range, inclusive of endpoints

Explanation This query retrieves employees whose salary is between 5000 and 10000, inclusive of both 5000 and 10000. The BETWEEN operator simplifies "Oracle 19c salary range" queries, making the code more readable.

Example 2: Date Range

-- Select orders placed between January 1, 2024, and June 30, 2024 (inclusive)
SELECT
    order_id,       -- Order identifier
    customer_id,    -- Customer ID
    order_date,     -- Date of the order
    total_amount    -- Total amount
FROM
    orders          -- Orders table
WHERE
    order_date BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-06-30', 'YYYY-MM-DD'); -- Orders within this date range

Explanation This statement fetches orders placed within the first six months of 2024. BETWEEN is very useful for "Oracle 19c date range queries," ensuring that both the start and end dates are included in the filter.

Example 3: Alphanumeric Range

-- Find products whose names start with letters from 'A' to 'M'
SELECT
    product_id,     -- Product identifier
    product_name,   -- Product name
    category        -- Product category
FROM
    products        -- Products table
WHERE
    product_name BETWEEN 'A' AND 'M'; -- to ensure all product names up to 'N' are included

Explanation This query retrieves products whose names alphabetically fall between 'A' and 'M'. While primarily used for numeric and date ranges, BETWEEN can also work with strings based on their lexical order, a feature sometimes useful for "Oracle 19c alphabetical range" filtering.

Example 4: Using NOT BETWEEN

-- Find employees whose salary is NOT between 5000 and 10000
SELECT
    employee_id,    -- Employee ID
    first_name,     -- First name
    last_name,      -- Last name
    salary          -- Employee's salary
FROM
    employees       -- Employee data
WHERE
    salary NOT BETWEEN 5000 AND 10000; -- Excludes salaries within this range

Explanation This query selects employees whose salary is outside the range of 5000 to 10000. NOT BETWEEN is perfect for "Oracle 19c excluding range" conditions, allowing you to quickly filter out data within a specified interval.

Example 5: Combined with AND for More Specific Dates

-- Find orders placed in March 2024 (inclusive of full month)
SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
FROM
    orders
WHERE
    order_date BETWEEN TO_DATE('2024-03-01', 'YYYY-MM-DD') AND TO_DATE('2024-03-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS');

Explanation This example demonstrates a common and effective use of BETWEEN for filtering an entire month's data. By specifying the start of the first day and the end of the last day, it accurately captures all records within "Oracle 19c specific month date range." This is a highly practical "SQL monthly report filter."

IN and NOT IN (List of Values)

The IN operator is used to test if an expression matches any value in a list of values. Conversely, NOT IN tests if an expression does not match any value in the list. These operators are highly efficient for filtering data based on multiple discrete values, simplifying "Oracle 19c list matching" and "SQL multiple value filtering."

Example 1: Using IN with Numeric Values

-- Select employees who work in department IDs 10, 20, or 30
SELECT
    employee_id,    -- Employee's unique ID
    first_name,     -- Employee's first name
    last_name,      -- Employee's last name
    department_id   -- Employee's department ID
FROM
    employees       -- Employee data table
WHERE
    department_id IN (10, 20, 30); -- Filters for employees in these specific departments

Explanation This query retrieves employees from departments 10, 20, or 30. The IN operator provides a concise way to specify "Oracle 19c multiple exact matches" for a column, an improvement over using multiple OR conditions.

Example 2: Using IN with String Values

-- Find products that are categorized as 'Electronics', 'Books', or 'Apparel'
SELECT
    product_id,     -- Product identifier
    product_name,   -- Product name
    category        -- Product category
FROM
    products        -- Products table
WHERE
    category IN ('Electronics', 'Books', 'Apparel'); -- Selects products from these categories

Explanation This statement selects products belonging to 'Electronics', 'Books', or 'Apparel' categories. Using IN with string literals is common for "SQL category filtering" and efficient "Oracle 19c string list matching."

Example 3: Using NOT IN with Numeric Values

-- Select employees who do NOT work in department IDs 10, 20, or 30
SELECT
    employee_id,    -- Employee's unique ID
    first_name,     -- Employee's first name
    last_name,      -- Employee's last name
    department_id   -- Employee's department ID
FROM
    employees       -- Employee data table
WHERE
    department_id NOT IN (10, 20, 30); -- Excludes employees from these specific departments

Explanation This query retrieves employees who are not in departments 10, 20, or 30. NOT IN is effective for "Oracle 19c excluding list values," allowing you to filter out data based on a defined set of exclusions.

Example 4: Using NOT IN with String Values

-- Find products that are NOT categorized as 'Electronics' or 'Food'
SELECT
    product_id,     -- Product identifier
    product_name,   -- Product name
    category        -- Product category
FROM
    products        -- Products table
WHERE
    category NOT IN ('Electronics', 'Food'); -- Excludes products from these categories

Explanation This statement selects products that are not categorized as 'Electronics' or 'Food'. NOT IN is highly useful for "SQL exclusion lists" and filtering out unwanted "Oracle 19c categories."

Example 5: Using IN with a Subquery

-- Select employees whose job ID is one of the job IDs found in the 'jobs' table with a min_salary > 8000
SELECT
    employee_id,    -- Employee ID
    first_name,     -- First name
    last_name,      -- Last name
    job_id,         -- Job ID of the employee
    salary          -- Employee's salary
FROM
    employees       -- Employee table
WHERE
    job_id IN (SELECT job_id FROM jobs WHERE min_salary > 8000); -- Filters based on job_ids returned by the subquery

Explanation This query uses a subquery with IN to dynamically select employees whose job_id corresponds to jobs with a min_salary greater than 8000. This demonstrates "Oracle 19c dynamic filtering" and "SQL subquery IN clause" for more complex, data-driven filtering.

LIKE and NOT LIKE (Pattern Matching: Wildcards %, _)

The LIKE operator is used for pattern matching in string comparisons. It allows you to search for values that match a specified pattern using wildcard characters. The % wildcard matches any sequence of zero or more characters, while the _ wildcard matches any single character. NOT LIKE does the opposite, finding values that do not match the pattern. These are essential for "Oracle 19c fuzzy search" and "SQL wildcard matching."

Example 1: Using LIKE with the '%' Wildcard

-- Find employees whose first name starts with 'Jo'
SELECT
    employee_id,    -- Employee ID
    first_name,     -- First name
    last_name       -- Last name
FROM
    employees       -- Employee data
WHERE
    first_name LIKE 'Jo%'; -- Matches names starting with 'Jo' followed by any characters

Explanation This query retrieves employees whose first_name begins with 'Jo'. The % wildcard makes it flexible for "Oracle 19c starts with" searches, which are frequently used in "SQL partial string matching."

Example 2: Using LIKE with the '_' Wildcard

-- Find products whose names have 'o' as the second character
SELECT
    product_id,     -- Product ID
    product_name    -- Product name
FROM
    products        -- Products table
WHERE
    product_name LIKE '_o%'; -- Matches names with any single character, then 'o', then any characters

Explanation This statement selects products where the second character of product_name is 'o'. The _ wildcard is useful for "Oracle 19c fixed position matching" or when you know the character at a specific position but not necessarily the surrounding characters.

Example 3: Using LIKE with both '%' and '_'

-- Find employees whose email contains 'smith' anywhere, and the first character is 'j'
SELECT
    employee_id,    -- Employee ID
    first_name,     -- First name
    last_name,      -- Last name
    email           -- Email address
FROM
    employees       -- Employee data
WHERE
    email LIKE 'j%smith%'; -- Matches emails starting with 'j', containing 'smith' anywhere after

Explanation This query fetches employees whose email starts with 'j' and contains 'smith' anywhere thereafter. This demonstrates how "Oracle 19c advanced pattern matching" can combine both wildcards for more refined searches, often used in "SQL text search."

Example 4: Using NOT LIKE

-- Find products whose names do NOT start with 'A'
SELECT
    product_id,     -- Product ID
    product_name    -- Product name
FROM
    products        -- Products table
WHERE
    product_name NOT LIKE 'A%'; -- Excludes products whose names start with 'A'

Explanation This statement retrieves products whose product_name does not start with 'A'. NOT LIKE is effective for "Oracle 19c excluding patterns" or finding data that does not conform to a specified pattern.

Example 5: Case-Insensitive LIKE (using UPPER or LOWER)

-- Find employees whose first name contains 'jo' (case-insensitive)
SELECT
    employee_id,    -- Employee ID
    first_name,     -- First name
    last_name       -- Last name
FROM
    employees       -- Employee data
WHERE
    UPPER(first_name) LIKE UPPER('%jo%'); -- Converts to uppercase before matching for case-insensitivity
    -- Alternatively, for case-insensitive search in Oracle, you might configure NLS_COMP or NLS_SORT parameters,
    -- or use the built-in function LOWER() on both sides.

Explanation This query finds employees whose first_name contains 'jo', regardless of case. By converting both the column value and the pattern to uppercase (or lowercase) using UPPER() or LOWER(), you achieve "Oracle 19c case-insensitive search" with LIKE, a valuable technique for "SQL flexible text matching."

IS NULL and IS NOT NULL (Handling Missing Data)

The IS NULL operator is used to test for null (missing or undefined) values in a column. IS NOT NULL is used to test for values that are not null. These operators are crucial for managing and querying data where values might be absent, ensuring accurate handling of "Oracle 19c missing data" and "SQL null value handling."

Example 1: Finding Rows with NULL Values

-- Select employees who do not have an assigned commission_pct (commission_pct is NULL)
SELECT
    employee_id,    -- Employee ID
    first_name,     -- First name
    last_name,      -- Last name
    commission_pct  -- Commission percentage
FROM
    employees       -- Employee data
WHERE
    commission_pct IS NULL; -- Filters for rows where commission_pct is missing

Explanation This query retrieves employees who do not have a commission_pct assigned. The IS NULL operator is specifically designed for "Oracle 19c null checks," as NULL cannot be compared using standard comparison operators like =.

Example 2: Finding Rows with NOT NULL Values

-- Select employees who have an assigned commission_pct (commission_pct is NOT NULL)
SELECT
    employee_id,    -- Employee ID
    first_name,     -- First name
    last_name,      -- Last name
    commission_pct  -- Commission percentage
FROM
    employees       -- Employee data
WHERE
    commission_pct IS NOT NULL; -- Filters for rows where commission_pct has a value

Explanation This statement fetches employees who have a value in their commission_pct column. IS NOT NULL is used for "Oracle 19c non-null checks," which is essential when you need to work only with complete records.

Example 3: Combining IS NULL with Other Conditions

-- Find products with a NULL description AND a price greater than 50
SELECT
    product_id,     -- Product ID
    product_name,   -- Product name
    description,    -- Product description
    price           -- Product price
FROM
    products        -- Products table
WHERE
    description IS NULL AND price > 50; -- Combines null check with a numeric condition

Explanation This query combines IS NULL with a numeric comparison. It finds products that have a NULL description and a price greater than 50. This demonstrates "Oracle 19c complex null filtering" in conjunction with other criteria.

Example 4: Handling Empty Strings vs. NULL (Important distinction in Oracle)

-- In Oracle, an empty string ('') is treated as NULL.
-- This query will find customers with an empty or NULL email.
SELECT
    customer_id,    -- Customer ID
    first_name,     -- First name
    last_name,      -- Last name
    email           -- Customer email
FROM
    customers       -- Customers table
WHERE
    email IS NULL; -- This will catch both truly NULL and empty string emails in Oracle

Explanation In Oracle, an empty string (e.g., '') is treated as NULL. This query showcases this specific behavior, where IS NULL will effectively catch both explicitly NULL values and empty strings in columns, a key aspect of "Oracle 19c empty string handling."

Example 5: Using COALESCE for Displaying Default Value for NULLs

-- Display employee commission, showing 'N/A' if commission_pct is NULL
SELECT
    employee_id,    -- Employee ID
    first_name,     -- First name
    last_name,      -- Last name
    COALESCE(TO_CHAR(commission_pct), 'N/A') AS actual_commission -- Replaces NULL commission with 'N/A'
FROM
    employees;      -- Employee data

Explanation While not strictly a WHERE clause example, this demonstrates a common way to handle NULL values in the SELECT list. The COALESCE function returns the first non-null expression in its list. Here, it's used to display 'N/A' when commission_pct is NULL, enhancing the presentation of "Oracle 19c null display" in query results.