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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.