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