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 a VALUES expression

The manual:

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 *
FROM  (VALUES (1), (2)) i(i)
CROSS  JOIN tbl t;

2. Provide an array and unnest()

... with an array constructor:

SELECT *
FROM   unnest(ARRAY[1,2]) i
CROSS  JOIN tbl t;

... with an array literal:

SELECT *
FROM   unnest('{1,2}'::int[]) i
CROSS  JOIN tbl t;

Add ORDER BY i, t.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.