Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Retrieving Data From More Than One Table


The examples presented up to now in this chapter have illustrated the essential features of simple SELECT statements with data retrieval from single tables. However, much of the power of SQL lies in the ability to perform joins through a single statement, i.e. to select data from two or more tables, using the search condition to link the tables in a meaningful way.

The Join Condition

In retrieving data from more than one table, the search condition or join condition specifies the way the tables are to be linked. For example:

List the product name in addition to the EAN and price:
 SELECT product, ean_code, price
    FROM items JOIN products ON items.product_id = products.product_id;
 

The join condition here is ITEMS.PRODUCT_ID = PRODUCTS.PRODUCT_ID, which relates the product identifier in table ITEMS (where codes are listed) to the product identifier in table PRODUCTS (where names are listed).

Returns:

 PRODUCT
EAN_CODE
PRICE
 100 Anos
77774238724
9.98
 12 Golden Country Greats
75596190923
17.98
 12 Super Exitos
724385487521
9.98
 1492: Conquest of Paradise
75678243226
17.98
 ...
...
...

Conceptually, the join first establishes a table containing all combinations of the rows in PRODUCTS with the rows in ITEMS, then selects those rows in which the two PRODUCT_ID values are equal. See Conceptual Description of the Selection Process for a fuller description of the conceptual SELECT process.

This does not necessarily represent the order in which the operations are actually performed; the order of evaluation of a complex SELECT statement is determined by the SQL optimizer, regardless of the order in which the component clauses are written.

Cross Products

Without the join condition, the result is a cross product of the columns in the tables in question, containing all possible combinations of the selected columns, for example:

 SELECT product, ean_code, price
    FROM items, products;
Returns:

 PRODUCT
EAN_CODE
PRICE
 'Murder In The Cathedral'
77774238724
9.98
 'Murder In The Cathedral'
75596190923
17.98
 'Murder In The Cathedral'
724385487521
9.98
 'Murder In The Cathedral'
75678243226
17.98
 ...
...
...

It is easy to see that a carelessly formulated join query can produce a very large result table. Two tables of 100 rows each, for instance, give a cross product with 10,000 rows; three tables of 100 rows each give a cross product with 1,000,000 rows!

The risk of generating large (erroneous) result tables is particularly high in interactive SQL (e.g. when using Mimer BSQL), where queries are so easily written and submitted.

Simple Joins

In simple joins, all tables used in the join are listed in the FROM clause of the SELECT statement. This is in distinction to nested joins, where the search condition for one SELECT is expressed in terms of another SELECT, see Nested Selects.

Example
 SELECT product, ean_code, price
    FROM items, products WHERE items.product_id = products.product_id;

SELECT *

The form SELECT * may be used in a join query, but since this selects all columns in the result set, at least one column is often duplicated (a join condition column).

Example
 SELECT *
       FROM items
       JOIN products ON items.product_id = products.product_id;
Returns:

Columns from ITEMS:

 ITEM_ID
 PRODUCT_ID
 FORMAT_ID
 RELEASE_DATE
 STATUS
 PRICE
 STOCK
 REORDER_LEVEL
 EAN_CODE
 PRODUCER_ID
 IMAGE_ID
 

Columns from PRODUCTS:

 PRODUCT
 PRODUCT_ID
 PRODUCT_SEARCH

Columns in the join query that are uniquely identified by the column name may be specified by name alone. Columns that have the same name in the joined tables must be qualified by their respective table names.

The same query as above, but only three columns are returned:

 SELECT product, ean_code, price
    FROM items JOIN products ON items.product_id = products.product_id;

Nesting Join Clauses

It is possible to nest join-clauses, for example:

List the category in addition to the EAN and price for any items released in December 1996:
 SELECT ean_code, price, category
 FROM items JOIN formats ON items.format_id = formats.format_id
    JOIN categories ON categories.category_id = formats.category_id
 WHERE release_date BETWEEN date'1996-12-01' AND date'1996-12-30';
Result:

 EAN_CODE
PRICE
 CATEGORY
  9780006498957 
7.99
 Books
 724385487521
9.98
 Music
 731453076723
29.98
 Music
 53308951925
11.98
 Music

Complex Search Conditions and Joins

A join query can join any number of tables using complex search conditions to select the relevant information from each table.

