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 | 

Subquery

A subquery can be used wherever expressions such as SELECT or WHERE clause can be used. If the subquery is represented as an expression, it must return a single column; otherwise it can return multiple instances. Subqueries can be divided into single-row subqueries and multiple-row subqueries depending on how they are used.

Single-row Subquery
Description

A single-row subquery outputs an instance that has a single column. If no instance is returned by the subquery, the subquery expression has a NULL value. If the subquery is supposed to return more than one instances, an error occurs.

Example

The following is an example of retrieving the history table as well as the host country where a new world record has been set. This example shows a single-row subquery used as an expression. In this example, the subquery returns host_nation values for the instances whose values of the host_year column in the olympic table are the same as those of the host_year column in the history table. If there are no values that meet the condition, the result of the subquery is NULL.

SELECT h.host_year, (SELECT host_nation FROM olympic o WHERE o.host_year=h.host_year),
h.event_code, h.score, h.unit from history h;    
=== <Result of SELECT Command in Line 1> ===
    host_year (SELECT host_nation FROM olympic o WHERE o.host_year=h.host_year)   event_code  score  unit
==============================================================================================================
         2004  'Greece'                                                            20283  '07:53.0' 'time'
         2004  'Greece'                                                            20283  '07:53.0' 'time'
         2004  'Greece'                                                            20281  '03:57.0' 'time'
         2004  'Greece'                                                            20281  '03:57.0' 'time'
         2004  'Greece'                                                            20281  '03:57.0' 'time'
         2004  'Greece'                                                            20281  '03:57.0' 'time'
         2004  'Greece'                                                            20326  '210'     'kg'
         2000  'Australia'                                                         20328  '225'     'kg'
         2004  'Greece'                                                            20331  '237.5'   'kg'
...
147 rows selected.

Multiple-Row Subquery
Description

A multiple-row subquery returns one or more instances that contain the specified column. The result of the mutiple-row subquery can be used to create a set, a multiset or a list/sequence set using an appropriate keyword (SET, MULTISET, LIST or SEQUENCE).

Example

The following is an example of retrieving countries and their capital cities from the nation table, and returning lists of host countries and host cities of the Olympic Games. In this example, the subquery result is used to create a list from the values of the host_city column in the olympic table. This query returns name and capital values for nation instances, as well as a set that contains host_city values of the olympic instances with host_nation values. If the name value is an empty set in the query result, it is excluded. If there is no olympic instance that has the same value as the name, an empty set is returned.

SELECT name, capital, list(SELECT host_city FROM olympic WHERE host_nation = name) FROM nation;

=== <Result of SELECT Command in Line 1> ===
  name                  capital               sequence((SELECT host_city FROM olympic WHERE host_nation=name))
==================================================================
  'Somalia'             'Mogadishu'           {}
  'Sri Lanka'           'Sri Jayewardenepura Kotte'  {}
  'Sao Tome & Principe'  'Sao Tome'            {}
...
  'U.S.S.R.'            'Moscow'              {'Moscow'}
  'Uruguay'             'Montevideo'          {}
  'United States of America'  'Washington.D.C'      {'Atlanta ', 'St. Louis', 'Los Angeles', 'Los Angeles'}
  'Uzbekistan'          'Tashkent'            {}
  'Vanuatu'             'Port Vila'           {}
215 rows selected.

Such multiple-row subquery expressions can be used anywhere a set value expression is allowed. However, they cannot be used where a set constant value is required as in the DEFAULT specification in the class attribute definition.

If the ORDER BY clause is not used explicitly in the subquery, the order of the multiple-row query result is not set. Therefore, the order of the multiple-row subquery result that creates a sequence set must be specified by using the ORDER BY clause.