How to select several hardcoded SQL rows?

Alternative methods to fetch multiple rows

Creating rows with the VALUES keyword

An alternative way to select hardcoded rows is to leverage the VALUES keyword inside a FROM clause:

SELECT * FROM (VALUES
-- Making SQL great again with VALUES!
(1, 'A'),
-- Attribute of the first Avenger
(2, 'B'),
-- Batman's secret SQL code
(3, 'C')
-- Even SQL developers have a soft spot for cats
) AS x (id, value);


Inserting values into a temporary table

Sometimes it's more convenient to insert the hardcoded data into a temporary table before executing a SELECT statement:

CREATE TABLE #TempRows (id INT, value CHAR(1));

-- Creating the table. Unlike Thor's hammer, anyone can pick up this one
INSERT INTO #TempRows (id, value)
VALUES (1, 'A'), (2, 'B'), (3, 'C');

-- Inserting values. We’re basically shoving 'A', 'B', and 'C' down SQL’s throat
SELECT id, value FROM #TempRows;


Oracle SQL and DUAL table

With Oracle SQL, it's common practice to use the DUAL table for selecting multiple hardcoded rows. Here's how to do it:

SELECT 1 AS id, 'A' AS value FROM DUAL
-- Even Oracle likes it simple sometimes
UNION ALL
SELECT 2, 'B' FROM DUAL
-- Oracle cooler than a 'B' in the freezer
UNION ALL
SELECT 3, 'C' FROM DUAL;
-- And here's 'C'


Guidelines for managing UNION ALL

Consistency and compatibility

When using UNION ALL, it's crucial to maintain consistent columns and compatible data types among all selects. That's how you ensure your queries return a valid result set.

Performance considerations

Be mindful of your query performance especially for large hardcoded datasets. Both VALUES and UNION methodologies have their own performance implications and hence should be considered judiciously.

Tips to avert problems

Column consistency and data type compatibility

Ensure all SELECT statements have matching column numbers and data types in a UNION ALL. Otherwise, you might run into errors.

Performance for large datasets

Considering the performance implications for large hardcoded data sets is a good practice. The usage of VALUES or UNION ALL might affect the performance and hence should be used judiciously.