Can you define "literal" tables in SQL?

Question

Is there any SQL subquery syntax that lets you define, literally, a temporary table?

For example, something like

SELECT
  MAX(count) AS max,
  COUNT(*) AS count
FROM
  (
    (1 AS id, 7 AS count),
    (2, 6),
    (3, 13),
    (4, 12),
    (5, 9)
  ) AS mytable
  INNER JOIN someothertable ON someothertable.id=mytable.id

This would save having to do two or three queries: creating temporary table, putting data in it, then using it in a join.

I am using MySQL but would be interested in other databases that could do something like that.

Answer 1

I suppose you could do a subquery with several SELECTs combined with UNIONs.

SELECT a, b, c, d
FROM (
    SELECT 1 AS a, 2 AS b, 3 AS c, 4 AS d
    UNION ALL 
    SELECT 5 , 6, 7, 8
) AS temp;

Answer 2

I found this link Temporary Tables With MySQL

CREATE TEMPORARY TABLE TempTable ( ID int, Name char(100) ) TYPE=HEAP; 

INSERT INTO TempTable VALUES( 1, "Foo bar" ); 

SELECT * FROM TempTable; 

DROP TABLE TempTable;

Answer 3

CREATE TEMPORARY TABLE ( ID int, Name char(100) ) SELECT ....

Read more at : http://dev.mysql.com/doc/refman/5.0/en/create-table.html

( near the bottom )

This has the advantage that if there is any problem populating the table ( data type mismatch ) the table is automatically dropped.

An early answer used a FROM SELECT clause. If possible use that because it saves the headache of cleaning up the table.

Disadvantage ( which may not matter ) with the FROM SELECT is how large is the data set created. A temporary table allows for indexing which may be critical. For the subsequent query. Seems counter-intuitive but even with a medium size data set ( ~1000 rows), it can be faster to have a index created for the query to operate on.

Answer 4

In standard SQL (SQL 2003 - see http://savage.net.au/SQL/) you can use:

INSERT INTO SomeTable(Id, Count) VALUES (1, 7), (2, 6), (3, 13), ...

With a bit more chasing, you can also use:

SELECT * FROM TABLE(VALUES (1,7), (2, 6), (3, 13), ...) AS SomeTable(Id, Count)

Whether these work in MySQL is a separate issue - but you can always ask to get it added, or add it yourself (that's the beauty of Open Source).

Answer 5

In a word, yes. Even better IMO if your SQL product supports common table expressions (CTEs) i.e. easier on the eye than using a subquery plus the same CTE can be used multiple times e.g. this to 'create' a sequence table of unique integers between 0 and 999 in SQL Server 2005 and above:

WITH Digits (nbr) AS 
(
 SELECT 0 AS nbr UNION ALL SELECT 1 UNION ALL SELECT 2 
 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 
 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 
 UNION ALL SELECT 9 
), 
Sequence (seq) AS
(
 SELECT Units.nbr + Tens.nbr + Hundreds.nbr 
   FROM Digits AS Units
        CROSS JOIN Digits AS Tens
        CROSS JOIN Digits AS Hundreds
)
SELECT S1.seq 
  FROM Sequence AS S1;

except you'd actually do something useful with the Sequence table e.g. parse the characters from a VARCHAR column in a base table.

HOWEVER, if you are using this table, which consists only of literal values, multiple time or in multiple queries then why not make it a base table in the first place? Every database I use has a Sequence table of integers (usually 100K rows) because it is so useful generally.