Subqueries

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

SQL to Find Master Records with Identical Detail Records

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 […]

Read the Full Article →
PIVOT in Oracle to Select Rows As Columns

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 […]

Read the Full Article →
DISTINCT Keyword in Oracle

DISTINCT and How NOT To Use It

The DISTICT 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:

Read the Full Article →
Scalar Subquery

Scalar Subquery

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:

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 our last article we said that in 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.

Read the Full Article →
Oracle SQL: IN vs 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! Huh? What about that thumb rule – IN for small inner query, EXISTS for big inner query?

Read the Full Article →
Inline Views

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 was 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:

Read the Full Article →
Correlated Subquery in SQL

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:

Read the Full Article →
Subqueries and Their Types

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:

Read the Full Article →