3 Replies - 1133 Views - Last Post: 15 September 2018 - 01:52 AM

#1 dgupta111   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 66
  • Joined: 28-August 17

SQL

Posted 14 September 2018 - 01:10 PM

Hi,I have a question.

WHy is this expression valid in ORACLE?

select * from a b where b.id= (select max(b.id) from a);

How can we apply a function to a column outside the query here max(b.id) is applied outside the outer query.

What is the relation between renaming a table inner or outer in subqueries and its alternative WITH clause ?

Links to the official documentation (SQL Language reference) would be grateful.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15319
  • View blog
  • Posts: 61,433
  • Joined: 12-June 08

Re: SQL

Posted 14 September 2018 - 01:12 PM

What does "select max(b.id) from a" produce? A single value, right? Why wouldn't that work in a WHERE clause where it is asking to compare against a value here. "where b.id= ".
Was This Post Helpful? 0
  • +
  • -

#3 dgupta111   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 66
  • Joined: 28-August 17

Re: SQL

Posted 14 September 2018 - 01:14 PM

View Postmodi123_1, on 14 September 2018 - 01:12 PM, said:

What does "select max(b.id) from a" produce? A single value, right? Why wouldn't that work in a WHERE clause where it is asking to compare against a value here. "where b.id= ".

Thanks for the guidance
Was This Post Helpful? 0
  • +
  • -

#4 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7492
  • View blog
  • Posts: 15,527
  • Joined: 16-October 07

Re: SQL

Posted 15 September 2018 - 01:52 AM

While outer tables can be accessed in subqueries, this is problematic:
select max(b.id) from a;


It becomes meaningless in this context, so you're effectively writing:
select max(b.id) from dual;



Which means that
select * from a b where b.id= (select max(b.id) from a);


is effectively
select * from a b where b.id= b.id;


which is, then, obviously:
select * from a;


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1