List the product for any items with a release date in the future along with the item price in both Swedish and Danish crowns (SEK and DKK respectively):
 SELECT product,
        CAST(price * exchange_rate AS DECIMAL(12,2)) AS cost,
        currency
    FROM items, products, currencies
    WHERE release_date > CURRENT_DATE
    AND products.product_id = items.product_id
    AND currencies.code IN ('SEK', 'DKK')
    ORDER BY product, currency;
Result:

 PRODUCT
COST
 CURRENCY
 Greatest Hits
99.42
 Danish Kronor
 Greatest Hits
125.61
 Swedish Kronor
 Pieces Of Fish
113.64
 Danish Kronor
 Pieces Of Fish
143.58
 Swedish Kronor
 The Future Foretold
49.71
 Danish Kronor
 The Future Foretold
62.80
 Swedish Kronor
 The Sql Quiz Book
99.49
 Danish Kronor
 The Sql Quiz Book
125.70
 Swedish Kronor

In formulating a search condition for a join query, it can help to write out the columns that would appear in a complete cross-product of the tables. The search condition is then formulated as though the query was a simple SELECT from the cross-product table.

Outer Joins

The joins in the previous sections were all inner joins. In an inner join between two tables, only rows that fulfill the join condition are present in the result.

An outer join, on the contrary, contains non-matching rows as well. The outer join has two options, LEFT and RIGHT.

Left Outer Join

Example
 SELECT ean_code, release_date, producer
    FROM items
    LEFT OUTER JOIN producers
       ON items.producer_id = producers.producer_id
    WHERE ean_code >= 800000000000
    ORDER BY ean_code;
Result:

EAN_CODE
 RELEASE_DATE
 PRODUCER
800488327626
 1998-08-11
 Giants Of Jazz (Ita)
801061007720
 2000-10-31
 Warp Records
4988002364947
 1999-09-28   
 -
4988011353147
 1998-06-30
 -
5013145800423
 2000-03-14   
 Mint / Cherry Red
5013929112322
 1999-10-12   
 Cherry Red
5014438710221
 1994-12-27   
 Receiver Records
5019317001728
 1994-12-15   
 Receiver Records
7157761806273
 1996-01-18   
 Status Records
...
 ...
 ...

In the example above all rows from the table to the left in the join clause, i.e. ITEMS, are present in the result; non-matching rows from the PRODUCERS table are filled with null values in the result.

Observe the difference in result for the next statement and the previous one.

 SELECT ean_code, release_date, producer
    FROM items
    LEFT OUTER JOIN producers
       ON items.producer_id = producers.producer_id
       AND ean_code >= 800000000000
    ORDER BY ean_code;
Result:

EAN_CODE
 RELEASE_DATE
 PRODUCER
8811038120
 1991-08-27
 -
8811042127
 1991-10-22
 -
8811061326
 1992-05-19
 -
8811067021
 1992-12-22
 -
...
 ...
 ...
800488327626
 1998-08-11
 Giants Of Jazz (Ita)
801061007720
 2000-10-31
 Warp Records
4988002364947
 1999-09-28   
 -
4988011353147
 1998-06-30
 -
5013145800423
 2000-03-14   
 Mint / Cherry Red
...
 ...
 ...

The reason is that conditions in the where clause are applied to the result of the join-clause and not to the joined tables as is the case with the conditions in the on-clause.

Right Outer Join

A right outer join will take all records from the table to the right in the join-clause.

Nesting Outer Joins

As with inner joins, it is possible to nest join-clauses. Nested joins can be of different types, i.e. both inner and outer joins.

The result of nested outer joins can be somewhat unexpected though, as it is the result of the first join-clause that is the left table in the next join, not the right table in the first join-clause.

Example
 SELECT *
 FROM tableA LEFT JOIN tableB ON tableA.id = tableB.id
   LEFT JOIN tableC ON tableA.id = tableC.id
 

This query does first perform tableA LEFT JOIN tableB. The result is then used as left table when performing LEFT JOIN tableC.

To make this query clearer, parentheses can be added as:

 SELECT *
 FROM (tableA LEFT JOIN tableB ON tableA.id = tableB.id)
   LEFT JOIN tableC ON tableA.id = tableC.id

Nested Selects

