1 t1 cross join t2
with t1 as (
select
1 as id,
'zhangsan1' as name
union all
select
2 as id,
'zhangsan2' as name
union all
select
3 as id,
'zhangsan3' as name
union all
select
4 as id,
'zhangsan4' as name
),
t2 as (
select
1 as id,
11 as age
union all
select
2 as id,
22 as age
union all
select
3 as id,
33 as age
union all
select
5 as id,
55 as age
)
select
t1.id as t1_id,
t1.name,
t2.id as t2_id,
t2.age
from
t1
cross join t2
order by
t1.id,
t2.age;
2 cross join on报错
with t1 as (
select
1 as id,
'zhangsan1' as name
union all
select
2 as id,
'zhangsan2' as name
union all
select
3 as id,
'zhangsan3' as name
union all
select
4 as id,
'zhangsan4' as name
),
t2 as (
select
1 as id,
11 as age
union all
select
2 as id,
22 as age
union all
select
3 as id,
33 as age
union all
select
5 as id,
55 as age
)
select
t1.id as t1_id,
t1.name,
t2.id as t2_id,
t2.age
from
t1
cross join t2 on t1.id = t2.id --报错，Cannot specify condition (NATURAL keyword, or ON or USING clause) following CROSS JOIN
order by
t1.id,
t2.age;
3 t2 cross join t1
with t1 as (
select
1 as id,
'zhangsan1' as name
union all
select
2 as id,
'zhangsan2' as name
union all
select
3 as id,
'zhangsan3' as name
union all
select
4 as id,
'zhangsan4' as name
),
t2 as (
select
1 as id,
11 as age
union all
select
2 as id,
22 as age
union all
select
3 as id,
33 as age
union all
select
5 as id,
55 as age
)
select
t2.id as t2_id,
t2.age,
t1.id as t1_id,
t1.name
from
t2
cross join t1
order by
t1.id,
t2.age;
end
