MySQL - VALUES Statement

---

---

MySQL VALUES Statement

The VALUES statement of MySQL is used to return a set of rows as a table, in short you can construct a table using this statement.

You can create an individual row by passing the values (separated by commas) to the ROW() constructor.

Syntax

Following is the syntax of the VALUES statement −

VALUES row_constructor_list [ORDER BY column_designator] [LIMIT BY number]

Example

Following query creates a table using the VALUES clause −

VALUES ROW(12,22, 53), ROW(25, 97, 69), ROW(45 ,69 , 68);

Output

Following is the Output of the above query −

column_0	column_1	column_2
12	22	53
25	97	69
45	69	68

Using ORDER BY clause

If you create a table using the VALUES clause the name of the columns will always be as column_1, column2.... You can arrange the created rows in order based on the column name using the ORDER BY clause as shown below −

VALUES ROW(11, 2, 53), ROW(25, 97, 69), ROW(45, 80, 68) ORDER BY column_2;

Output

The above query produces the following output −

column_0	column_1	column_2
11	2	53
25	97	68
45	80	69

Using the LIMIT clause

You can also limit the number of records in the result using the LIMIT clause as −

VALUES ROW(11, 2, 53), ROW(25, 97, 69), ROW(45, 80, 68) LIMIT 2;

Output

The above mysql query will generate the output shown below −

column_0	column_1	column_2
11	2	53
25	97	69

Various types in one row

While creating tables using the VALUES clause you insert various types of values under a row −

VALUES Row ('Raju', 25, '25-09-2014'), Row (55, 447, 44), 
Row('test', 'sample', 'demo');

Output

Following is the output of the above query −

column_0	column_1	column_2
Raju	25	25-09-2014
55	447	44
test	sample	demo

Using the UNION clause

You can also use the UNION clause to join two or more rows as shown below −

VALUES Row ('Raju', 25, '25-09-2014') UNION VALUES 
Row (55, 447, 44) 
UNION VALUES Row('test', 'sample', 'demo');

Output

The above query generates the following output −

column_0	column_1	column_2
Raju	25	25-09-2014
55	447	44
test	sample	demo