inline views

Hierarchical Data in a Relational Table

Flatten Hierarchical Data using a Single SQL

Single Oracle SQL to find and flatten hierarchical data stored in a table, using a combination of SYS_CONNECT_BY_PATH and regular expressions.

Read the Full Article →
Thumbnail image for LEAD/LAG Functions to Place a Value within Extents

LEAD/LAG Functions to Place a Value within Extents

Analytic functions LEAD and LAG provide access to more than one row of a table at the same time, without a self join. This feature can be used to find the position of a value within extents, where the extents are defined by the upper bound only and the lower bound is derived via comparison to the previous/next row.

Read the Full Article →
Thumbnail image for 15 Things You Should Know about the ORDER BY Clause

15 Things You Should Know about the ORDER BY Clause

What is the precedence of sorting in a multi-column ORDER BY? How does ORDER BY sort nulls? What does ORDER SIBLINGS BY do? Answers to these questions and more about Oracle’s ORDER BY clause.

Read the Full Article →
Thumbnail image for What are Inline Views in Oracle?

What are Inline Views in Oracle?

An inline view is a subquery with an alias that you can use within a SQL statement, often used in queries for Top-N analysis. An inline view behaves just as if the subquery were a table name.

Read the Full Article →
Thumbnail image for Correlated Subquery

Correlated Subquery

A correlated subquery is a type of nested subquery that uses columns from the outer query in its WHERE clause. While a simple subquery is evaluated only once for each table, a correlated subquery is evaluated once for each row.

Read the Full Article →
Thumbnail image for Understanding Subqueries and Their Types

Understanding Subqueries and Their Types

A subquery – that is, a query within a query – can be of various flavors in Oracle. Subqueries may be simple or correlated, nested or scalar. A closer look at the types of subqueries and their purpose.

Read the Full Article →