Self Joins

SQL has traditionally been a poor tool for analysing data in context. ie. Selecting information about a row that requires input from neighboring rows. For instance, to select information about the highest salary earner in each department, we must first find the employees with the highest salary, and then select their details:

Queries that are context sensitive have one thing in common: they must refer to the same table more than once. The example above is remarkably simple; more complex queries must access the same table 3, 4 or more times.

Oracle 8i has supplied a new feature called Analytic Functions which are unique in SQL: they allow a row to access information from its neighbors without having to perform a self-join. Analytic functions are difficult to get the hang of, but once mastered are quite easy and even intuitive! See the Oracle SQL Reference for more information on Analytic Functions.

The query above can be re-written with analytic functions as follows:

Be aware that Analytic Functions are not necessarily quicker than self joins. Even though they seem to perform fewer and more simple steps, queries like the one above can be more efficient when written as a self join. Always trial both option to find the fastest.

If only a single result is required (eg. One employee with highest salary, rather than all with that salary) then it is even faster to use the KEEP modifier to the aggregate function.

Another option is to process the data in a procedural language such as PL/SQL. eg.

©Copyright 2003