Problem solve Get help with specific problems with your technologies, process and projects.

Is this where we use a RIGHT OUTER JOIN?

SQL Expert Rudy Limeback explains a situation when its appropriate to use a RIGHT OUTER JOIN.

How do you perform an outer join on the results of 2 tables inner joined, without using a subselect?

Subselect example:

select a,b,c
from table t1
outer join
  (select t2.create_dt_tm, t3.page_cnt 
    from t2, t3
   where t2.id = t3.t2_id 
     and t2.create_dt_tm > sysdate -4)
   oj on (t1.id = t2.t1_id);

In SQL FAQ: Common SQL questions, part 2 (05 July 2007), towards the end, there is mention of "esoteric" questions which we suspect are university-level homework assignments. This question sounds suspiciously like one of those.

My first reaction would be to say, "You don't." Why would you feel it necessary to do this without using a subselect? Unless it was some kind of trick question.

By the way, there are some serious problems in your question. OUTER JOIN by itself is incorrect. The word OUTER is optional, but you must say whether it's LEFT or RIGHT or FULL. There are also typos in the table aliases. But the intent of your question was clear, assuming you meant a left outer join from t1 to the subselect.

Perhaps this is one of those rare examples of an appropriate use for a RIGHT OUTER JOIN.

select a,b,c
  from t3
  join t2
    on t2.id = t3.t2_id
   and t2.create_dt_tm > sysdate -4
right outer
  join t1
    on t1.id = t2.t1_id

Please do let me know how that works. RIGHT OUTER JOINs give me the creeps. Why didn't you want to use a subselect again?

Dig Deeper on Oracle development languages

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.