join(조인)

1. join이란

: 두 개 이상의 테이블을 서로 연결하여 데이터를 검색할 때 사용하는 방법으로, 두 개의 테이블을 마치 하나의 테이블인 것처럼 보여주는 것

2. join의 기본 사용 방법

• 두 개의 테이블에 하나라도 같은 컬럼이 있어야 함
• 두 컬럼의 값은 공유되어야 함
• 보통 join을 위해 기본키(Primary Key)와 외래키(Foreign Key)를 활용
  

inner join

// 벤다이어그램으로 생각

• 각 테이블에서 join 조건에 일치되는 데이터만 가져온다
• inner join은 교집합
• select * from A inner join B
  
  

• join 조건에 일치하는 데이터 및 일치하지 않는 데이터를 모두 select함
• outer join은 inner join과는 다르게 주(main) 테이블이 어떤 테이블인지가 중요 그래서 어떤 테이블이 중심이 되느냐에 따라서 left outer join, right outer join, full outer join으로 세분화할 수 있다
• left outer join은 왼쪽에 있는 테이블이 메인
  right outer joind은 오른쪽에 있는 테이블이 메인
  full outer join은 왼쪽 테이블 모두가 메인이라는 뜻
  

1. left outer join

// 거의 이것만 쓰임

• 왼쪽 테이블기준
• join조건에 부합하는 데이터가 join 당하는 테이블에 있으면 해당 데이터를 없으면 null로 select함
  
• select * from A left outer join B

2. right outer join

• 오른쪽 테이블이 기준이 됨
• join 조건에 부합하는 데이터가 join 당하는 테이블에 있으면 해당 데이터를, 부재하면 Null로 select됨
  
• select * from A right outer join B

3. full outer join

• 양쪽 테이블 모두가 기준
• join 조건에 부합하는 데이터가 조인 당하는 테이블에 있으면 해당 데이터를, 부재현 무제한 nill이 생성
  

4. ANSI join vs oravle join

• SQL은 데이터베이스를 관리하기 위해 만들어진 프로그래밍 언어이며, 데이터 베이스를 관리해주는 대부분의 Management System(DBMS : Oracle, Mysql ...)들을 SQL를 사용
• 물론 DBMS 자체의 특수성 때문에 SQL 사용법이 조금씩 다르기도 하지만, 큰 틀에서 보면 나름대로의 보편성을 가지고 있음
• 이를 위해 미국 국립 표준 협회(American National Standards Institute, ANSI)에서도 SQL에 대한 보편적인 문법을 제시하고 있는데, 그것이 바로 ANSI Query임
• join도 마찬가지로 ANSI문법을 사용하는 경우가 대부분이긴 하지만, 편의에 따라 DBMS만의 자체 문법을 사용하기도 함
• 대표적으로 Oracle에서 사용하는 Oracle join이 있음

문법 코드

-- join
SELECT *
FROM EMPLOYEES e 
WHERE DEPARTMENT_ID = 125
;
SELECT *
FROM DEPARTMENTS d 
WHERE DEPARTMENT_ID = 50
;

-- inner join
SELECT e.EMPLOYEE_ID, e.FIRST_NAME, d.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMPLOYEES e INNER JOIN DEPARTMENTS d 
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE d.DEPARTMENT_ID  = 80
ORDER BY e.EMPLOYEE_ID 
;

SELECT e.EMPLOYEE_ID, e.FIRST_NAME, d.DEPARTMENT_ID, d.DEPARTMENT_NAME, l.LOCATION_ID, l.STREET_ADDRESS, l.CITY
FROM EMPLOYEES e 
INNER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
INNER JOIN LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID
WHERE l.CITY = 'Seattle'
;

-- left outer join
SELECT e.EMPLOYEE_ID , e.FIRST_NAME, d.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMPLOYEES e 
LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
ORDER BY e.EMPLOYEE_ID 
;
SELECT e.EMPLOYEE_ID, e.FIRST_NAME, d.DEPARTMENT_ID, d.DEPARTMENT_NAME 
FROM DEPARTMENTS d 
LEFT OUTER JOIN EMPLOYEES e ON d.DEPARTMENT_ID = e.DEPARTMENT_ID 
ORDER BY e.EMPLOYEE_ID 
;

-- right outer join (하고 잊어버리기)
SELECT e.EMPLOYEE_ID , e.FIRST_NAME, d.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMPLOYEES e 
RIGHT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
ORDER BY e.EMPLOYEE_ID 
;

