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:
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
--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'
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.
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.
FROM (subquery) [ AS ] derived_table_name [( column_name [ {, column_name }_ ] )]
The following example shows how to retrieve 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
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 example 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