join of value lists in postgres-CodePudding

I need to join two value lists in postgres. I tried the following but it does not work;

select a.*,b.* from (values('a'),('b')) as a join (values('1'),('2'),('3')) as b ;

What should I write instead ; The result should be a table of 6 values.

CodePudding user response：

I assume you want to cross join values so that you will get 6 values.

select
    a.*,
    b.* 
  from (values('a'),('b')) as a (a),  
  (values('1'),('2'),('3')) as b (b);

a | b
--|--
a | 1
b | 1
a | 2
b | 2
a | 3
b | 3

Here is another statement with same result (with join clause)

select
    a.*,
    b.* 
  from (values('a'),('b')) as a (a)
  cross join (values('1'),('2'),('3')) as b (b);

CodePudding user response：

Strangely enough (from what I knew until now) it works only with the two forms bellow:

    select a.*,b.* from 
(values('a'),('b')) as a cross join (values('1'),('2'),('3')) as b ;
    
    select a.*,b.* from 
(values('a'),('b')) as a join (values('1'),('2'),('3')) as b on true;