A form of SELECT, called a subselect, can be used in the search condition of a SELECT statement to form a nested query.

The main SELECT statement is then referred to as the outer select.

For example:

Select the products that have a release date in the future.
 SELECT product
    FROM products
    WHERE product_id IN (SELECT product_id
                            FROM items
                            WHERE release_date > CURRENT_DATE);
Result:

 PRODUCT
 Greatest Hits
 Pieces Of Fish
 The Future Foretold
 The Sql Quiz Book

To see how this works, evaluate the subselect first:

 SELECT product_id
    FROM items
    WHERE release_date > CURRENT_DATE;
Result:

 PRODUCT_ID
 30206
 30618
 31082
 31083

Then use the result of the subselect in the search condition of the outer select:

 SELECT product
    FROM products
    WHERE product_id IN (30206, 30618, 31082, 31083);
Result:

 PRODUCT
 Greatest Hits
 Pieces Of Fish
 The Future Foretold
 The Sql Quiz Book

Using Subselects

A subselect can be used in a search condition wherever the result of the subselect can provide the correct form of the data for the search condition.

Thus a subselect used with `=' must give a single value as a result.

A subselect used with IN, ALL or ANY must give a set of single values, see Retrieval with ALL, ANY, SOME.

A subselect used with EXISTS may give any result, see Retrieving Data Using EXISTS and NOT EXISTS.

Examples:
 WHERE column = (subselect)
 WHERE column IN (subselect)
 WHERE column = ALL (subselect)
 WHERE column = ANY (subselect)
 WHERE EXISTS (subselect)
 

The UNION, EXCEPT and INTERSECT operators can be used to combine two or more subselects in more complex statements, see Union, Except and Intersect Queries.

Nested Queries

Many nested queries can equally well be written as simple joins. For example:

Select the products that have a release date in the future.
 SELECT product
    FROM products
    WHERE product_id IN (SELECT product_id
                            FROM items
                            WHERE release_date > CURRENT_DATE);
 

or alternatively

 SELECT DISTINCT product
    FROM products, items
    WHERE products.product_id = items.product_id
    AND items.release_date > CURRENT_DATE;
 

Both these queries give exactly the same result. In most cases, the choice of which form to use is a matter of personal preference. Choose the form which you can understand most easily; the clearest formulation is least likely to cause problems.

Subselects in Queries

Queries may contain any number of subselects, for example:

List the producers (manufacturers) which have items that are more expensive than any of the items produced by Sony.
 SELECT producer
    FROM producers
    WHERE producer_id IN
          (SELECT producer_id
              FROM items
              WHERE price >
                       (SELECT MAX(price)
                           FROM items
                           WHERE producer_id =
                                    (SELECT producer_id
                                        FROM producers
                                        WHERE producer = 'SONY')));
 

Note the balanced parentheses for the nested levels.

It is particularly important at this level of complication to think carefully through the query to make sure that it is correctly formulated.

Often, writing some of the levels as simple joins can simplify the structure. The previous example may also be written:

 SELECT DISTINCT producer
    FROM producers, items
    WHERE producers.producer_id = items.producer_id
    AND price > (SELECT MAX(price)
                    FROM items, producers
                    WHERE items.producer_id = producers.producer_id
                    AND producer = 'SONY');

Correlation Names

A correlation name is a temporary name given to a table to represent a logical copy of the table within a query.

There are three uses for correlation names:

Simplifying Complex Queries Using Correlation Names

Using short correlation names into complicated queries can make the query easier to write and understand, particularly when qualified table names are used:

 SELECT mimer_store_music.artists.artist,
        mimer_store.product_details.*
    FROM mimer_store.product_details,
         mimer_store_music.titles,
         mimer_store_music.artists
    WHERE mimer_store_music.titles.item_id =
          mimer_store.product_details.item_id
    AND mimer_store_music.artists.artist_id =
          mimer_store_music.titles.artist_id
    ORDER BY mimer_store_music.artists.artist;
 

may be rewritten

 SELECT art.artist, pdt.*
    FROM mimer_store.product_details AS pdt,
         mimer_store_music.titles AS ttl,
         mimer_store_music.artists AS art
    WHERE ttl.item_id = pdt.item_id
    AND art.artist_id = ttl.artist_id
    ORDER BY art.artist;
 

The keyword AS in the FROM clause may be omitted, but is recommended for clarity.

About Correlation Names

Correlation names are local to the query in which they are defined.

When a correlation name is introduced for a table name, all references to the table in the same query must use the correlation name.

The following expression is not accepted:

    ...
    FROM mimer_store.product_details AS pdt,
         mimer_store_music.titles AS ttl,
    ...
    WHERE ttl.item_id = mimer_store.product_details.item_id

Joining a Table with Itself Using a Correlation Name

Joining a table with itself allows you to compare information in a table with other information in the same table. This can be done with a correlation name.

Select all currencies with the same exchange rate:
 SELECT currencies.currency, currencies.code, currencies.exchange_rate
    FROM currencies, currencies AS copy
    WHERE currencies.exchange_rate = copy.exchange_rate
    AND currencies.currency <> copy.currency;
Result:

 CURRENCY
 CODE
EXCHANGE_RATE
 Croatian Kuna
 HRK
7.0820
 Gourdes
 HTQ
7.0820
 Iraqi Dina
 IQD
1551.0000
 Uganda Shillings
 UGX
1551.0000

Here, the table CURRENCIES is joined to a logical copy of itself called COPY.

The first search condition finds pairs of currencies with the same exchange rate, and the second eliminates 'pairs' with the same currency name. Without the second condition in the search criteria, all currencies would be selected!

Without correlation names, this kind of query cannot be formulated. The following query would select all the currencies from the table:

 SELECT currency, code, exchange_rate
    FROM currencies
    WHERE currencies.exchange_rate = currencies.exchange_rate;

Outer References in Subselects Using Correlation Names

In some constructions using subselects, a subselect at a lower level may refer to a value in a table addressed at a higher level. This kind of reference is called an outer reference.

 SELECT currency
    FROM currencies
    WHERE EXISTS (SELECT *
                     FROM countries
                     WHERE currency_code = currencies.code);
 

This kind of query processes the subselect for every row in the outer select, and the outer reference represents the value in the current outer select row. In descriptive terms, the query says 'For each row in CURRENCIES, select the CURRENCY column if there are rows in COUNTRIES containing the current CODE value'.

If the qualifying name in an outer reference is not unambiguous in the context of the subselect, a correlation name must be defined in the outer select.

A correlation name may always be used for clarity, as in the following example:

 SELECT currency
    FROM currencies AS c
    WHERE EXISTS (SELECT *
                     FROM countries
                     WHERE currency_code = c.code);

Retrieving Data Using EXISTS and NOT EXISTS

EXISTS is used to check for the existence of some row or rows which satisfy a specified condition. EXISTS differs from the other operators in that it does not compare specific values; instead, it tests whether a set of values is empty or not. The set of values is specified as a subselect.

The subselect following the EXISTS clause most often uses of `SELECT *' as opposed to `SELECT column-list' since EXISTS only searches to see if the set of values addressed by the subselect is empty or not - a specified column is seldom relevant in the subquery.

