Functions and Analytic Queries

Core built-in functions#

A handful of Oracle-specific functions show up in almost every real query. None of these are ANSI-standard SQL — they're Oracle's own, and worth knowing by name.

NVL and NVL2 — substituting for NULL#

NVL(expr, replacement) returns expr if it isn't NULL, otherwise replacement — the same idea as COALESCE, which Oracle also supports, but NVL only ever takes two arguments and is far more commonly seen in existing Oracle code:

SELECT last_name, NVL(job_title, 'Unassigned') AS job_title
FROM employees;

NVL2(expr, value_if_not_null, value_if_null) is a less common but handy variant — it branches on whether expr is NULL, but returns a different expression in each branch rather than just substituting a default:

SELECT last_name, NVL2(department_id, 'Assigned', 'Unassigned') AS department_status
FROM employees;

DECODE — Oracle's original CASE#

Before CASE expressions existed in Oracle at all, DECODE was the way to branch inside a SELECT. It compares an expression against a list of values pairwise and returns the matching result, with an optional trailing default:

SELECT last_name,
       DECODE(department_id, 10, 'Admin', 20, 'Sales', 30, 'IT', 'Other') AS department_label
FROM employees;

This is exactly equivalent to:

SELECT last_name,
       CASE department_id
           WHEN 10 THEN 'Admin'
           WHEN 20 THEN 'Sales'
           WHEN 30 THEN 'IT'
           ELSE 'Other'
       END AS department_label
FROM employees;

DECODE still appears constantly in existing Oracle code (and has one genuine edge CASE lacks: it treats two NULLs as equal for matching purposes), but for new code, CASE is the ANSI-standard, more readable choice — especially once the branching condition is a range or a boolean expression rather than a flat equality list.

TO_CHAR and TO_DATE — format models#

Converting between strings, numbers, and dates in Oracle goes through explicit format models rather than implicit conversion rules:

SELECT TO_CHAR(hire_date, 'YYYY-MM-DD') AS hired_on,
       TO_CHAR(salary, 'FM999,999,990.00') AS formatted_salary
FROM employees;

SELECT * FROM employees
WHERE hire_date >= TO_DATE('2024-01-01', 'YYYY-MM-DD');

Common format elements: YYYY (4-digit year), MM (month number), DD (day), HH24:MI:SS (24-hour time), Month/Mon (full/abbreviated month name), Day/Dy (day name). Always pass an explicit format mask rather than relying on the session's default NLS_DATE_FORMAT — a query that works fine on your own connection can silently misparse dates on a session configured with different regional defaults.

SUBSTR and INSTR — string slicing and searching#

SUBSTR(string, start_position, length) extracts a substring (Oracle strings are 1-indexed, and a negative start_position counts from the end):

SELECT SUBSTR(email, 1, INSTR(email, '@') - 1) AS username
FROM employees;

INSTR(string, substring) returns the 1-based position of the first occurrence of substring within string (or 0 if not found) — the pair above is the standard Oracle idiom for "everything before the @" in an email column, since Oracle has no SPLIT-style function on plain VARCHAR2.

Analytic (window) functions#

Analytic functions are where Oracle SQL goes well beyond simple aggregation. A GROUP BY aggregate collapses many rows into one per group — an analytic function computes a value across a group of rows while still returning one row per original row, which is exactly what you need for things like "this employee's salary rank within their department" without losing every other column on that row.

Every analytic function shares the same shape: function() OVER (PARTITION BY ... ORDER BY ...).

• PARTITION BY — splits the result into independent groups, restarting the calculation at the start of each group (directly analogous to GROUP BY, but without collapsing rows).
• ORDER BY (inside the OVER clause) — defines the order the function processes rows in within each partition — required for ranking and running-total functions, irrelevant for something like a plain per-partition SUM with no ordering dependency.

RANK, DENSE_RANK, and ROW_NUMBER#

These three all assign a position to each row within its partition, ordered by some column — and differ only in how they handle ties:

SELECT employee_id, last_name, department_id, salary,
       RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_dense_rank,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_row_number
FROM employees
WHERE department_id = 20
ORDER BY salary DESC;

With two employees tied at the top salary in department 20:

EMPLOYEE_ID  LAST_NAME  SALARY  SALARY_RANK  SALARY_DENSE_RANK  SALARY_ROW_NUMBER
201          Okafor     9500    1            1                  1
205          Bianchi    9500    1            1                  2
202          Lindgren   8700    3            2                  3
203          Suzuki     8200    4            3                  4

• RANK() gives tied rows the same rank, then skips the ranks that would follow — after two rows tied at rank 1, the next row is rank 3, not 2.
• DENSE_RANK() also gives tied rows the same rank, but doesn't skip — the next distinct value gets the very next integer.
• ROW_NUMBER() never ties — every row gets a unique, strictly increasing number, with ties broken arbitrarily (by whatever order the database happens to process them in, unless the ORDER BY includes a further tiebreaker column).

Reach for DENSE_RANK() when you want "the Nth highest distinct value" with no gaps (used heavily on the tricky-queries page later in this track), RANK() when skipped positions genuinely reflect standing (like a race — two people tied for 1st means nobody is 2nd), and ROW_NUMBER() whenever you need a guaranteed-unique row per position, such as picking exactly one row per group.

LAG and LEAD — looking at neighboring rows#

LAG() and LEAD() fetch a value from a preceding or following row in the same ordered partition, without needing a self-join:

SELECT employee_id, last_name, hire_date,
       LAG(hire_date)  OVER (ORDER BY hire_date) AS previous_hire_date,
       LEAD(hire_date) OVER (ORDER BY hire_date) AS next_hire_date
FROM employees
WHERE department_id = 20;

This is the natural tool for "how does this row compare to the one before/after it" — month-over-month change, comparing an employee's salary to the next-highest-paid peer, and similar sequential comparisons that would otherwise require a self-join on a computed row number.

Running totals with SUM() OVER#

The same OVER mechanism applies to ordinary aggregates, not just ranking functions — an ORDER BY inside OVER turns a plain SUM into a running total, since by default the window extends from the start of the partition up to the current row:

SELECT employee_id, last_name, department_id, hire_date, salary,
       SUM(salary) OVER (
           PARTITION BY department_id
           ORDER BY hire_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_dept_salary_total
FROM employees
WHERE department_id = 20
ORDER BY hire_date;

EMPLOYEE_ID  LAST_NAME  HIRE_DATE   SALARY  RUNNING_DEPT_SALARY_TOTAL
201          Okafor     2019-03-11  9500    9500
202          Lindgren   2020-07-02  8700    18200
203          Suzuki     2021-11-19  8200    26400
205          Bianchi    2023-05-30  9500    35900

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the explicit window frame — "every row from the start of this partition up through the current one" — and it's actually the default frame whenever ORDER BY is present with no explicit frame given, so SUM(salary) OVER (PARTITION BY department_id ORDER BY hire_date) alone produces the identical running total. Writing the frame out explicitly is good practice anyway, since it makes the intent unambiguous to the next reader.

Worked example: ranking employees by salary within department#

Putting several of these together in one query — every employee's salary rank inside their own department, alongside the department's running salary total by hire date:

SELECT e.employee_id, e.last_name, d.department_name, e.salary,
       RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS dept_salary_rank,
       SUM(e.salary) OVER (
           PARTITION BY e.department_id
           ORDER BY e.hire_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_dept_salary_total
FROM employees e
JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, dept_salary_rank;

Notice this reads naturally alongside a regular JOIN — analytic functions compose with the rest of standard SQL rather than replacing it; they're evaluated after WHERE/GROUP BY/HAVING but before the final ORDER BY and SELECT list are applied.

Common mistakes#

• Forgetting PARTITION BY when a per-group ranking was intended — without it, RANK()/ROW_NUMBER()/SUM() OVER (...) operate across the entire result set as one partition, silently producing a global rank or a running total that ignores department boundaries entirely.
• Confusing RANK() (skips positions after a tie) with DENSE_RANK() (doesn't skip) — picking the wrong one changes which row is "3rd place" the moment there's any tie in the data.
• Assuming an analytic function belongs in a WHERE clause — it doesn't; analytic functions are computed after WHERE filtering, so WHERE dept_salary_rank = 1 fails with an error. Wrap the query in a subquery or CTE and filter the outer query instead.
• Omitting ORDER BY inside OVER() for a running total and getting a full-partition sum on every row instead of a true row-by-row accumulation — the ORDER BY inside OVER is what makes it a running total rather than one repeated grand total.