조인 (JOIN) - 리뷰 및 예제

<조인 (JOIN) - 리뷰 및 예제>

 

• HR 계정 테이블 관계도

 

 

• 기본키 Primary Key (PK): 빨간색 칼럼(열)

• NULL 또는 중복값 불가능

 

• 외래키 Foreign Key (FK): 기본키와 연결된 다른 테이블의 칼럼

• 항상 참조하는 기본키에 있는 데이터를 사용

• NULL값 가능

• 되도록 기본키와 같은 열 이름으로 함

 

 

 

<조인 예제>

 

 

• departments 테이블에 있는 manager_id와 employees 테이블의 employee_id를 조인하여 부서명, 매니저번호, 매니저 이름, 전화번호를 출력

SELECT d.department_name 부서명,
       d.manager_id 매니저번호,
       e.last_name ||' '|| e.first_name "매니저이름(직원이름)",
       e.phone_number 전화번호
FROM departments d
JOIN employees e
    ON d.manager_id=e.employee_id; --이름은 다르지만 참조하고 있음

 

 

• 조인을 이용하여 사원들의 직원번호 (employee_id), 고용일자(hire_date), 직종(job_id), 직책(job_title) 출력

SELECT e.employee_id 사원번호,
    e.hire_date 고용일자,
    j.job_id 직종,
    j.job_title 직책
FROM employees e
JOIN jobs j
    ON e.job_id=j.job_id;

 

 

• 직책(job_title)이 'Sales Manager'인 사원들의 입사년도 그룹 별 평균 급여를 출력. 입사년도를 기준으로 오름차순 정렬 (jobs 테이블과 조인, job_id를 이용, 입사년도: TO_CHAR(e.hire_date, 'YYYY')

SELECT TO_CHAR(e.hire_date,'YYYY') 입사년도,
    ROUND(AVG(e.salary)) 평균급여
FROM employees e
JOIN jobs j
    ON e.job_id=j.job_id
WHERE j.job_title='Sales Manager'
GROUP BY TO_CHAR(e.hire_date,'YYYY')
ORDER BY 1;

 

 

• 각각의 도시(city)에 있는 모든 부서 직원들의 평균급여를 조회. 평균급여가 가장 낮은 도시부터 도시명(city)과 평균연봉, 해당 도시의 직원 수를 출력. 단, 도시에 근무하는 직원이 10명 이상인 곳은 제외하고 조회. 

SELECT lo.city, round(avg(e.salary)), count(*)
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations lo ON d.location_id = lo.location_id
GROUP BY lo.city
HAVING COUNT (*)<10
ORDER BY 2;

 

 

• 자신의 매니저보다 급여를 많이 받는 직원들의 성(last_name), 급여(salary)와 매니저의 last_name과 salary를 출력

SELECT e.employee_id, e.last_name, e.salary, m.last_name, m.salary
FROM employees e --자신
JOIN employees m --매니저
    ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;