CODEWITHSUNDEEP
sqloracle-databaseexists
Jamie Taylor
Jamie Taylor

Reputation: 3530

Selecting column from an exists statement

This is a follow-up to my previous question: Optimisation of an oracle query

I am now using the query as follows

select t1.column1, t2.column2 
    from table1@dev t1 
  where exists
         ( select *
              from table2@dev 
            where t2.column2 = t1.column1
              and t2.column3 > 0
                    ) 
order by column1

But the problem is I can't access column2 from t2 i'm getting the error

t2.column2 invalid identifier

Is it possible to access the column from this table when using EXISTS?

Thanks in advance

Upvotes: 0

Views: 131

Answers (1)

SWeko
SWeko

Reputation: 30892

Exists (as the name implies) merely checks if that thing exists, it does not return any data beyond the true/false of the existence predicate, so no, it's not possible to return data from an exists statement.

To select something from multiple tables, you need to join them, like this:

select t1.column1, t2.column2      
  from table1@dev t1    
    inner join table2@dev t2 on t2.column2 = t1.column1
 where t2.column3 > 0
 order by t1.column1

Upvotes: 2

Related Questions