Q

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
inner
  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?

This was first published in February 2008

Dig deeper on Oracle development languages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close