Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

FROM Clause

General
Description

The FROM clause specifies the table in which data is to be retrieved in the query. If no table is referenced, the FROM clause can be omitted. Retrieval paths are as follows:

  • Single table
  • Subquery
  • Derived table
Syntax

SELECT [ <qualifier> ] <select_expressions>

                          [ FROM <table_specification> [ {, <table specification>

| <join table specification> }... ]]

 

 

<select_expressions> ::= * | <expression_comma_list> | *, <expression_comma_list>

 

<table_specification> ::=

 <single_table_spec> [ <correlation> ] [ WITH (lock_hint) ] |

 <metaclass_specification> [ <correlation> ] |

 <subquery> <correlation> |

 TABLE ( <expression> ) <correlation>

 

<correlation> ::= [ AS ] <identifier> [ ( <identifier_comma_list> ) ]

 

<single_table_spec> ::= [ ONLY ] <table_name> |

                      ALL <table_name> [ EXCEPT <table_name> ]

 

<metaclass_specification> ::= CLASS <class_name>

 

lock_hint ::=

READ UNCOMMITTED

  • select_expressions : One or more columns or expressions to query is specified. Use * to query all columns in the table. You can also specify an alias for a column or an expression to be queried by using the AS keyword. This keyword can be used in GROUP BY, HAVING, ORDER BY and FOR clauses. The position index of the column is given according to the order in which the column was specified. The starting value is 1.
  • table_specification : At least one table name is specified after 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.
Example

--FROM clause can be omitted in the statement

SELECT 1+1 AS sum_value;

    sum_value

=============

            2

 

--db_root can be used as a dummy table

SELECT 1+1 AS sum_value FROM db_root;

    sum_value

=============

            2

 

SELECT CONCAT('CUBRID', '2008' , 'R3.0') AS db_version;

  db_version

======================

  'CUBRID2008R3.0'

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. A derived table created form a subquery can have any number of columns and records.

Syntax

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

  • The number of column_name and the number of columns created by the subquery must be identical.
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);

  sum(n)

========

      82

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));

  nation_code          host_year          gold

=========================================

  'JPN'                    2004                16

  'CHN'                    2004                32

  'DEN'                    1996                 4

  'ESP'                    1992                13