Can you define "literal" tables in SQL?
SqlMysqlSubqueryTemp TablesSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
I suppose you could do a subquery with several SELECT
s combined with UNION
s.
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 - Sql
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
Solution 3 - Sql
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 - Sql
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 - Sql
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;
Solution 6 - Sql
Since MariaDB v10.3.3 and MySQL v8.0.19 you can now do exactly that!
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.
Solution 7 - Sql
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.
Solution 8 - Sql
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.