SQL LEVEL

What is the SQL LEVEL pseudocolumn?

LEVEL is an Oracle pseudocolumn that returns the depth (starting at 1) of each row in a hierarchical SELECT that uses CONNECT BY.

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 LEVEL: Oracle (native), Snowflake (supports CONNECT BY and LEVEL), MariaDB 10.2+ (CONNECT BY and LEVEL), PostgreSQL (via the orafce extension), IBM Db2 (LEVEL keyword inside CONNECT BY clause). Not available in standard MySQL, SQL Server, or SQLite without emulation.

SQL LEVEL Full Explanation

LEVEL is not a table column but a pseudocolumn automatically supplied by Oracle Database (and some compatible dialects) when a query includes a hierarchical clause (CONNECT BY). It counts how many parent-child traversals were required to reach the current row, starting at 1 for the root nodes identified in START WITH. Because it is computed at runtime, LEVEL cannot be updated or indexed. It is evaluated after the CONNECT BY logic, so filtering on LEVEL in a WHERE clause acts on the generated hierarchy, while filtering in a CONNECT BY clause influences the recursion itself. LEVEL is available only in statements that actually contain CONNECT BY, hierarchical subquery factoring USING NOCYCLE CONNECT BY, or recursive subquery factoring constructs that Oracle internally rewrites to a CONNECT BY plan.

SQL LEVEL Syntax

SELECT column_list,
       LEVEL
FROM   table_name
START WITH <root-condition>
CONNECT BY <parent-child-condition>;

SQL LEVEL Parameters

Example Queries Using SQL LEVEL

--Basic hierarchy with employees
SELECT employee_id,
       manager_id,
       LEVEL AS depth
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

--Filter rows deeper than level 2
SELECT employee_id,
       LEVEL
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
WHERE  LEVEL > 2;

--Indent names based on LEVEL to show tree visually
SELECT LPAD(' ', (LEVEL-1)*2) || last_name AS indented_name
FROM   employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Expected Output Using SQL LEVEL

Each query returns one row per node in the hierarchy
The LEVEL column contains 1 for root rows, 2 for their direct children, 3 for grandchildren, and so on
In the indented_name example, the LPAD creates a text tree reflecting LEVEL depth

Use Cases with SQL LEVEL

Building organizational charts
Expanding bill of materials trees
Producing category or menu hierarchies
Quickly identifying depth of records without self-joins or recursive CTE syntax

Common Mistakes with SQL LEVEL

Using LEVEL without a CONNECT BY clause (raises ORA-01788)
Expecting LEVEL to exist in databases that do not implement Oracle style hierarchies
Filtering on LEVEL in the CONNECT BY clause instead of WHERE, which can unintentionally prune paths
Forgetting that LEVEL starts at 1, not 0

First Introduced In

Oracle7 Database (1992)

Frequently Asked Questions

What does LEVEL indicate in a hierarchical query?

LEVEL shows how deep a row is in the hierarchy produced by CONNECT BY. A value of 1 is a root row, 2 is its child, 3 is a grandchild, and so forth.

Does LEVEL exist in standard ANSI SQL?

No. LEVEL is Oracle specific. Other systems rely on recursive CTEs where you must explicitly calculate a depth column.

Can I filter rows by LEVEL?

Yes. Add a WHERE clause such as WHERE LEVEL <= 3 to restrict the output after the hierarchy is built.

Is LEVEL available outside SELECT queries?

It is primarily for SELECT. Using LEVEL in INSERT or UPDATE statements without a hierarchical SELECT subquery will cause an error.