[SQL] JOIN

서로 다른 테이블을 합치는 JOIN

 

SELECT emp_id, emp_name, dept_id, phone, email
	FROM employee 
	WHERE retire_date IS NULL;

 

employee 테이블에 department_name 을 추가하고 싶을 때:

• department_name은 department 테이블에 있음
  • deparment table에서 department_name을 가져와야 됨
• * 어떤 값을 기준으로 가져올까?  기준이 되는 값이 있어야 됨
• parent table -> child table의 관계를 잘 파악해야 됨

JOIN 연산 수행

- JOIN 합칠테이블 ON 현재테이블.기준열 = 합칠테이블.기준열

-- JOIN 으로 두 테이블 합치기
SELECT emp_id, emp_name, employee.dept_id, dept_name, phone, email
    FROM employee 
    JOIN department ON employee.dept_id = department.dept_id --이 부분
    WHERE retire_date IS NULL;
    
    
-- 테이블 이름을 별명으로 쿼리 단순화
SELECT emp_id, emp_name, employee.dept_id, dept_name, phone, email
    FROM employee AS e
    JOIN department AS d ON e.dept_id = d.dept_id --이 부분
    WHERE retire_date IS NULL;

-- 어떤 테이블에서 가져온 칼럼이름인지 명시해주기
SELECT e.emp_id, e.emp_name, e.dept_id, d.dept_name, e.phone, e.email
    FROM employee AS e
    INNER JOIN department AS d ON e.dept_id = d.dept_id
    WHERE e.retire_date IS NULL;
    
    
-- join 여러번 가능
SELECT v.emp_id, e.emp_name, d.dept_name,  v.begin_date,  v.reason, v.duration
    FROM vacation AS v
    JOIN employee AS e ON v.emp_id = e.emp_id
    JOIN department AS d ON e.dept_id = d.dept_id
    WHERE e.gender = 'M' AND e.dept_id = 'SYS';
    
 
SELECT v.emp_id, e.emp_name, d.dept_name,  v.begin_date,  v.reason, v.duration, c.club_name
	FROM vacation AS v
    JOIN employee AS e ON v.emp_id = e.emp_id
    JOIN department AS d ON e.dept_id = d.dept_id 
    JOIN club_join AS cj On e.emp_id = cj.emp_id 
    JOIN club AS c On cj.club_id = c.club_id
    WHERE e.gender = 'M' AND e.dept_id = 'SYS';

 

 

 

 

** JOIN 연산 시

-- 7개 부서가 조인하니까 6개가 되어버림 : 전략기획팀은 unit id 가 없음
SELECT dept_id, dept_name, unit_name
	FROM department AS d
	JOIN unit AS u ON d.unit_id = u.unit_id; 

/* inner join 은 양쪽 테이블에서 mapping 되는 것만 가져 옴*/

 

 

inner join, outer join 

+ groupby

-- 직원 휴가 데이터 : 휴가를 간 직원만
SELECT e.emp_id, e.emp_name, v.begin_date, v.end_date, v.reason, v.duration
	FROM employee AS e
	INNER JOIN vacation AS v ON e.emp_id = v.emp_id
	WHERE retire_date IS NULL
	ORDER BY e.emp_id ASC;
    
-- 모든 직원 정보 조회
SELECT e.emp_id, e.emp_name, v.begin_date, v.end_date, v.reason, v.duration
	FROM employee AS e
	LEFT OUTER JOIN vacation AS v ON e.emp_id = v.emp_id
	WHERE retire_date IS NULL
	ORDER BY e.emp_id ASC;
    
-- 휴가를 총 몇 번 갔는지, 총 며칠 갔는지
SELECT e.emp_id, e.emp_name, 
	COUNT(*) AS count, 
	SUM(v.duration) as total_duartion
	FROM employee AS e
	LEFT OUTER JOIN vacation AS v ON e.emp_id = v.emp_id
	WHERE retire_date IS NULL
	GROUP BY e.emp_id
	ORDER BY e.emp_id ASC;

 

cross join

 

 

하위쿼리

-- S0011 직원과 같은 날 입사한 직원 정보 조회
SELECT emp_id, emp_name, hire_date
	FROM employee 
	WHERE hire_date = (SELECT hire_date FROM employee WHERE emp_id = 'S0011');
    
-- 휴가 간 적이 있는 SYS부서 직원 조회 
SELECT emp_id, emp_name, dept_id
    FROM emplyee AS e
    WHERE emp_id IN (SELECT emp_id FROM vacation) 
    	AND dept_in='SYS';
 
-- 휴가 간 적이 없는 SYS부서 직원 조회 
SELECT emp_id, emp_name, dept_id
    FROM emplyee AS e
    WHERE emp_id NOT IN (SELECT emp_id FROM vacation)
    	AND dept_in='SYS';