Subqueries

SQL subqueries and their types, articles and demos on how to use subqueries.

Thumbnail image for Pagination Queries in Oracle: Analytics, Row Limiting Clause

Pagination Queries in Oracle: Analytics, Row Limiting Clause

How to implement pagination queries in Oracle, using (1) analytic functions (2) Oracle’s row limiting clause to fetch N rows from a given offset.

Read the Full Article →
Thumbnail image for SQL to Find Master Records with Identical Detail Records

SQL to Find Master Records with Identical Detail Records

SQL to find those records in the master table that have identical detail records in a child table.

Read the Full Article →
Thumbnail image for PIVOT in Oracle 11G to Select Rows As Columns

PIVOT in Oracle 11G to Select Rows As Columns

SQL to PIVOT a result set in Oracle — that is, display rows as columns. Pre-11G solution and a simple example using the 11G+ PIVOT syntax.

Read the Full Article →
Thumbnail image for DISTINCT and How NOT To Use It

DISTINCT and How NOT To Use It

Oracle keyword DISTINCT restricts the result to unique rows from a query, but DISTINCT is often used as a quick fix to bad queries. Some practices to avoid when using DISTINCT.

Read the Full Article →

Scalar Subquery: SELECT within SELECT!

A scalar subquery is a single row, single column query, which looks just like a column or function in the SELECT clause. Here’s a look at the unique characteristics and benefits of this feature in Oracle.

Read the Full Article →
Thumbnail image for IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!

IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!

In Oracle SQL queries, IN and EXISTS are interchangeable. Many of us assume therefore, that NOT IN and NOT EXISTS are also interchangeable. That’s not the case though.

Read the Full Article →
Thumbnail image for Which is faster – IN or EXISTS?

Which is faster – IN or EXISTS?

A question asked multiple times over on Oracle forums: Which is faster – IN or EXISTS? The short answer, post-Oracle 9i is: Both are pretty much the same!

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 →