2

I have the following oracle SQL code, but I can't understand what is the purpose of ordering by a subquery. Anyone can explain it clearly to me ?

SELECT employee_id, last_name
  FROM employees e
 ORDER BY (
            SELECT department_name
            FROM departments d
            WHERE e.department_id = d.department_id
           );
ashur
  • 4,177
  • 14
  • 53
  • 85

2 Answers2

4

The ordering is done by results from other table. In this case the query returns only results from employees table, but the ordering is done by department_name, which is stored in departments table.

You could achieve identical result by using join, selecting only values from employees table, and ordering by department_name from departments table:

SELECT e.employee_id, e.last_name
FROM employees e INNER JOIN departments d
    ON e.department_id = d.department_id
ORDER BY d.department_name

This query is valid if employee must always have a department. If there can be employees without departments then you should use LEFT join instead.

dotnetom
  • 24,551
  • 9
  • 51
  • 54
  • This query relies on all values in the employees.department_id column existing in the departments.department_id column. Using a left outer join instead of the inner join would mean you guaranteed both queries always returning the same data. It would also be guaranteed if employees.department_id was a not null column, and was foreign keyed to departments.department_id. – steve godfrey Mar 08 '15 at 19:22
  • @stevegodfrey That's a very good point, thank you. I added note at the end of the answer about the limitations of this query – dotnetom Mar 09 '15 at 05:14
1

The clear intention of that query is employee_id and last_name from employees should be order by department_name from departments.

Okay, you don't subquery then go for join 

select e.employee_id,e.last_name from employees e join departments d on
e.department_id = d.department_id order by d.department_name;
venkat
  • 503
  • 6
  • 15