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

You can do it in PostgreSQL:

=> select * from (values (1,7), (2,6), (3,13), (4,12), (5,9) ) x(id, count);
 id | count 
----+-------
  1 |     7
  2 |     6
  3 |    13
  4 |    12
  5 |     9

http://www.postgresql.org/docs/8.2/static/sql-values.html

Answer 3

In Microsoft T-SQL 2008 the format is:

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b)

I.e. as Jonathan mentioned above, but without the 'table' keyword.

See:

• FROM (T-SQL MSDN docs) (under derived-table), and
• Table Value Constructor (T-SQL MSDN docs)

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

Since MariaDB v10.3.3 and MySQL v8.0.19 you can now do exactly that!

See docs: MariaDB, MySQL

MariaDB:

WITH literaltable (id,count) AS (VALUES (1,7),(2,6),(3,13),(4,12),(5,9))
SELECT MAX(count) AS max,COUNT(*) AS count FROM literaltable

I used a WITH here because MariaDB doesn't supply nice column names for VALUES .... You can use it in a union without column names:

SELECT 1 AS id,7 AS count UNION ALL VALUES (2,6),(3,13),(4,12),(5,9) ORDER BY count DESC

And although the docs don't appear to mention it, you can even use it as a top-level query:

VALUES (1,7),(2,6),(3,13),(4,12),(5,9) ORDER BY 2 DESC

The actual column names are in fact the just first row of values, so you can even do this (though it's inelegant, and you can run into duplicate column name errors):

SELECT MAX(`7`) AS max,COUNT(*) AS count FROM (VALUES (1,7),(2,6),(3,13),(4,12),(5,9)) literaltable

---

MySQL:

I don't have an instance of MySQL v8.0.19 to test against right now, but according to the docs either of these should work:

SELECT MAX(column_1) AS max,COUNT(*) AS count FROM (VALUES ROW(1,7), ROW(2,6), ROW(3,13), ROW(4,12), ROW(5,9)) literaltable

SELECT MAX(data) AS max,COUNT(*) AS count FROM (VALUES ROW(1,7), ROW(2,6), ROW(3,13), ROW(4,12), ROW(5,9)) literaltable(id,data)

Unlike MariaDB, MySQL supplies automatic column names column_0, column_1, column_2, etc., and also supports renaming all of a subquery's columns when referencing it.

I'm not sure, but this dev worklog page seems to suggest that MySQL has also implemented the shorter sytax (omitting "ROW", like MariaDB), or that they will in the near future.

Answer 6

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 7

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.

Answer 8

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.