WHERE Clause

The WHERE clause in Oracle SQL is used to filter records based on specified conditions. It restricts the rows returned in the result set by evaluating the condition(s) provided. The WHERE clause is one of the most commonly used components of SQL queries, and understanding how to use it effectively is crucial for retrieving the desired data from the database.

Key Points About WHERE Clause

1. Basic Syntax of WHERE Clause

The basic syntax for the WHERE clause is as follows:

SELECT column1, column2, ...

FROM table_name

WHERE condition;

• condition: The condition can be an expression, comparison, or logical combination of conditions.

 

2. Common Comparison Operators in the WHERE Clause

The WHERE clause allows various comparison operators to filter rows:

·        = (Equal to): Filters rows where the column value matches a specified value.

SELECT * FROM employees WHERE department_id = 10;

·        != or <> (Not equal to): Filters rows where the column value does not match the specified value.

SELECT * FROM employees WHERE department_id != 10;

·        > (Greater than): Filters rows where the column value is greater than the specified value.

SELECT * FROM employees WHERE salary > 50000;

·        < (Less than): Filters rows where the column value is less than the specified value.

SELECT * FROM employees WHERE salary < 50000;

·        >= (Greater than or equal to): Filters rows where the column value is greater than or equal to the specified value.

SELECT * FROM employees WHERE hire_date >= '2020-01-01';

·        <= (Less than or equal to): Filters rows where the column value is less than or equal to the specified value.

SELECT * FROM employees WHERE salary <= 60000;

 

3. Using Logical Operators in the WHERE Clause

Logical operators are often used to combine multiple conditions in the WHERE clause. The main logical operators include:

·        AND: Combines two or more conditions and returns rows where all conditions are true.

SELECT * FROM employees

WHERE department_id = 10 AND salary > 50000;

·        OR: Combines two or more conditions and returns rows where at least one condition is true.

SELECT * FROM employees

WHERE department_id = 10 OR salary > 50000;

·        NOT: Negates a condition, returning rows where the condition is false.

SELECT * FROM employees

WHERE NOT department_id = 10;

·        BETWEEN: Filters rows where a column value lies within a specific range (inclusive).

SELECT * FROM employees

WHERE salary BETWEEN 50000 AND 80000;

·        IN: Filters rows where the column value matches any value in a list of specified values.

SELECT * FROM employees

WHERE department_id IN (10, 20, 30);

·        LIKE: Filters rows based on pattern matching. It is typically used for text or string columns.

• %: Represents zero or more characters.
• _: Represents a single character.

SELECT * FROM employees

WHERE first_name LIKE 'J%';  -- Names starting with 'J'

·        IS NULL: Filters rows where a column value is NULL.

SELECT * FROM employees

WHERE commission_pct IS NULL;

·        IS NOT NULL: Filters rows where a column value is not NULL.

SELECT * FROM employees

WHERE commission_pct IS NOT NULL;

 

4. Combining Multiple Conditions

You can combine multiple conditions using logical operators such as AND, OR, and NOT to filter rows more precisely.

Example 1: Using AND

SELECT * FROM employees

WHERE department_id = 10 AND salary > 50000;

Example 2: Using OR

SELECT * FROM employees

WHERE department_id = 10 OR department_id = 20;

Example 3: Using NOT

SELECT * FROM employees

WHERE NOT department_id = 10;

Example 4: Using AND, OR, and NOT together

SELECT * FROM employees

WHERE (department_id = 10 OR department_id = 20) AND salary > 50000 AND NOT job_id = 'SA_REP';

 

5. Using WHERE with String Comparisons

You can use the WHERE clause to filter rows based on string values, including pattern matching with the LIKE operator.

·        Example with LIKE:

SELECT * FROM employees

WHERE last_name LIKE 'Smi%';  -- Names starting with 'Smi'

·        Example with NOT LIKE:

SELECT * FROM employees

WHERE last_name NOT LIKE 'Smi%';  -- Names not starting with 'Smi'

 

6. Using WHERE with Date Comparisons

In Oracle, dates are stored with time components. When using the WHERE clause with date values, you must ensure that the date format is correct.

·        Example with = for exact match:

SELECT * FROM employees

WHERE hire_date = TO_DATE('2020-01-01', 'YYYY-MM-DD');

·        Example with BETWEEN for a date range:

SELECT * FROM employees

WHERE hire_date BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD') 

                    AND TO_DATE('2021-01-01', 'YYYY-MM-DD');

 

7. Performance Considerations with WHERE Clause

• Indexes: The WHERE clause is often used with columns that are indexed, which can significantly improve query performance.
• Avoiding Full Table Scans: Make sure the WHERE clause is written in such a way that it utilizes available indexes for better performance. For example, avoid functions or operations that may cause Oracle to perform a full table scan.

 

8. WHERE vs HAVING

• The WHERE clause filters rows before the grouping (i.e., before the GROUP BY clause).
• The HAVING clause is used to filter rows after the GROUP BY operation, often with aggregate functions.

Example using WHERE:

SELECT department_id, COUNT(*)

FROM employees

WHERE salary > 50000

GROUP BY department_id;

Example using HAVING:

SELECT department_id, COUNT(*)

FROM employees

GROUP BY department_id

HAVING COUNT(*) > 5;

 

9. Examples of WHERE Clause in Queries

Example 1: Filter by Single Condition

SELECT * FROM employees

WHERE department_id = 10;

Example 2: Using AND to Combine Conditions

SELECT * FROM employees

WHERE department_id = 10 AND salary > 50000;

Example 3: Using OR to Combine Conditions

SELECT * FROM employees

WHERE department_id = 10 OR department_id = 20;

Example 4: Using LIKE for Pattern Matching

SELECT * FROM employees

WHERE last_name LIKE 'Smi%';

Example 5: Using BETWEEN for Range Comparison

SELECT * FROM employees

WHERE salary BETWEEN 50000 AND 100000;

Example 6: Using IN for Multiple Value Match

SELECT * FROM employees

WHERE department_id IN (10, 20, 30);

Example 7: Filtering with NULL

SELECT * FROM employees

WHERE commission_pct IS NULL;