Confused about number in SQL SELECT statement

I was reading a SQL "cookbook" type reference, and I came across a statement that I has never come across before:

INSERT INTO table (col1, col2)
SELECT t.col1, 8
FROM table AS t
WHERE t.c开发者_开发百科ol2 = 5
UNION ALL
SELECT 8, 8

Now what confuses me is a number (in this example, 8) immediately following the SELECT statement. In my limited SQL experience, I have only come across SELECT queries that are followed by column names. Could somebody help me understand what this does? Thanks!

---

The SELECT 8, 8 does exactly what it appears to do; it returns a result set with 2 un-named columns each containing 8 - if you run just that statement yourself you'll see exactly that.

You are free to select literal values & expressions just as you are columns, function results etc;

SELECT 'cake', 123 + 456

The UNION ALL merges (non-duplicate) rows from the 1st table select with the result of the 2nd select so your inserting a single additional (8,8) row into the table.

---

You can add a literal value to the select statement.

In this case it will just add an additional column to the result with the value 8 for all rows.

You can also use functions that don't reference columns similarly. e.g.

SELECT t.col1, NOW() AS t

The AS t defines a column alias for the extra column. This is not important in your example as it is just used as the source for an insert anyway.

I won't bother addressing the UNION ALL as that isn't the point of confusion to you.

---

It selects the literals 8 and 8. The statement will insert into table the values of col1 and 8 from table where col2 of table = 5 AND one more row with values 8 and 8

---

INSERT INTO table1 (col1, col2)

Insert into a table named table1 a row with values for col and col2.
Set all other columns to their default value (or null if there is none).

SELECT t.col1, 8
FROM table AS t
WHERE t.col2 = 5

Insert the output of this select statement.
Note that select 8 will substitute a literal 8 in every row of the output.

UNION ALL
SELECT 8, 8

Put an extra row with just col1=8, col2=8 in the result.

Links:
http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
http://dev.mysql.com/doc/refman/5.0/en/select.html

From the last link:

SELECT can also be used to retrieve rows computed without reference to any table.
For example:

mysql> SELECT 1 + 1;
       -> 2

---

You can SELECT anything you want, starting from table fields, ending at custom expressions that can be evaluated by SQL engine (for example table.field * 2). In this query you'll have 8 as fields of every returned record.

---

It is the relational operation known as 'extend' (interestingly, Codd initially omitted extend from his relational algebra, probably because he envisaged calculations being done in the 'front end').

Although typically used for calculation, extension involving a literal is allowed and, while not terribly useful, from a language design point of view there is no reason to disallow it.

---

You can put a value directly in a SELECT statement. t.col1 is actually a variable which will have different values in different returned registers. 8 is a literal value.