Generate rows
Here you are shown how to generate rows without the use of tables
You can use single value SELECT
to generate tables
You can use them when you need a small table for your query but you don't have permission to create tables in the database itself.
SELECT x centigrade, x*9/5+32 fahrenheit
FROM (SELECT 0 x UNION SELECT 10 UNION SELECT 20
UNION SELECT 30 UNION SELECT 40) t
SELECT x centigrade, x*9/5+32 fahrenheit
FROM ( SELECT 0 x FROM dual
UNION SELECT 10 FROM dual
UNION SELECT 20 FROM dual
UNION SELECT 30 FROM dual UNION SELECT 40 FROM dual) t
Hack 10 Converting subqueries into joins
Hack 11 Converting aggregate subqueries into joins
Hack 16 Search for a String across columns
Hack 24 Multiply Across a Result Set
Hack 25.5 Splitting and combining columns
Hack 26 Include the rows your JOIN forgot
Hack 30 Calculate the maximum/minimum of two fields
Hack 33 Get values and subtotals in one shot
Hack 50 Combine tables containing different data
Hack 51/52 Display rows as columns
Hack 55 Import Someone Else's Data
Hack 62 Issue Queries Without Using a Table
Hack 63 Generate rows without tables
Hack 72 Extract a subset of the results
Hack 78 Break it down by Range
Hack 88 Test two values from a subquery