SQL PRIOR

What is the SQL PRIOR keyword?

PRIOR references the parent row in Oracle hierarchical (CONNECT BY) queries.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL PRIOR: Oracle Database, Autonomous Database, Oracle-compatible engines (YugabyteDB YCQL in Oracle mode, Amazon Redshift RDS for Oracle). Not supported in PostgreSQL, MySQL, SQL Server, SQLite, or standard ANSI SQL.

SQL PRIOR Full Explanation

PRIOR is an Oracle-specific keyword used only inside a CONNECT BY clause to build hierarchical result sets. It tells the optimizer which side of the comparison refers to the parent row and which side refers to the child row. By marking one column with PRIOR, Oracle can traverse self-referencing tables (such as employee-manager or folder-subfolder structures) and return rows in depth-first order while automatically populating the pseudo-columns LEVEL, CONNECT_BY_ISCYCLE, and CONNECT_BY_ROOT. PRIOR does not change data; it only influences how rows are matched during recursive evaluation. The keyword can appear on either side of the comparison, letting you control parent-to-child or child-to-parent navigation. Outside CONNECT BY, PRIOR has no meaning and will raise a syntax error.

SQL PRIOR Syntax

SELECT column_list
FROM   table_name
START WITH <root_condition>
CONNECT BY PRIOR parent_col = child_col;

SQL PRIOR Parameters

Example Queries Using SQL PRIOR

-- Standard employee hierarchy
SELECT employee_id,
       manager_id,
       LEVEL AS lvl
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- Reverse traversal (child to parent)
SELECT employee_id,
       manager_id,
       LEVEL
FROM   employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id;

Expected Output Using SQL PRIOR

Rows are returned in hierarchical order
LEVEL starts at 1 for START WITH rows and increments by 1 for each descendant
The second query walks up the chain until the root is reached

Use Cases with SQL PRIOR

Building employee or organizational charts
Displaying nested folder or category trees
Calculating aggregated metrics along parent-child paths
Finding ancestors or descendants without iterative client code

Common Mistakes with SQL PRIOR

Using PRIOR outside CONNECT BY (raises ORA-00904)
Omitting PRIOR and creating an infinite self-join
Reversing the comparison direction and getting an empty set
Forgetting a START WITH clause and retrieving unintended roots

First Introduced In

Oracle7 (1992)

Frequently Asked Questions

What does PRIOR do in Oracle SQL?

PRIOR tells Oracle which column in the CONNECT BY comparison belongs to the parent row, enabling hierarchical traversal.

Do other databases support PRIOR?

No. PostgreSQL, MySQL, SQL Server, and SQLite do not recognize PRIOR. Use recursive CTEs instead.

Is PRIOR mandatory in CONNECT BY?

Yes. Without at least one PRIOR, Oracle cannot distinguish parent from child rows and raises ORA-01436.

Can PRIOR appear on either side of the equals sign?

Absolutely. Place PRIOR on the column that belongs to the parent row to control traversal direction.