How to select multiple rows filled with constants?

Let's expand: More than just UNION ALL

While UNION ALL works well for a short list of constant rows, different techniques are useful for more complex situations. Here's a brief overview:

1. PostgreSQL and its love for VALUES:

   SELECT * FROM (VALUES ('A', 'B'), ('C', 'D')) AS t(Name, Value);  -- Why write four lines when two will do?
   

2. Oracle's CONNECT BY, a series' best friend:

   SELECT 'Constant' FROM dual CONNECT BY LEVEL <= 5;   -- We are livin' la vida loca, five times!
   

3. SQL Server and PostgreSQL boast recursive CTEs:

   WITH RECURSIVE series AS (
     SELECT 1 AS n           -- The one where it all starts
     UNION ALL
     SELECT n + 1 FROM series WHERE n < 5   -- The one with the recursion
   )
   SELECT 'Constant' FROM series;   -- The one with constants. SQL: The Final Frontier. These are the voyages of the starship 'Constant'
   

4. Oracle's sys.odcivarchar2list in PL/SQL lists, or Table Type instantiations for bulk operations.

SQL Syntax Swirl: Compatibility and Efficiency

Interpret SQL syntax differences

Each database engine has unique nuances. While PostgreSQL's VALUES clause efficiently produces a set of constant rows, you'll need to fall back on UNION ALL in MySQL or SQL Server.

Embrace WITH for complex needs

In cases where you need to work with dependent constants or require recursion, Common Table Expressions (CTE) via WITH clause is your answer. By defining temporary result sets that can be referenced more than once within a query, CTEs simplify complex SQL operations with constants.

Adapt to different databases

Always comment your SQL code specifying its intended database system. With all its diversity, SQL solutions might need adaptation to align with the feature set and syntax of the target system.

When dealing with larger datasets

Generate series for bulk constants

Some DBMS offer functions to generate series of numbers. Both Oracle's CONNECT BY and SQL Server's generate_series can create recordsets without explicit constants, super useful when you need to generate larger datasets with constant values.

Using CTE and recursive methods

For more complex requirements, consider the possibility of recursive WITH queries in SQL Server and PostgreSQL. They are your best mates when you need to generate multiple rows of constant values in a scalable way.

SQL Fiddle: Practice makes perfect

For those keen on learning or wanting to experiment with different query methods, SQL Fiddle is a great sandbox tool. You can trial and debug your SQL statements against any database backend, no need to worry about setting up a local database instance.