Cross-product between table column and input values

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?

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.

-
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);

-
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

StackExchange Database Administrators Q#129844, answer score: 23