[Oracle] Join 조인

명시적 조인

SELECT employees.employee_id, department_name
FROM employees
JOIN departments
ON employees.department_id = departments.department_id;

 

SELECT employees.employee_id, department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

 

묵시적 조인

SELECT employees.employee_id, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

---

LEFT OUTER JOIN

# WHERE절의 조건 만족 여부와 상관없이 employees 테이블 정보를 출력
SELECT emplyee_id, last_name, hire_date, department_name
FROM employees e
LEFT OUTER JOIN departments d
ON d.department_id = e.department_id
WHERE TO_CHAR(hire_date, 'YYYY-MM')
BETWEEN '1999-01' AND '1999-06';

 

# (+)로 지정된 테이블의 컬럼 값은 JOIN조건을 만족하지 않아도 반환
SELECT employee_id, last_name, hire_date, department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
AND TO_CHAR(hire_date, 'YYYY-MM') BETWEEN '1999-01' AND '1999-06';

---

하위 질의 (Subquery)

 

- 하위 질의의 결과가 1개의 값만 가지는 경우

# employees 테이블에서 평균 급여보다 많이 받는 사원을 검색
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

 

- 하위 질의에서 반환되는 값이 1개 이상인 경우 (IN 키워드 사용)

# 직무별(job_id) 최대 급여를 받는 사원 정보
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE (job_id, salary) IN
(SELECT job_id, MAX(salary)
FROM employees
GROUP BY job_id)
ORDER BY job_id;