Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 38 ยท SQL in Oracle

Filtering and Sorting in Oracle

Filter rows with WHERE, LIKE, IN and BETWEEN, sort with NULLS FIRST or LAST, and limit results with FETCH FIRST, OFFSET and the classic ROWNUM trap.

Real tables hold thousands or millions of rows, and you rarely want all of them. WHERE filters rows, ORDER BY sorts what is left, and Oracle's row-limiting clause returns just the top few. Oracle handles the last of these differently from MySQL, PostgreSQL and SQLite, so this lesson spends time on it.

Most examples use the company database in the editor. Where Oracle syntax differs, the Oracle form is shown as a comment above a runnable stand-in.

Filtering with WHERE

WHERE keeps only the rows for which its condition is true. Conditions combine with AND, OR and NOT; use parentheses so the order is clear (AND binds tighter than OR).

SQL Comparison and logic ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Oracle's comparison operators are =, <> (also != and ^=), <, <=, > and >=. Note that Oracle compares text case-sensitively by default: 'smith' = 'Smith' is false.

BETWEEN, IN and LIKE

SQL
WHERE salary BETWEEN 70000 AND 90000       -- inclusive on both ends
WHERE department_id IN (1, 3, 5)           -- same as three OR conditions
WHERE last_name LIKE 'Ok%'                 -- starts with Ok
WHERE last_name LIKE '_a%'                 -- second letter is a
WHERE email NOT LIKE '%@company.example'   -- does not end with the domain

% matches any run of characters (including none) and _ matches exactly one character. To search for a literal % or _, declare an escape character: LIKE '50\%' ESCAPE '\'.

SQL BETWEEN, IN and LIKE ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

For case-insensitive matching, compare UPPER(col) or LOWER(col), or use REGEXP_LIKE(col, 'pattern', 'i').

NULL and three-valued logic

WHERE manager_id = NULL never matches, because NULL = NULL is unknown, not true. Use IS NULL. And be careful with NOT IN: if the list contains a NULL, the whole condition is never true, so no rows return.

SQL NULL traps ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The first count returns 1, the second 0, and the third query returns no rows at all.

Sorting with ORDER BY

Without ORDER BY, Oracle does not promise any row order. Add it whenever order matters:

SQL
SELECT last_name, salary FROM employees
ORDER BY salary DESC, last_name ASC;

You can sort by column names, by expressions, by a SELECT alias or by position (ORDER BY 2). Unlike WHERE, ORDER BY may use aliases.

NULLS FIRST and NULLS LAST

In Oracle, NULL is treated as larger than any value. That means ORDER BY col ASC puts NULLs last and DESC puts them first. Override this explicitly:

SQL Controlling where NULLs go ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

This syntax is the same in Oracle, which uses FETCH FIRST 6 ROWS ONLY in place of LIMIT 6. A good habit is to write NULLS FIRST or NULLS LAST whenever the column can be null.

Limiting rows: FETCH FIRST

Oracle 12c introduced the standard row-limiting clause. It goes at the end of the query:

SQL
SELECT last_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;
Clause Meaning
FETCH FIRST n ROWS ONLY The first n rows
FETCH FIRST n ROWS WITH TIES The first n rows plus any row tied with the last one
FETCH FIRST 10 PERCENT ROWS ONLY The first ten percent of rows
OFFSET m ROWS FETCH NEXT n ROWS ONLY Skip m rows, then take n (paging)

Paging page 3 with 10 rows per page reads:

SQL
SELECT last_name, salary FROM employees
ORDER BY salary DESC, employee_id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Always sort on a unique combination (add the primary key as a tie breaker) so pages do not overlap. Stand-in using LIMIT 10 OFFSET 20:

SQL Stand-in for paging ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The ROWNUM trap

Before 12c, everyone used ROWNUM, and you will find it in old code. ROWNUM is assigned as rows come out of the WHERE clause, before ORDER BY. So this does not return the five highest salaries:

SQL
-- WRONG: takes five arbitrary rows, then sorts those five
SELECT last_name, salary FROM employees
WHERE ROWNUM <= 5
ORDER BY salary DESC;

The fix is to sort in an inline view first, then cut:

SQL
SELECT * FROM (
  SELECT last_name, salary FROM employees ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

Two more ROWNUM facts: WHERE ROWNUM = 1 works, but WHERE ROWNUM = 2 or ROWNUM > 1 returns nothing, because the first candidate row always becomes row number 1 and is then rejected, so the counter never advances. For "rows 11 to 20" use a nested query that exposes ROWNUM as a column, or simply use OFFSET ... FETCH.

Stand-in with a window function, which gives the same numbering after sorting:

SQL Stand-in for the sorted ROWNUM pattern ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In Oracle, where do NULL values appear with ORDER BY col ASC by default?

    Oracle treats NULL as larger than any value, so ascending order puts NULLs last. Use NULLS FIRST to change it.
  2. 2. What does SELECT x FROM t WHERE ROWNUM <= 3 ORDER BY x DESC return?

    ROWNUM is applied before ORDER BY, so Oracle picks three rows first and then sorts them. Use FETCH FIRST 3 ROWS ONLY, or sort in an inline view first.
  3. 3. Which query skips 20 rows and returns the next 10?

    OFFSET m ROWS FETCH NEXT n ROWS ONLY is Oracle's standard paging syntax. LIMIT does not exist in Oracle, and ROWNUM BETWEEN 20 AND 30 returns nothing.
  4. 4. SELECT * FROM t WHERE x NOT IN (1, 2, NULL) returns:

    A NULL in a NOT IN list makes the condition unknown for every row, so nothing matches.
  5. 5. Which pattern matches last names that start with Ok?

    % matches any number of characters. 'Ok_' would match exactly one more character.

Summary

  • WHERE filters rows; AND binds tighter than OR; text comparison is case-sensitive in Oracle.
  • Test NULL with IS NULL; a NULL inside NOT IN makes it match nothing.
  • ORDER BY is needed for any guaranteed order; use NULLS FIRST or NULLS LAST explicitly.
  • Limit rows with FETCH FIRST n ROWS ONLY and page with OFFSET ... FETCH NEXT.
  • ROWNUM is applied before sorting; sort in an inline view first or avoid it.

Next: Creating Tables and Constraints