EXISTS (subselect) is true if the result set of the subselect is not empty

NOT EXISTS (subselect) is true if the result set of the subselect is empty

SELECT statements with EXISTS almost always include an outer reference linking the subselect to the outer select.

Examples of EXISTS

Find all currencies that are used in the COUNTRIES table:
 SELECT currency
    FROM currencies AS c
    WHERE EXISTS (SELECT *
                     FROM countries
                     WHERE currency_code = c.code);
 

Without the outer reference, the select becomes a conditional `all-or-nothing' statement: perform the outer select if the subselect result is not empty, otherwise select nothing.

List all products where the producer (manufacturer) is not known:
 SELECT product
    FROM products
    WHERE EXISTS (SELECT *
                     FROM items
                     WHERE producer_id IS NULL
                     AND product_id = products.product_id);

Examples of NOT EXISTS

The next example illustrates NOT EXISTS:

List all products where the producer (manufacturer) is not known:
 SELECT product
 FROM products
 WHERE NOT EXISTS (SELECT *
                   FROM items
                   JOIN producers ON items.producer_id = producers.producer_id
                   WHERE product_id = products.product_id);
Result:

 PRODUCT
 Invictus
 Middle Of Nowhere

Negated EXISTS

Negated EXISTS clauses must be handled with care. There are two semantic `opposites' to EXISTS, with very different meanings:

 WHERE EXISTS (SELECT *
                  FROM artists
                  WHERE artist = 'Enigma')
 

