Cartesian-Join with the blank table

Posted on 2008-06-25
Last Modified: 2012-06-27
Sample Query:                                                                                    
SELECT     *                                                                                      
FROM       TEMP01,TEMP02,TEMP03,TEMP04,TEMP05,TEMP06,TEMP07,TEMP08,TEMP09,TEMP10,                
I have to DO the Cartesian-Join with all the 22 Tables, The problem is if any one of the table is
blank then the result set also empty,                                                            
How can i handle this?. In the table set any table/Combination can be blank.                      
Question by:midwestexp
LVL 74

Expert Comment

ID: 21869987
you would have to do an outer join to any table that might be missing data.

however,  are you sure you really need to do a 22-way join?

Author Comment

ID: 21870935
Yes i have to include all the 22 Tables, Please explain me How to Do that.
LVL 10

Expert Comment

ID: 21871412
is this a trick question?  I'm trying to think of a reason to ever do this.
do all of the tables have the same number of columns?
what is the ddl for temp01?

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 21871705
Each table have only one column,

create table temp01 (
    a varchar2(1)
LVL 27

Accepted Solution

sujith80 earned 500 total points
ID: 21872064
Cartesian join of 22 tables will be a bad idea, if they are big tables. Probably you can do something programmatically to achieve the actual goal behid this.
To answer the question you may use a query like this, extend it for the 22 tables.
select * from 
(select * from TEMP01 union all select null from dual where not exists(select 1 from TEMP01 )) , 
(select * from TEMP02 union all select null from dual where not exists(select 1 from TEMP02 )) , 
(select * from TEMP03 union all select null from dual where not exists(select 1 from TEMP03 )) 

Open in new window


Author Closing Comment

ID: 31470774
Thank You Very Much.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to trim oracle sql sentence in unix 17 69
Where Does Time Value Come From for Database Insert or Update 4 36
Email query results in HTML 6 37
oracle collections 2 27
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question