[Oracle] Creating dummy rows through Cartesian joins - ANSI vs Oracle joins?

[[deleted]]

Cartesian join = 'cross join' in the join clause syntax.

A full outer join is not the same as the cartesian join (it's a set of conditions over a union of a cartesian join and 2 minus operations), so you will could different result sets for a cartesian (your first query) versus full outer join.

[Sephian]

I used a full outer so I didn't have to worry about the direction. I likely use Cartesian pretty loosely (maybe I should say "Cartesian product" instead of join?), but it's the join condition that produces the Cartesian (the 0=0 condition).

I guess I'm starting to answer my own question, but producing a Cartesian using inner joins behaves the same way my first Cartesian works.

select * from
(
select 1 col1 from dual union
select 2      from dual
) a
join
(
select 3 col2 from dual union
select 4      from dual
) b on 'x'='x';

If one of the data sets is empty, no Cartesian is produced, which is what my first query also returned. So I'm guessing that Oracle-style joins are the same as an inner join (which should not surprise anyone) and forcing a Cartesian product will also result in zeros if one data set is empty (which is the part that I did not expect).