CODEWITHSUNDEEP
sqloracle-databaseselect
user452187
user452187

Reputation: 123

Why does only one of these SQL statements work?

Why does the first SQL statement not execute, and the second one does?

1.

select e.employee_id, e.last_name, e.salary as "SALARY"
from employees e
where employees.salary > (select avg(salary) from employees) 
order by SALARY;

2.

select e.employee_id, e.last_name, e.salary as "SALARY"
from employees e
where e.salary > (select avg(salary) from employees) 
order by SALARY;

Upvotes: 0

Views: 77

Answers (2)

cardmagik
cardmagik

Reputation: 1698

To make the first one work, replace all occurrences of "e" with employees and remove the "e" alias:

select employees.employee_id, employees.last_name, employees.salary as "SALARY"
from employees 
where employees.salary > (select avg(salary) from employees) 
order by SALARY;

Upvotes: 0

John Woo
John Woo

Reputation: 263733

Because you have already define an alias for the table name employee.

So in the WHERE clause, you need to use the alias, not the table name since it is not valid anymore.

SELECT e.employee_id, e.last_name, e.salary as "SALARY" 
FROM   employees e 
WHERE  e.salary > (select avg(salary) from employees) 
    -- ^ ALIAS should be used, not the tableName
ORDER  BY SALARY;

This might be a little off-topic but this is an additional info.

The Order of SQL Operation is as follows:

  • FROM clause
  • WHERE clause
  • GROUP BY clause
  • HAVING clause
  • SELECT clause
  • ORDER BY clause

Since you have supplied an alias for the table name on the FROM clause, anything the follows in the order of operations is now referring to that alias given and not on the table name itself.

Upvotes: 5

Related Questions