**PL/SQL Procedure**: CREATE OR REPLACE PROCEDURE name(param IN TYPE) AS BEGIN ... END; /

**Package**: CREATE OR REPLACE PACKAGE name AS ... END; / CREATE OR REPLACE PACKAGE BODY name AS ... END; /

**Partitioning**: PARTITION BY RANGE/LIST/HASH (column) (...)

**Materialized View**: CREATE MATERIALIZED VIEW name REFRESH FAST/COMPLETE ON COMMIT/DEMAND AS SELECT ...

**ROWNUM**: WHERE ROWNUM <= n (top n rows, assigned before ORDER BY)

**FETCH FIRST**: ORDER BY column OFFSET n ROWS FETCH FIRST m ROWS ONLY (Oracle 12c+)

**ROW_NUMBER**: ROW_NUMBER() OVER (ORDER BY column) (window function)

PL/SQL procedures: CREATE OR REPLACE PROCEDURE name(param IN/OUT TYPE) AS BEGIN ... END; /

Packages: CREATE OR REPLACE PACKAGE name AS ... END; / and PACKAGE BODY for implementation

Partitioning: PARTITION BY RANGE/LIST/HASH (column) (...) - requires Enterprise Edition

Materialized views: CREATE MATERIALIZED VIEW name REFRESH FAST/COMPLETE ON COMMIT/DEMAND AS SELECT

ROWNUM: WHERE ROWNUM <= n (assigned before ORDER BY, legacy)

FETCH FIRST: ORDER BY column OFFSET n ROWS FETCH FIRST m ROWS ONLY (Oracle 12c+, ANSI standard)

FOR UPDATE: SELECT ... FOR UPDATE locks rows for update, prevents race conditions

RETURNING: INSERT/UPDATE/DELETE ... RETURNING column INTO variable

PL/SQL, packages, RAC, advanced partitioning, materialized views with query rewrite, ROWNUM/FETCH FIRST, Enterprise Edition required for advanced features

T-SQL, no packages (use schemas), Always On (similar to RAC), partitioning in Enterprise, indexed views (similar to materialized views), OFFSET/FETCH

PL/pgSQL, no packages, no built-in clustering (use Patroni), declarative partitioning (10+), materialized views (no query rewrite), LIMIT/OFFSET

Stored procedures (limited), no packages, no clustering (use Galera), partitioning (limited types), no materialized views, LIMIT

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.

salary >= 80000

BETWEEN

Checks whether a value falls inside an inclusive lower/upper range.

order_total BETWEEN 100 AND 500

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

DATE

Stores a calendar date without any time-of-day component.

DATE '2026-04-18'