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

Single-Row Subquery
Description

A single-row subquery outputs an 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 rows, 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 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
Description

The multiple-row subquery returns one or more rows 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 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 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.