Oracle SQL WHERE and ORDER BY: Filtering and Sorting

Oracle SQL WHERE Clause and ORDER BY: Complete Guide

The WHERE clause filters rows before they are returned, while ORDER BY controls the sort order of the final result set. These two clauses are fundamental to writing precise, readable, and efficient SQL queries in Oracle.

Learning Objectives
By the end of this guide you will master all comparison operators, range filtering with BETWEEN, pattern matching with LIKE, logical operator precedence, NULL handling, and advanced ORDER BY techniques including aliases and NULL positioning.

1. Comparison Operators

Basic comparison operators work with numbers, strings, and dates. String comparisons are case-sensitive by default.

-- Numeric comparisons
WHERE salary > 5000
WHERE salary != 5000          -- also written as <> or ^=

-- String comparisons (case-sensitive)
WHERE last_name = 'King'      -- matches only 'King', not 'king' or 'KING'
WHERE last_name > 'M'         -- names that come after 'M' alphabetically

-- Date comparisons
WHERE hire_date > TO_DATE('01-JAN-2020', 'DD-MON-YYYY');

Case sensitivity matters: WHERE last_name = 'king' will not match a row stored as 'King'. Always match the exact case stored in the database.

2. BETWEEN — Inclusive Range Filtering

BETWEEN includes both boundary values. It is a convenient shorthand for two comparison conditions.

WHERE salary BETWEEN 5000 AND 10000;
-- Equivalent to: salary >= 5000 AND salary <= 10000

WHERE last_name BETWEEN 'A' AND 'C';
-- Includes names starting with A, B, or exactly 'C'

Lower bound must come first: BETWEEN 10000 AND 5000 returns zero rows. Oracle does not automatically reverse the range.

3. IN and NOT IN

IN is shorthand for multiple OR conditions. It works with lists or subqueries.

WHERE job_id IN ('IT_PROG', 'SA_REP', 'AD_PRES');

WHERE department_id NOT IN (10, 20, 30);

Important NULL behavior:
• IN with NULL in the list simply ignores the NULL.
• NOT IN with any NULL in the list returns **zero rows** (a common gotcha).

4. LIKE — Pattern Matching

LIKE allows partial string matching using two wildcards:

Wildcard	Meaning	Example
%	Zero or more characters	'S%' matches 'Smith', 'SQL', 'S'
_	Exactly one character	'_ing' matches 'King', 'Ring'

WHERE last_name LIKE 'S%'          -- starts with S
WHERE last_name LIKE '%son'        -- ends with son
WHERE last_name LIKE '%ar%'        -- contains "ar" anywhere
WHERE last_name LIKE '_ing'        -- exactly 4 characters ending in "ing"

5. LIKE with ESCAPE Clause

To search for a literal % or _, use the ESCAPE clause.

WHERE job_id LIKE 'SA\_%' ESCAPE '\';
-- Matches 'SA_REP', 'SA_MAN' (literal underscore)

WHERE comments LIKE '%50\%%' ESCAPE '\';
-- Matches strings containing the literal text "50%"

6. Logical Operators — AND, OR, NOT and Precedence

Logical operators combine conditions. Precedence is important:

Priority	Operator
1 (highest)	Comparison operators (=, >, LIKE, BETWEEN, IN, IS NULL)
2	NOT
3	AND
4 (lowest)	OR

-- AND has higher precedence than OR
WHERE job_id = 'SA_REP' 
   OR job_id = 'IT_PROG' 
  AND salary > 8000;

-- Use parentheses for clarity
WHERE (job_id = 'SA_REP' OR job_id = 'IT_PROG')
  AND salary > 8000;

Always use parentheses with mixed AND/OR: They make your intent clear and prevent unexpected results.

7. ORDER BY — Sorting Rules

ORDER BY is the last clause executed and can reference column aliases or positions.

SELECT last_name, salary * 12 AS annual_sal
FROM   employees
ORDER BY last_name;                    -- by column name

ORDER BY annual_sal DESC;              -- by alias (only possible in ORDER BY)

ORDER BY 2 DESC;                       -- by column position (2nd column in SELECT)

ORDER BY department_id, salary DESC;   -- multi-column sort

8. ORDER BY with NULL Values

Oracle treats NULL as the largest value when sorting.

Sort direction	Default NULL position
ASC	NULLs appear last
DESC	NULLs appear first

ORDER BY commission_pct ASC;           -- NULLs at the bottom
ORDER BY commission_pct DESC;          -- NULLs at the top

-- Override default
ORDER BY commission_pct ASC NULLS FIRST;
ORDER BY commission_pct DESC NULLS LAST;

9. Interactive Practice Quiz

Q1. What does this WHERE clause return?
WHERE department_id NOT IN (10, 20, NULL)

A. Rows where department_id is not 10 and not 20 B. All rows (NULL is ignored) C. Zero rows D. Rows where department_id is NULL

Q2. In ORDER BY commission_pct ASC, where do NULL values appear?

A. At the top B. At the bottom C. In random order D. Excluded from results

Q3. Without parentheses, how is this evaluated?
WHERE job_id = 'SA_REP' OR job_id = 'IT_PROG' AND salary > 8000

A. (SA_REP OR IT_PROG) AND salary > 8000 B. SA_REP OR (IT_PROG AND salary > 8000) C. All three conditions evaluated left to right D. AND and OR have equal precedence

Conclusion

The WHERE clause and ORDER BY are the foundation of effective data retrieval in Oracle SQL. Mastering comparison operators, pattern matching, logical precedence, and NULL handling will help you write precise filters and perfectly sorted results every time.

Practice these patterns regularly — especially the NULL behavior in NOT IN and the default NULL sorting rules — and you will write cleaner, more reliable queries.