What is Filtering Data?
Filtering Data is the process of retrieving a subset of rows from a database table based on specific conditions. In SQL, this is primarily accomplished using the WHERE clause in a SELECT, UPDATE, or DELETE statement. Its core purpose is to pinpoint and manipulate only the relevant data, effectively solving the problem of information overload by excluding irrelevant records.
Filtering is fundamental to achieving precision and efficiency in data retrieval and manipulation.
Comparison Operators
Comparison operators form the bedrock of filtering conditions (=, != or <>, <, >, <=, >=).
DECLARE
l_employee_name employees.last_name%TYPE;
BEGIN
-- Select the employee whose ID is exactly 100
SELECT last_name INTO l_employee_name
FROM employees
WHERE employee_id = 100; -- Using the equality operator '='
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || l_employee_name);
END;
/
Logical Operators AND
The AND operator is used to combine multiple conditions where all must be true for a row to be included.
The AND operator narrows the result set, making the filter more specific.
BEGIN
-- Update salary for employees in a specific department who have a specific job title
UPDATE employees
SET salary = salary * 1.10 -- 10% raise
WHERE department_id = 60
AND job_id = 'IT_PROG'; -- Both conditions MUST be true
DBMS_OUTPUT.PUT_LINE('Salaries updated for IT Programmers in Dept 60: ' || SQL%ROWCOUNT);
END;
/
Logical Operators OR
The OR operator combines multiple conditions where at least one must be true for a row to be included.
DECLARE
CURSOR emp_cur IS
-- Select employees who are either Managers or based in location 1700
SELECT first_name, last_name, job_id, department_id
FROM employees
WHERE job_id LIKE '%MAN' -- Condition 1: Job is a Manager
OR department_id IN (SELECT department_id
FROM departments
WHERE location_id = 1700); -- Condition 2: In location 1700
BEGIN
FOR emp_rec IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.last_name || ' works as ' || emp_rec.job_id);
END LOOP;
END;
/
NOT Operator
A logical operator that negates a condition. It returns TRUE if the condition following it is FALSE.
The NOT operator is powerful but should be used judiciously as it can sometimes hinder performance.
BEGIN
-- Delete employees who are NOT in the 'IT' department
DELETE FROM employees
WHERE NOT (department_id = 60); -- Negates the condition "department_id = 60"
DBMS_OUTPUT.PUT_LINE('Employees not in IT deleted: ' || SQL%ROWCOUNT);
END;
/
IN Operator
Provides a concise way to check if a value matches any value in a list.
The IN operator is a shorthand for multiple OR conditions and improves code readability.
DECLARE
CURSOR c_managers IS
-- Select employees whose manager ID is in a specific list
SELECT first_name, last_name
FROM employees
WHERE manager_id IN (100, 102, 114); -- Equivalent to manager_id=100 OR manager_id=102 OR manager_id=114
BEGIN
FOR rec IN c_managers LOOP
DBMS_OUTPUT.PUT_LINE('Managed by senior staff: ' || rec.first_name || ' ' || rec.last_name);
END LOOP;
END;
/
BETWEEN Operator
Used to filter a range of values (inclusive). It checks if a value is within a specified lower and upper bound.
BEGIN
-- Select employees hired between two specific dates
FOR rec IN (
SELECT first_name, hire_date
FROM employees
WHERE hire_date BETWEEN DATE '2003-01-01' AND DATE '2003-12-31' -- Inclusive range for the year 2003
) LOOP
DBMS_OUTPUT.PUT_LINE(rec.first_name || ' was hired in 2003 on ' || rec.hire_date);
END LOOP;
END;
/
LIKE Operator
Used for pattern matching on string data using wildcards:
% matches any sequence of characters (zero or more)_ matches exactly one character
DECLARE
v_search_pattern VARCHAR2(20) := 'S%';
BEGIN
-- Find all employees whose last name starts with 'S'
FOR rec IN (
SELECT last_name
FROM employees
WHERE UPPER(last_name) LIKE v_search_pattern -- The '%' wildcard matches any characters after 'S'
) LOOP
DBMS_OUTPUT.PUT_LINE('Found: ' || rec.last_name);
END LOOP;
END;
/
IS NULL Operator
Specifically used to check for NULL values.
A common mistake is using = NULL, which always evaluates to UNKNOWN. Always use IS NULL or IS NOT NULL.
BEGIN
-- Update records where the commission percentage has not been set (is NULL)
UPDATE employees
SET commission_pct = 0
WHERE commission_pct IS NULL; -- Correctly identifies rows with NULL values
DBMS_OUTPUT.PUT_LINE('Updated ' || SQL%ROWCOUNT || ' rows with NULL commission.');
END;
/
Why is Filtering Data Important?
By filtering data at the source (in the database), you minimize the amount of data transferred to the PL/SQL engine and client, adhering to the principle of Set-Based Processing. This is far more scalable than fetching entire tables and filtering procedurally.
Data Integrity & Accuracy
Precise filtering, especially in UPDATE and DELETE statements, prevents unintended changes to data. This ensures ACID compliance by guaranteeing that transactions only affect the correct rows, maintaining database integrity.
Code Maintainability (DRY)
By embedding the filtering logic directly in the SQL statement, you avoid writing verbose procedural code (e.g., loops with IF statements) to sift through data. This keeps code DRY (Don’t Repeat Yourself) and more declarative.
Advanced Nuances
Operator Precedence and Parentheses
Logical operators have a defined precedence: NOT, AND, then OR. Use parentheses to clarify ambiguous conditions.
-- Without parentheses (ambiguous)
WHERE condition1 OR condition2 AND condition3
-- Evaluated as: condition1 OR (condition2 AND condition3)
-- With parentheses (explicit)
WHERE (condition1 OR condition2) AND condition3
LIKE with Escaped Characters
When searching for strings containing literal % or _ characters, use the ESCAPE clause:
WHERE file_name LIKE '%\_backup%' ESCAPE '\'
BETWEEN with Dates and Timestamps
While BETWEEN is inclusive, it can be tricky with TIMESTAMP data types. Use careful range definitions.
-- Common pattern to exclude upper bound
WHERE timestamp_col >= start_date AND timestamp_col < end_date + 1
How This Fits the Roadmap
Within the “Basic SQL Querying” section, Filtering Data is a foundational pillar.
- Data Aggregation (
GROUP BY, HAVING) - You must filter individual rows before grouping
- Joining Tables - The
ON clause is itself a filter, and WHERE filters join results
Essential For Advanced Topics:
- Cursor Design - Efficient cursors rely on well-filtered result sets
- Dynamic SQL - Building
WHERE clauses dynamically based on user input
- Performance Tuning - The effectiveness of indexes depends on efficient
WHERE clauses
Precise data filtering is not a “basic” skill to be forgotten but a core competency that underpins all efficient and correct database programming.