Q

LEFT OUTER JOIN without using LEFT OUTER JOIN

Can I get the same result as LEFT OUTER JOIN without using LEFT OUTER JOIN?

I would like to ask whether is it possible that I can get the same result as LEFT OUTER JOIN without using LEFT OUTER JOIN? Thanks.

We've been getting a number of this type of question lately: short, and simple on the surface. Short questions...

are wonderful, because they leave more room for the answer. Simple questions are even better, as long as they aren't too simple.

In this case, the question is not too simple, but I have difficulty imagining the circumstances which would lead to this requirement.

Here's the LEFT OUTER JOIN way to do it:

select A.foo
     , A.bar
     , B.qux
  from table1 as A
left outer
  join table2 as B
    on B.Aid = A.id

As you know, a LEFT OUTER JOIN returns all rows from the left table, with matching rows from the right table, if any. This is the same as an INNER JOIN combined in a UNION with a query on the left table only, with a NOT EXISTS subquery:

select A.foo
     , A.bar
     , B.qux
  from table1 as A
inner
  join table2 as B
    on B.Aid = A.id
union all
select A.foo
     , A.bar
     , null
  from table1 as A
 where not exists
       ( select *
           from table2 
          where Aid = A.id )

As mentioned, it's pretty hard to imagine why you wouldn't want to use the LEFT OUTER JOIN.

This was last published in April 2007

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close