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

Changing a subquery into a JOIN

How can I select (display) the columns that are in the subselect of a query? For example,

select col1, col2
from table1
where exists (select ColA
              from table2
       where table1.key1 = table2.key1)

I want to have ColA also in the outer select.


In many cases you can re-write the subquery into a join. Unfortunately this does not always give you the desired results, especially, as in this case, where it is an EXISTS subquery.

Your query written as a join would be:

select distinct 
       table1.col1
     , table1.col2
     , table2.colA
  from table1
inner
  join table2
    on table1.key1 = table2.key1

The DISTINCT is necessary because of the possibility of a one-to-many relationship between table1 and table2. Note that all existing colA values are selected, but because of the DISTINCT, multiple colA values are collapsed into one value for each combination of values of col1 and col2. In other words, you may get more than one row for each combination of values of col1 and col2. In your original query, you got only one for each combination.

For More Information


This was last published in August 2003

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