INNER JOIN, OUTER JOIN , 서브쿼리

--innner join on 사용

select *

from employees a inner join jobs j

on a.job_id = j.job_id

where a.job_id like '%A%';

 

--inner join using 사용

select *

from employees a inner join jobs j

using (job_id)

where job_id like '%A%';

 

--가존 조인 outer 대신

select *

from employees emp , job_history jhis

where emp.employee_id = jhis.employee_id(+);

 

--left outer join on 사용

select *

from employees emp left outer join job_history jhis

on emp.employee_id = jhis.employee_id

where emp.employee_id like '%1%';

 

--right outer join using 사용

select *

from employees emp right outer join job_history jhis

using (employee_id);

 

--full outer join on 사용

select *

from job_history jhis full outer join employees emp

on emp.employee_id = jhis.employee_id

where emp.employee_id like '%1%';

 

--full outer join using 사용

select *

from job_history jhis full outer join employees emp

using (employee_id);

 

-- cross join on 카타시안 프로덕트

select *

from employees emp cross join departments dep

on emp.DEPARTMENT_ID = dep.DEPARTMENT_ID;

 

--서브쿼리 IN 연관성 없는 서브쿼리

select count(*)

from employees

where department_id IN (

select department_id

from departments

where manager_id IS NOT NULL

);

 

--서브쿼리 EXISTS 연관성 있는 서브쿼리

select count(*)

from employees emp

where EXISTS (

select 1

from departments dep

where dep.manager_id IS NOT NULL

AND emp.department_id = dep.department_id

);

query.zip