-- full outer join
SELECT e.EMPLOYEE_ID , e.FIRST_NAME, d.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMPLOYEES e 
FULL OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
ORDER BY e.EMPLOYEE_ID 
;

문제 코드

-- 1) 사원들의 이름, 부서번호, 부서명을 출력하라
SELECT e.FIRST_NAME, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMPLOYEES e 
LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
;

-- 2) 30번 부서의 사원들의 이름, 직업, 부서명을 출력
SELECT FIRST_NAME, LAST_NAME, JOB_ID, DEPARTMENT_NAME 
FROM EMPLOYEES e 
LEFT OUTER JOIN DEPARTMENTS d  ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.DEPARTMENT_ID = 30
;

-- 3) 커미션을 받는 사원의 이름, 직업, 부서 번호, 부서명을 출력하라
SELECT e.FIRST_NAME, e.LAST_NAME, e.JOB_ID, e.DEPARTMENT_ID, d.DEPARTMENT_NAME 
FROM EMPLOYEES e LEFT OUTER JOIN  DEPARTMENTS d 
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.COMMISSION_PCT > 0
;

-- 4) 지역번호 2500에서 근무하는 사원의 이름, 직업, 부서 번호,부서명을 출력하라
SELECT e.FIRST_NAME, e.LAST_NAME, e.JOB_ID, e.DEPARTMENT_ID, d.DEPARTMENT_NAME 
FROM EMPLOYEES e INNER JOIN DEPARTMENTS d
ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE D.LOCATION_ID = 2500
;

-- 5) 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라
SELECT e.FIRST_NAME, e.LAST_NAME, d.DEPARTMENT_NAME 
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d  ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.FIRST_NAME LIKE '%A%'
;

-- 6) 사원이름과 그 사원의 관리자 이름을 출력하라
SELECT e.FIRST_NAME AS 사원명, e2.FIRST_NAME AS 매니저명, e.EMPLOYEE_ID, e2.EMPLOYEE_ID 
FROM EMPLOYEES e
INNER JOIN EMPLOYEES e2 ON e.MANAGER_ID = e2. EMPLOYEE_ID 
ORDER BY e.EMPLOYEE_ID
;

-- 7) 사원이름과 부서명과 월급을 출력하는데 월급이 3000 이상인 사원을 출력하라
SELECT e.FIRST_NAME, e.LAST_NAME, d.DEPARTMENT_NAME, e.SALARY  
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d  ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE SALARY >= 3000
;

-- 8) 급여가 3000에서 5000사이인 사원의 이름과 소속 부서명 출력하라
SELECT e.FIRST_NAME, e.LAST_NAME, d.DEPARTMENT_NAME
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d  ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.SALARY BETWEEN 3000 AND 5000
;

-- 9) 급여가 3000이하인 사원의 이름과 급여, 근무지(location_id, city)를 출력하라
SELECT e.FIRST_NAME, e.SALARY, d.LOCATION_ID, l.CITY 
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d  ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
LEFT OUTER JOIN LOCATIONS l ON d.LOCATION_ID = l.LOCATION_ID 
WHERE e.SALARY <= 3000 
;

-- 10) Steven King의 부서명을 출력하라.
SELECT  d.DEPARTMENT_NAME 
FROM DEPARTMENTS d
LEFT OUTER JOIN  EMPLOYEES e ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.FIRST_NAME = 'Steven' AND e.LAST_NAME = 'King'
;

-- 11) IT부서에서 근무하고 있는 사람들을 출력하라.
SELECT  e.FIRST_NAME, e.LAST_NAME, d.DEPARTMENT_NAME 
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d  ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE d.DEPARTMENT_NAME = 'IT'
;

-- 12) EMPLOYEES 테이블에서 사원번호,이름,업무, EMPLOYEES 테이블의 부서번호, 
-- DEPARTMENTS 테이블의 부서번호,부서명,근무지를 출력하여라.
SELECT  e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME, e.DEPARTMENT_ID, 
d.DEPARTMENT_ID, d.DEPARTMENT_NAME, d.LOCATION_ID 
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d  ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
;

-- 13) EMPLOYEES 테이블과 DEPARTMENTS 테이블의 부서번호를 조인하고 
-- SA_MAN 사원만의 사원번호,이름,급여,부서명,근무지를 출력하라.
SELECT  e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME, e.SALARY, d.DEPARTMENT_NAME, d.LOCATION_ID
FROM EMPLOYEES e
LEFT OUTER JOIN DEPARTMENTS d  ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE e.JOB_ID = 'SA_MAN'
;