Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 9.0.0 | 

VALUES

Description

The VALUES clause prints out the values of rows defined in the expression. In most cases, the VALUES clause is used for creating a constant table, however, the clause itself can be used. When one or more rows are specified in the VALUES clause, all rows should have the same number of the elements.

The VALUES clause can be used to express the UNION query, which consists of constant values in a simpler way. For example, the following query can be executed.

VALUES (1 AS col1, 'first' AS col2), (2, 'second'), (3, 'third'), (4, 'forth');

The above query prints out the following result.

SELECT 1 AS col1, 'first' AS col2

UNION ALL

SELECT 2, 'second'

UNION ALL

SELECT 3, 'third'

UNION ALL

SELECT 4, 'forth';

Syntax

VALUES (expression[, ...])[, ...]

  • expression: An expression enclosed within parentheses stands for one row in a table.
Example

The following example shows use of the VALUES clause with multiple rows in the INSERT statement.

INSERT INTO athlete (code, name, gender, nation_code, event)

    VALUES ('21111', 'Miran Jang', 'F', 'KOR', 'Weight-lifting'),

           ('21112', 'Yeonjae Son', 'F', 'KOR', 'Rhythmic gymnastics');

The following example shows how to use subquery in the FROM statement.

SELECT a.*

FROM athlete a, (VALUES ('Miran Jang', 'F'), ('Yeonjae Son', 'F')) AS t(name, gender)

WHERE a.name=t.name AND a.gender=t.gender;

 

         code  name                gender   nation_code        event

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

        21111  'Miran Jang'        'F'      'KOR'              'Weight-lifting'

        21112  'Yeonjae Son'       'F'      'KOR'              'Rhythmic gymnastics'