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;