SQL subqueries and their types, articles and demos on how to use subqueries.
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.
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?
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.
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:
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:
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.
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!
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:
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:
A subquery is – to put it simply – a query within a query.
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: