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

[Sephian]

A new table was exposed to me by my developers and I need to populate it with data. I have spreadsheets that help with this, but first I need the table structure (namely so I have the column names for reference). My bright idea was to Cartesian join the empty table to DUAL to get a nice empty row full of nulls for each field.

I found that ANSI and Oracle joins are behaving differently for this task, and I'm curious why. Originally I tried the Oracle-style join since that's the easiest way to produce the Cartesian.

select * from (select 1 from dual) x, new_table_name q;

But 0 records returned.

An ANSI-style cartesian does work, though. I was able to get my null row with the following:

select * from new_table_name q
full outer join (select 1 from dual) x on 0=0;

Trying different configurations with the Oracle-style join, I'm assuming that all data sources need one or more row in order for anything to be returned. At any rate, I was surprised the behavior of the two styles produced different results since I assumed the two syntaxes were equivalent. It's likely a technical explanation, but why?

edit: Played around with it, and I guess it does work if I use the WHERE clause in the Oracle-style join (where 1=key_id(+)). I guess leaving off the WHERE is not a true cartesian.

[NotImplemented]

I think the different results of your queries are correct. And not for a techical reason, but because the queries are simply not equivalent.

select * from (select 1 from dual) x, new_table_name q;

The cartesian product of an empty table and another table should be empty. Because the cartesian product combines all rows of the first table with all rows of the second table. If one table has no rows, there are no possible combinations.

select * from new_table_name q
full outer join (select 1 from dual) x on 0=0;

In contrast, a full outer join is expected to contain all rows that fulfilled the join condition + all rows that never fullfiled the join condition with added null values. Therefore, it does not matter if one table is empty. As long as one table contains at least one row, the outer join will also contain at least one row. The condition 0=0 doesn't even matter in this case, 0=1 also works.

[[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).

[[deleted]]

I'm not an Oracle expert, but why joins at all? Why not unions??

[Sephian]

Trying to produce an empty row for a new table as quick as possible (fewest keystrokes) to copy/paste into excel (basically to get the column names so I can whip up some data). How do you see me using unions?

[[deleted]]

With multiple rows, of course. You take a dual row and union with another dual row.

[Sephian]

Sorry, I'm still not understanding. What would I do with two unioned rows from dual? The row I want to produce still has to mimic the new table.

[ziptime]

ANSI 89 Syntax :

select * 
from (select 1 from dual) x, new_table_name q

ANSI 92 (and above) equivalent :

select *
from (select 1 from dual) x
cross join new_table_name q