Cross-product between table column and input values

Question

I seem to be unable to write an SQL query, which computes the cross-product between a table column and a set of given input values.

Something along the lines of:

WITH {1,2} as Input
Select *
From mTable.column, Input

With mTable.column containing the values 3 and 4, it should return:

1,3
1,4
2,3
2,4

Is there any way to achieve this?

Answer 1

In other RDBMS (like SQL Server before 2008 - as per Paul's comment) one might cross join to a subquery with UNION ALL SELECT, but there are more convenient and efficient options in Postgres.

And you don't need a CTE for this. You can use it, but it has no performance benefit.

1. Provide a set with VALUES:

   VALUES computes a row value or set of row values specified by value expressions. It is most commonly used to generate a "constant table" within a larger command, but it can be used on its own.

   SELECT t.i, m.col1
   FROM   mTable m
   CROSS  JOIN (VALUES (1), (2)) t(i);
   

2. Provide an array and unnest()

   2a. with an array constructor:

   SELECT i, m.col1
   FROM   mTable m
   CROSS  JOIN unnest (ARRAY[1,2]) i;
   

   2b. With an array literal:

   SELECT i, m.col1
   FROM   mTable m
   CROSS  JOIN unnest ('{1,2}'::int[]) i;
   

Add ORDER BY i, m.col1 if you need the sort order in your result.

About row and array syntax:

• Array of strings when updating a field

Answer 2

What you're describing is a CROSS JOIN.

SELECT input.n, mTable.column
FROM mTable, (
    SELECT 1 AS n
    UNION ALL
    SELECT 2 AS n) AS input;

.. or, in more modern SQL notation,

SELECT input.n, mTable.column
FROM mTable
CROSS JOIN (
    SELECT 1 AS n
    UNION ALL
    SELECT 2 AS n) AS input;

It's essentially a join without join conditions and it displays the cartesian product of mTable and input, similar to INNER JOIN ... ON 1=1.

You may also be able to use the LATERAL construct for potentially better performance, but PostgreSQL isn't my strong card.