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 | 


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 rows. Subqueries can be divided into single-row subquery and multiple-row subquery depending on how they are used.

Single-Row Subquery

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


The following example shows how to retrieve 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 rows 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;    

    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'


Multiple-Row Subquery

The multiple-row subquery returns one or more rows that contain the specified column. The result of the mutiple-row subquery can create SET, MULTISET, and LIST (=SEQUENCE) by using an appropriate keyword.


The following example shows how to retrieve nations, capitals and host cities for Olympic Game all together in the nation table. 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 value for nation table, as well as a set that contains host_city values of the olympic table with host_nation value. If the name value is an empty set in the query result, it is excluded. If there is no olympic table 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;

  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'           {}

Such multiple-row subquery expressions can be used anywhere a collection-type value expression is allowed. However, they cannot be used where a collection-type 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 LIST (=SEQUENCE) must be specified by using the ORDER BY clause.