is true if at least one artist is called Enigma.

 WHERE NOT EXISTS (SELECT *
                      FROM artists
                      WHERE artist = 'Enigma')

is true if no artist is called Enigma.

But

 WHERE EXISTS (SELECT *
                  FROM artists
                  WHERE artist <> 'Enigma')
 

is true if at least one artist is not called Enigma.

 WHERE NOT EXISTS (SELECT *
                      FROM artists
                      WHERE artist <> 'Enigma')
 

is true if no artist is not called Enigma, that is if every artist is called Enigma.

Retrieval with ALL, ANY, SOME

Subselects that return a set of values may be used in the quantified predicates ALL, ANY or SOME. Thus

 WHERE PRICE < ALL (subselect)
 

selects rows where the price is less than every value returned by the subselect

 WHERE PRICE < ANY (subselect)
 

selects rows where the price is less than at least one of the values returned by the subselect

Select countries that have an exchange rate of less than one:
 SELECT country
    FROM countries
    WHERE currency_code <> ALL (SELECT code
                                   FROM currencies
                                   WHERE exchange_rate >= 1.0);
 

If the result of the subselect is an empty set, ALL evaluates to true, while ANY or SOME evaluates to false.

An alternative to using ALL, ANY or SOME in a value comparison against a general sub-select, is to use EXISTS or NOT EXISTS to see if values are returned by a sub-select which only selects for specific values. For example:

Select countries where the associated currency code contains the letter 'E' as the middle character in the code:
 SELECT country
    FROM countries
    WHERE currency_code = ANY (SELECT code
                                  FROM currencies
                                  WHERE code LIKE '_E_');

is equivalent to:

 SELECT country
    FROM countries AS c
    WHERE EXISTS (SELECT *
                    FROM currencies
                    WHERE code LIKE '_E_'
                    AND code = c.currency_code);

Union, Except and Intersect Queries

The UNION, EXCEPT and INTERSECT operators combine the results of two select clauses.

UNION first merges the result tables specified by the separate selects and then eliminates duplicate rows from the merged set. (UNION ALL does not eliminate duplicate rows.)

EXCEPT takes the distinct rows from the first select and returns the rows that do not appear in the second select. (EXCEPT ALL does not eliminate duplicate rows.)

INTERSECT takes the results of two selects and returns only rows that appear in both selects, after removing duplicate rows from the final result set. (INTERSECT ALL does not eliminate duplicate rows.)

Columns which are merged by UNION, EXCEPT and INTERSECT must have compatible data types (numerical with numerical, character with character, etc).

Subselects addressing more than one result column are merged column by column in the order of selection. The number of columns addressed in each subselect must be the same.

The column names in the result of a UNION, EXCEPT or INTERSECT are taken from the names in the first subselect. Use labels in the first subselect to assign different column names to the result table.

In UNION, EXCEPT and INTERSECT queries, you may need to add an empty column so that columns not represented in both queries in the statement are retained in the result set. This is done by casting a NULL value to a matching datatype.

Example
 SELECT ean_code, release_date, producer
 FROM items INNER JOIN producers
     ON items.producer_id = producers.producer_id
 UNION ALL
 SELECT ean_code, release_date, CAST(NULL AS char)
 FROM items
 WHERE NOT EXISTS
     (SELECT * FROM producers
      WHERE items.producer_id = producers.producer_id)

UNION Examples

Select the different codes for currencies and countries that start with the letter 'D':
 SELECT code
    FROM currencies
    WHERE code LIKE 'D%'
 UNION
 SELECT currency_code
    FROM countries
    WHERE country LIKE 'D%';
 

The result is obtained by merging the results of the two selects and eliminating duplicates:

 SELECT code                   SELECT currency_code
    FROM currencies               FROM currencies
    WHERE code LIKE 'D%;          WHERE country LIKE 'D%';
 

CODE
CURRENCY_CODE
DJF
DJF
DKK
DKK
DOP
XCD
DZD
DOP

and the UNION gives the result table:

 CODE
 DJF
 DKK
 DOP
 DZD
 XCD

