Inner query fail but gives output when entire query runs | Oracle 19c |

user_19868464 0
Viewed 44

I am not getting what actually happening with this query and How the output is returned

My first table : tbl_a and its data

Table 1

My second table : tbl_b and its data

Table 2

Now what I observed is when inner query is run it fails as the column eage does not belong to tbl_b. It belongs to tbl_a

Inner query

Error :

Inner query error

But When I run the entire query it gives me output

Output

May I know how this query actually returning output ? With a detail explanation

select * from tbl_a where eid in (
select bid from tbl_b where eage=24
)

Even I tried this query also it gives me output when entire query is run and Only Inner query is run it fails

select * from tbl_a where eid in (
select bid from tbl_b where bid=1 and eage=24
)

Any solution is much appreciated !!!

oracle19c oracle sql
1 Answers

The sql engine evalues the complete query before it executes. Since "tbl_b" does not have column "eage", it will check if that columns exists elsewhere in the query and use that.

If that column does exist but it exists in more than one place and it is unclear which one to use, oracle will raise the error ORA 00918 column ambiguously defined.

This seems like a bit of magic at first but as an oracle developer, you should make the queries as readable as possible. By properly aliasing the tables, this whole question becomes trivial. Nothing will change in the execution path of the query, but this query is so much easier to understand:

select a.* 
  from tbl_a a 
 where a.eid in 
 (
    select b.bid 
      from tbl_b b 
     where a.eage=24
 )

Note that by properly aliasing the tables, the "ORA-00918" will be avoided too.

user_41898140
Related