CODEWITHSUNDEEP
sqldatabase
user7657133
user7657133

Reputation: 1

Why this subquery fails?

select * 
from 
    (select * from person) as t1; /*ok*/

select * 
from 
    (select * from person) as t1 
where t1.birthday >= '1987-04-09'; /*ok*/

select * 
from 
    (select * from person) as t1 
where 
    t1.birthday = (select max(birthday) from t1); /* fails with 't1 doesn't exist' */

I know that the correct SQL for the 3rd query is

select * 
from person 
where person.birthday = (select max(birthday) from person) /*ok*/

Would anyone help me understand why the 3rd SQL query failed?

Thanks a lot

Upvotes: 0

Views: 51

Answers (1)

Shehryar
Shehryar

Reputation: 31

The SQL Query Execution Order is:

  1. From/Join
  2. Where
  3. Group By
  4. Having
  5. Select
  6. Distinct
  7. Order by

In your case, 'from t1' will be executed first and it will throw an error because t1 hasn't yet been recognised by the DB.

Better use the table name instead of t1.

Upvotes: 1

Related Questions