Optimizing an Oracle SQL Query

Here is a query I have that returns the chain of supervisors for an employee but it uses a bunch of nested SELECT statements. I'd like to know whether this query could be refactored to be more efficient. The query is for an application where 3 levels of management authorize an employee to take a training class. Currently we require 3 levels of approvals, but this could change to 4 or more.

SELECT badge as employee, 
   supervisor_badge as boss1, 
   (select supervisor_badge FROM hr_data level2 WHERE badge = level1.supervisor_badge) as boss2
   (select supervisor_badge FROM hr_data level3 WHERE badge = 
           (select supervisor_badge FROM hr_data level开发者_开发知识库2 WHERE badge = level1.supervisor_badge)) as boss3
   FROM hr_data level1 WHERE BADGE = '123456';

badge = the employee's ID

supervisor_badge = the badge of the employee's supervisor

bothe fields are in the hr_data table

           badge    supervisor_badge 
           123456   111111
           111111   454545
           454545   332211

output

 employee       boss1      boss2      boss3
 123456         111111     454545     332211

I don't have a database handy, to mock this up, so I'll wing it. I tried to use your same naming conventions, for clarity.

SELECT   level1.badge as employee
        ,level2.badge as boss1
        ,level3.badge as boss2
        ,level3.supervisor_badge as boss3
FROM    hr_data level1 
        INNER JOIN hr_data level2 
            ON level2.badge = level1.supervisor_badge
        INNER JOIN hr_data level3 
            ON level3.badge = level2.supervisor_badge
WHERE       level1.badge = '123456'

IMPORTANT NOTE: This will only return records where data exists at all joins. To return records that have fewer than 3 bosses (i.e. only boss1 and boss2, but no boss3), change the INNER JOIN statements to LEFT JOIN.

Use joins rather than subqueries.

SELECT
    e.badge, b1.badge, b2.badge, b3.badge
FROM
    hr_data e
LEFT JOIN hr_data b1 ON e.badge=b1.badge
LEFT JOIN hr_data b2 ON b1.badge=b2.badge
LEFT JOIN hr_data b3 ON b2.badge=b3.badge
WHERE
    e.badge='123456';

As the level is variable, you may want to consider using a Stored Procedure to internally loop for the prespecified number of levels (until it hits the top, e.g., your CEO).