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.

Solution 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;

Solution 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

Solution 3

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).

Solution 4

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)

Solution 5

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;

View more solutions

Comments

[thomasrutter]

Wow, that's a creative solution! Sounds like it would at least work.

[thomasrutter]

Thanks for the answer! Unfortunately MySQL (5.0) doesn't like FROM TABLE(VALUES... but at least I know about it now

[thomasrutter]

This answer is probably more informative than the accepted answer, it just didn't solve my personal problem as well because I was using MySQL. I wish I could accept multiple answers.

[Michael]

conversely, the ugly of Open Source is, asking will get ignored or rejected unless a lot of people and/or a developer with free time is interested, and the learning curve for DIY is often time prohibitive. (not to say the alternative is in any way better...)

[Zarepheth]

Remove the keyword TABLE and this will work with MS SQL Server 2008 and probably above.

[Dan Lenski]

PostgreSQL FTW.

[jab]

Works in Spark SQL too.

[Pysis]

Just wondering if this was the only answer for MySQL. In the same situation as some other commenters, and wondering if there was a different construct to use besides temporary tables, like a sort fo select query listing the data inline, but I guess that is still also just data in memory the same as this example.

[Stijn de Witt]

Question is about MySQL

[lmsurprenant]

At first I thought the x was some kind of special PostgreSQL thing until I realized its just the same as as x (id, count)