oracle10g - sql not throwing invalid identifier -


i asking question because not getting error expect there should error. please me understand under circumstances possible. have query:

select foracid,acct_name, schm_code, schm_type tbaadm.gam  acid  in(select acid tbaadm.iar); 

this query returning results without throwing error. expect invalid identifier because table tbaadm.iar not have field acid. when run:

 select acid tbaadm.iar; 

i get:

ora-00904: "acid": invalid identifier 00904. 00000 -  "%s: invalid identifier" *cause:     *action: error @ line: 772 column: 8 

i using sqldeveloper , oracle 10g. kind of strange me. worth mention though there field in tbaadm.iar id , right query should be:

select foracid,acct_name, schm_code, schm_type tbaadm.gam  acid  in(select entity_id tbaadm.iar); 

what going on here?

a subquery that's used in in clause can reference columns outer query, because necessary in correlated subqueries. clause equivalent to:

where acid in (select tbaadm.gam.acid tbaadm.iar) 

an example of correlated subquery shows why necessary is:

select * outer_table somefield = (select someotherfield                     inner_table                    inner_table.id = outer_table.inner_id) 

this more common use, field outer table used in where clause of subquery. sql isn't picky field outer query used. can used anywhere in subquery expression permitted, includes select clause.


Comments

Popular posts from this blog

google api - Incomplete response from Gmail API threads.list -

Installing Android SQLite Asset Helper -

Qt Creator - Searching files with Locator including folder -