조인과 서브 쿼리 비교

select emp.employee_id, emp.first_name, emp.DEPARTMENT_ID 

   

   from EMPLOYEES emp; 

    

    

select * from DEPARTMENTS; 

 

 

select emp.employee_id 

  , emp.first_name 

  , emp.DEPARTMENT_ID 

  ,( 

  select dep.department_name  

    from departments dep 

    where dep.department_id = emp.department_id 

  ) 

    from EMPLOYEES emp; 

 

 

select emp.employee_id 

  , emp.first_name 

  , emp.DEPARTMENT_ID 

  ,( 

  select dep.department_name  

    from departments dep 

    where dep.department_id = emp.department_id 

  ) 

    from EMPLOYEES emp 

    order by emp.department_id; 

     

     

     

    select  

    count(emp.employee_id) as cot 

    ,avg(emp.salary) 

    ,emp.DEPARTMENT_ID 

  ,( 

  select dep.department_name  

    from departments dep 

    where dep.department_id = emp.department_id 

  ) 

    from EMPLOYEES emp 

    group by emp.department_id; 

     

     

    --join 으로 표현하면 

    select count(emp.employee_id) as cot 

    ,avg(emp.salary) 

    ,emp.department_id 

    ,dep.DEPARTMENT_NAME 

    from EMPLOYEES emp left outer join DEPARTMENTS dep 

    on emp.DEPARTMENT_ID = dep.DEPARTMENT_ID 

    group by emp.DEPARTMENT_ID, dep.DEPARTMENT_NAME;