To retain duplicates in the result table, use UNION ALL in place of UNION, see the Mimer SQL Reference Manual, UNION or UNION ALL, for details.

Merge the codes and names of currencies where the code begins with 'D' with the codes and names of the countries where the country begins with 'D':
 SELECT code, currency AS currency_or_country
    FROM currencies
    WHERE code LIKE 'D%'
 UNION
 SELECT currency_code, country
    FROM countries
    WHERE country LIKE 'D%'
 ORDER BY code;
Result:

CODE
 CURRENCY_OR_COUNTRY
DJF
 Djibouti
DJF
 Djibouti Francs
DKK
 Danish Kronor
DKK
 Denmark
DOP
 Dominican Pesos
DOP
 Dominican Republic
DZD
 Algerian Dinars
XCD
 Dominica

Find the lowest and highest exchange_rates:

Unions can be used to combine information from the same table.

 SELECT 'Highest', MAX(exchange_rate) AS rate
    FROM currencies
 UNION ALL
 SELECT 'Lowest', MIN(exchange_rate)
    FROM currencies
 ORDER BY rate;
Result:

 
 RATE
 Lowest
 0.2644
 Highest
 1035000.0000

List the EAN, price and producer for each item, also add each item where the producer is not known:

Unions can be used to perform OUTER JOIN's, joining information in a table or tables with information not listed in those tables (i.e. information that is null).

 SELECT ean_code, release_date, producer
    FROM items JOIN producers ON items.producer_id = producers.producer_id
 UNION ALL
 SELECT ean_code, release_date, '*** undefined ***'
    FROM items
    WHERE NOT EXISTS (SELECT *
                       FROM producers
                       WHERE producer_id = items.producer_id)
 ORDER BY ean_code;
Result:

 EAN_CODE
 RELEASE_DATE
 PRODUCER
 800488327626
 1998-08-11
 Giants Of Jazz (Ita)
   801061007720
 2000-10-31
 Warp Records
 4988002364947
 1999-09-28   
 *** undefined ***
 4988011353147
 1998-06-30
 *** undefined ***
 5013145800423
 2000-03-14   
 Mint / Cherry Red
 5013929112322
 1999-10-12   
 Cherry Red
 5014438710221
 1994-12-27   
 Receiver Records
 5019317001728
 1994-12-15   
 Receiver Records
 7157761806273
 1996-01-18   
 Status Records
 ...
 ...
 ...

EXCEPT Examples

Select the codes from currencies, except those that also are found in countries, starting with the letter 'D':
 SELECT code
    FROM currencies
    WHERE code LIKE 'D%'
 EXCEPT
 SELECT currency_code
    FROM countries
    WHERE country LIKE 'D%';
 

The result is obtained by taking the first result and then remove the rows also found in the second select, and finally eliminating duplicates:

 SELECT code                   SELECT currency_code
    FROM currencies               FROM currencies
    WHERE code LIKE 'D%;          WHERE country LIKE 'D%';
 

CODE
CURRENCY_CODE
DJF
DJF
DKK
DKK
DOP
XCD
DZD
DOP

and the EXCEPT gives the result table:

 CODE
 DZD

To retain duplicates in the result table, use EXCEPT ALL in place of EXCEPT, see the Mimer SQL Reference Manual, EXCEPT or EXCEPT ALL, for details.

INTERSECT Examples

Select the codes from currencies and countries that exist in both tables, starting with the letter 'D':
 SELECT code
    FROM currencies
    WHERE code LIKE 'D%'
 INTERSECT
 SELECT currency_code
    FROM countries
    WHERE country LIKE 'D%';
 

The result is obtained by taking the first result and then remove the rows also found in the second select, and finally eliminating duplicates:

 SELECT code                   SELECT currency_code
    FROM currencies               FROM currencies
    WHERE code LIKE 'D%;          WHERE country LIKE 'D%';
 

CODE
CURRENCY_CODE
DJF
DJF
DKK
DKK
DOP
XCD
DZD
DOP

and the INTERSECT gives the result table:

 CODE
 DFJ
 DKK
 DOP

To retain duplicates in the result table, use INTERSECT ALL in place of INTERSECT, see the Mimer SQL Reference Manual, INTERSECT or INTERSECT ALL, for details.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX