FROM Clause

• General
• Derived Table
• Subquery Derived Table

General

Description

The FROM clause specifies the table in which data is to be retrieved in the query. Retrieval paths are as follows:

• Single table

• Subquery

• Derived table

Syntax

FROM from_specification

from_specification :
table_spec [ {, table_spec | outerjoin_table_spec }...]

table_spec :
single_table_spec [ correlation ] [ WITH (lock_hint [ {, lock_hint } ] ) ]
( single _table_spec [ {, single_table_spec}...] )
CLASS table_name [ correlation ]
subquery correlation
TABLE (expression) correlation

outerjoin_table_spec :
{ LEFT | RIGHT } [OUTER ] JOIN table_spec ON search_condition

single_table_spec :
[ ONLY ] table_name
ALL table_name [ ( EXCEPT table_spec ) ]

correlation :
[ AS ] identifier

lock_hint :
READ UNCOMMITTED

• table_spec : To retrieve class attributes from a table, you must use the keyword CLASS followed by the table name in the FROM clause.
  Subqueries and derived tables can also be used in the FROM clause. For more information on subquery derived tables, see "Subquery Derived Table."
• lock_hint : You can set READ UNCOMMITTED  for the table isolation level. READ UNCOMMITTED is a level where dirty reads are allowed; see Transaction Isolation Level for more information on the CUBRID transaction isolation level.

Derived Table

In the query statement, subqueries can be used in the table specification of the FROM clause. Such subqueries create derived tables where subquery results are treated as tables. A correlation specification must be used when a subquery that creates a derived table is used.
Derived tables are also used to access the individual element of an attribute that has a set value. In this case, an element of the set value is created as an instance in the derived table.

Subquery Derived Table

Description

Each instance in the derived table is created from the result of the subquery in the FROM clause.

Syntax

FROM (subquery) [ AS ] derived_table_name [( column_name [ {, column_name }_ ] )]

A derived table created form a subquery can have any number of columns and instances.

However, the number of column_name and the number of columns created by the subquery must be identical.

Example

• Example 1
• The following is an example of retrieving the sum of the number of gold medals won by Korea and that of silver medals won by Japan. This example shows a way of getting an intermediate result of the subquery and processing it as a single result, by using a derived table. The query returns the sum of the gold values whose nation_code is 'KOR' and the silver values whose nation_code column is 'JPN'.

SELECT SUM(n) FROM (SELECT gold FROM participant WHERE nation_code='KOR'
UNION ALL SELECT silver FROM participant WHERE nation_code='JPN') AS t(n);
=== <Result of SELECT Command in Line 2> ===
  sum(n)
========
      82
1 rows selected.

• Example 2
• Subquery derived tables can be useful when combined with outer queries. For example, a derived table can be used in the FROM clause of the subquery used in the WHERE clause.
• The following is a query example that shows nation_code, host_year and gold fields of the instances whose number of gold medals is greater than average sum of the number of silver and bronze medals when one or more sliver or bronze medals were won. In this example, the query (the outer SELECT clause) and the subquery (the inner SELECT clause) share the nation_code attribute.

SELECT nation_code, host_year, gold
FROM participant p
WHERE gold > ( SELECT AVG(s)
            FROM ( SELECT silver + bronze
            FROM participant
            WHERE nation_code = p.nation_code
            AND silver > 0
            AND bronze > 0
          ) AS t(s));
=== <Result of SELECT Command in Line 1> ===

  nation_code          host_year          gold
=========================================
  'JPN'                        2004                16
  'CHN'                       2004                32
  'DEN'                       1996                  4
  'ESP'                       1992                 13    

4 rows selected.