Queries and Joins

Filtering and sorting — nothing new here#

SELECT, WHERE, ORDER BY, GROUP BY, aggregate functions — all of it works against Oracle exactly as covered in this app's general SQL track. There's no Oracle-specific twist to re-learn for the basics:

SELECT last_name, salary
FROM employees
WHERE department_id = 20
ORDER BY salary DESC;

What genuinely differs in Oracle is join syntax history, and how pagination (LIMIT-style "give me the top N rows") works. Both are covered here using the employees/departments schema from the previous page.

The old (+) join syntax — recognize it, don't write it#

Long before Oracle supported the ANSI JOIN keyword, outer joins were written with a proprietary (+) operator placed in the WHERE clause, on the side of the join that's allowed to have no match:

-- Old Oracle syntax: every department, plus its employees if it has any
SELECT d.department_name, e.last_name
FROM departments d, employees e
WHERE d.department_id = e.department_id(+);

The (+) sits next to e.department_id — the "optional" side — meaning "include this row from departments even if there's no matching row in employees; if there isn't one, fill in NULL." That's precisely a LEFT JOIN from departments to employees, just spelled differently.

You'll still run into this syntax constantly in legacy Oracle codebases — some of it decades old — so it's worth being able to read it. But it has real, sharp limitations that are exactly why it was superseded:

• It cannot express a FULL OUTER JOIN at all.
• (+) cannot be combined with an OR against the joined column, and has historically fragile behavior combined with IN lists.
• With more than two tables, it's easy to write an ambiguous or unintentionally-wrong join, because the join conditions live scattered through WHERE rather than paired explicitly with each table.

Always write new Oracle code using the modern ANSI JOIN syntax — the same syntax covered in this app's general SQL track:

-- Modern syntax: identical result, unambiguous, supports FULL OUTER JOIN too
SELECT d.department_name, e.last_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id;

Oracle has fully supported ANSI join syntax since Oracle 9i (the early 2000s) — there's no version-compatibility reason left to reach for (+) in anything new.

Pagination: ROWNUM vs FETCH FIRST#

Oracle has a pseudo-column, ROWNUM, that numbers rows as they're returned — historically the only way to say "just give me some limited number of rows." It looks like it should work the way LIMIT does elsewhere:

SELECT * FROM employees WHERE ROWNUM <= 5;

That works fine on its own. The trap appears the moment ORDER BY gets involved:

-- This does NOT return the 5 highest-paid employees
SELECT * FROM employees
WHERE ROWNUM <= 5
ORDER BY salary DESC;

ROWNUM is assigned as rows are fetched from the table, before ORDER BY sorts the result — so WHERE ROWNUM <= 5 filters down to an arbitrary 5 rows first, and only then sorts those 5 by salary. You get the top 5 salaries among whichever 5 rows happened to be scanned first, not the 5 highest salaries in the whole table. This is one of the most common real-world Oracle bugs, and it's subtle precisely because the query runs without error and returns a plausible-looking result.

The fix, if you need to stick with ROWNUM, is to force the sort to happen first in an inner query, then apply ROWNUM in an outer query against the already-sorted result:

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

Since Oracle 12c, there's a much cleaner, standard-SQL way to say exactly this that sidesteps the whole gotcha — FETCH FIRST (with its sibling OFFSET for skipping rows):

-- The 5 highest-paid employees, correctly
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

-- Pagination: skip the first 10, return the next 10 (i.e., "page 2" of 10 per page)
SELECT * FROM employees
ORDER BY employee_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

FETCH FIRST/OFFSET are evaluated after the ORDER BY, so there's no ambiguity about whether you're limiting before or after the sort — this is the syntax to reach for in new Oracle code, exactly the same way modern ANSI JOIN replaced (+).

Comparison#

Common mistakes#

• Combining WHERE ROWNUM <= n with ORDER BY in the same query block and expecting the top-N-by-that-order result — it filters before sorting, not after.
• Assuming (+) and LEFT JOIN/RIGHT JOIN are always trivially interchangeable — (+) cannot express a full outer join and behaves unpredictably with certain OR/IN combinations, which is exactly why it was deprecated in favor of ANSI joins.
• Not aliasing tables in a multi-join query — Oracle raises an ambiguous-column error the moment two joined tables share a column name (both employees and departments could plausibly have a department_id-like column).
• Writing new Oracle code with (+) because that's what surrounding legacy code uses — it still works, but it's worth migrating to ANSI JOIN syntax in anything actively being written or touched.