Subqueries

Pagination Queries in Oracle: Analytics, Row Limiting Clause

Pagination is the process of dividing query results (typically on a user interface) into discrete pages, where each page contains a smaller/more manageable number of rows. In classic offset pagination, each page shows a fixed count of rows (say N): if N is 20, then the first page shows 1-20 rows, a “Next” navigation moves control to the second page with 21-40 rows, and so on.

This article demonstrates how to write queries to achieve this style of pagination in Oracle.

[click to continue…]

{ 2 comments }

SQL to Find Master Records with Identical Detail Records

The query to identify duplicate records in Oracle in a single table is fairly straightforward. Things get tricky in a two-table (master-detail) scenario, in which the master table holds the header information (id, name, etc) and the detail table has sets of values associated with the master records.

What if we need to find those master records that have identical sets of values in the detail table? Can a single SQL list master records with identical detail records?

[click to continue…]

{ 0 comments }

PIVOT in Oracle 11G to Select Rows As Columns

A frequent requirement in SQL is to "pivot" a result set — that is, display rows as columns. Before 11G, the developer had to do the equivalent of jumping through hoops to pivot the data. In 11G, with the introduction of the new PIVOT syntax, the task of transposing rows to columns has become a lot more intuitive.

This post shows the use of PIVOT with an example and sample scripts.

[click to continue…]

{ 7 comments }

DISTINCT and How NOT To Use It

The DISTINCT keyword placed next to SELECT restricts the result to unique rows from a query.

DISTINCT is also a much abused keyword, often used as a quick fix to bad queries. Take this example from Oracle Applications:

[click to continue…]

{ 3 comments }

Scalar Subquery: SELECT within SELECT!

A subquery in the SELECT clause of the main query is called a scalar subquery.

This is a single row, single column query, which looks just like a column or function in the SELECT clause. The structure is:
[click to continue…]

{ 0 comments }

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.

A big mistake.

See how NOT IN and NOT EXISTS behave differently in this small example.

[click to continue…]

{ 2 comments }

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!

Huh? What about that thumb rule – IN for small inner query, EXISTS for big inner query?

[click to continue…]

{ 1 comment }

What are Inline Views in Oracle?

An inline view is a subquery with an alias that you can use within a SQL statement. An inline view behaves just as if the subquery were a table name.

A classic use of inline views is in queries for Top-N analysis. See the one used for finding Nth row from a table:

[click to continue…]

{ 4 comments }

Correlated Subquery

A correlated subquery is a type of nested subquery that uses columns from the outer query in its WHERE clause.

For example, a query to list employees whose salary is more than their department’s average:

[click to continue…]

{ 3 comments }

Understanding Subqueries and Their Types

A subquery is – to put it simply – a query within a query.

What purpose does a subquery serve?

A subquery may be needed when it takes more than a single step to reach the answer.

Suppose we need to find all employees who work in the same department as KING. We need to:

[click to continue…]

{ 0 comments }