SQL SELECT문으로 이것저것 다 해보기(4) / JOIN

우리가 다른 테이블에 있는 자료를 함께 묶어서 작업해야 할 일이 생기곤 합니다. 모든 일을 혼자서 다 할 수 없는 것처럼 다른 테이블에서 내가 필요한 정보를 꺼내오는 작업이 필요한데 그럴 때 SQL에서는 JOIN을 활용합니다.

먼저 제일 많이 쓰이고 중요한 JOIN이 바로 INNER JOIN 입니다. 이름처럼 안에서 꺼내온다 즉 두 개 이상의 테이블을 활용하여서 특정 조건에 맞는 값을 가진 새로운 테이블을 만드는 것입니다. 백문이 불여일견이라고 예제를 통해 알아가보겠습니다. ansi SQL과 Oracle SQL, 두 가지 버전으로 사용하는 방법을 알아볼 것입니다. 

 

1. INNER JOIN 

--ansi SQL
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e INNER JOIN departments d 
ON e.department_id = d.department_id;

--Oracle SQL
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

일단 먼저 사용할 두 가지 테이블은 EMPLOYEES 테이블과 DEPARTMENTS 테이블입니다. INNER JOIN은 수학 시간에 배웠던 교집합 개념이라고 생각하면 이해가 쉽습니다. 두 테이블 모두가 가지고 있는 값만 데려오기 때문이죠. ansi SQL같은 경우에는 우리가 사용하고자 하는 JOIN의 종류를 직접적으로 명시해줍니다. FROM employees e INNER JOIN departments d 이렇게 되어있는데 employees와 departments 뒤에 붙어있는 e와 d는 단축키와 같다고 생각하면 좋겠습니다. 간단하고 편리하기도 하지만 무엇보다도 저렇게 쓰지 않고 만약 나는 귀찮아도 employees.department_id 이렇게 할래 하더라도 컴퓨터가 못 알아먹습니다. 그러니까 보다 더 간단한 방법을 사용하는것이 좋겠죠? 

 

오라클 SQL같은 경우에는 INNER JOIN을 직접적으로 쓰진 않고 어디서! 를 나타내기 위해 WHERE를 씁니다. 뭐가 더 낫다고 말하기는 어렵지만 제가 느끼기에는 오라클이 더 직관적으로 느껴집니다. 

--예제 job_id = IT_PROG 업무명
SELECT e.employee_id, e.first_name, e.job_id, j.job_id, j.job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id AND e.job_id = 'IT_PROG';

그렇다면 실제 예제로 업무아이디가 IT_PROG'인 사람의 사원번호, 이름, 업무아이디 그리고 전체 업무명을 알아보겠습니다. EMPLOYEES 테이블만 가지고 보면 우리는 예를 들어 Alexander라는 사람의 업무아이디가 IT_PROG인지는 알겠는데 이게 프로그래머를 말하는건지 프로게이머를 말하는건지 전체 업무명을 알 수가 없습니다. 이럴때 JOBS 테이블과 이너조인을 해주면 그 테이블 안에 있는 job_title을 알아낼 수 있게 됩니다.

 

Alexander는 프로그래머였습니다..

꼭 두 개의 테이블만 조인이 가능한것일까요? 그렇지 않습니다. 만약 내가 원하는 정보가 a테이블에도 있고 b테이블에도 있다면 세 개의 테이블을 조인해줄 수 있습니다. 

 

SELECT e.employee_id, e.first_name, d.department_name, j.job_title
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id AND e.job_id = j.job_id AND e.employee_id = 101;

내가 갖고 있는 테이블은 EMPLOYEES 테이블밖에 없는데 원하는 것은 부서명과, 업무명이 한 눈에 들어왔으면 좋겠습니다. 그런 경우에는 EMPLOYEES 테이블과 DEPARTMENTS 테이블, JOBS 테이블을 함께 조인해줘야 합니다. 

 

그런 경우에는 조건을 AND를 두 번 써서 걸어주면 되는데요, 일단 부서명을 얻기 위해서 EMPLOYEES 테이블과DEPARTMENTS 테이블을 먼저 조인한다면 WHERE e.department_id = d.department_id가 되겠죠. 그러나 여기서 업무명도 함께 알아낼 것이기 때문에 AND를 한 번 더 써서 AND e.job_id = j_job_id를 써줍니다. 여기까지만 쓰면 부서명과 업무명을 다 갖고 있는 106명이 나타나게 됩니다. 만약 조건을 추가적으로 더 걸어서 사원번호가 101인 사람을 구해줘! 하면 단 한 명의 사원이 나타나게 됩니다. 사원번호는 여럿이서 공유하는 것이 아니라 나만 갖고 있는 번호이니까요. 

결과값은 위와 같습니다. Neena가 사원번호가 101번인 사원이었네요

2. CROSS JOIN 

많이 쓰이지는 않지만 존재 자체는 알아둬야 하는 크로스조인입니다. 

 

--cross join 
--ansi SQL
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e CROSS JOIN departments d; 

--Oracle SQL
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e, departments d;

cross, 말 그대로 교차한다는 뜻이죠? EMPLOYEES 테이블과 DEPARTMENTS 테이블을 서로 교차해서 하나하나 다 묶어준다고 생각하면 됩니다. 총 2889개의 행이 나오는데, EMPLOYEES 테이블의 행이 총 107개이고, DEPARTMENTS 테이블의 행이 총 27개 입니다. 107 * 27 = 2889개이죠? 쉽게 말해서 107개 행 중에 첫 번째 행에 27개를 붙이고, 두 번째 행에 27개를 붙이고 그렇게 쭉쭉 107번째 행까지 한다는 뜻입니다.

 

3. LEFT OUTER JOIN / RIGHT OUTER JOIN

공통적으로 가지고 있는 데이터만 가져오는 것만도 해봤고, 아예 싹 다 가져오는 것도 해봤습니다. 그러면 이번에는 위의 그림처럼 이너에 덧붙여서 왼쪽 테이블에 있는 값만 가져오고, 또 반대로 오른쪽 테이블에 있는 값만 가져오려면 어떻게 해야 할까요?

--left outer join 

--ansi SQL
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;

--Oracle SQL
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e, departments d 
WHERE e.department_id = d.department_id(+);

안시 SQL같은 경우 left outer join을 한다고 아예 분명하게 명시하기 때문에 이해하기가 쉽습니다. 그러나 오라클에서는 조금 다르게 (+)를 붙여주는데요? 근데 LEFT ,즉 왼쪽이라 했는데 왜 WHERE절에는 오른쪽에 (+)를 붙여준것일까요? 먼저 (+) 이 사인 자체에 대해 설명하자면, 이것은 오라클에서 outer join을 하겠다고 할 때 쓰는 구문(syntax)입니다. outer join operator인 것이죠.

 

WHERE conditional statement = optional statement; 

 

이렇게 되는데, 여기서 optional statement는 비어있거나, 널값을 가져도 됩니다. 이것을 가져다가 왼쪽의 conditional statement인 e.department_id에서 조인을 할 것이기 때문에 가져오는 값인 격으로 d.department_id(+) 이렇게 (+)를 붙여주는 것이죠 . 쉽게 말해서 만약 left outer join을 하고자 한다면 = 을 기준으로 명시되어있는 쪽과 반대쪽에 (+)를 붙여주는 것이라고 생각하면 되겠습니다. 예를 들어 hr계정의 EMPLOYEES 테이블에는 단 한 사람만 department_id가 null값을 가지고 있습니다. 

제일 마지막에 Kimberly만 department_id가 null값인데요, 만약 left outer join을 하지 않고 inner join만 했더라면 Kimberly는 명단에 등장하지 못했을 것입니다. 그러나 우리는 left outer join을 통해 e.departmet_id에 null값을 가진 Kimberly 조차 포함하기로 했는데요, 이처럼 Kimberly는 e.department_id와 d.department_id에 아무런 내용이 없는데도 포함이 된 것입니다.

 

만약 (+)를 왼쪽에 두고 right outer join을 해보면 어떨까요? 그러면 결과값이 다르게 나옵니다. 

SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e, departments d 
WHERE e.department_id(+)= d.department_id;

그러면 이번에는 DEPARTMENTS 테이블에는 있지만 EMPLOYEES 테이블에는 없는 값을 가져오는 셈이 됩니다. 실제로 DEPARTMENTS 테이블에서 부서번호 120부터는 직원번호나 이름이 존재하지 않습니다. 그러므로 당연히 부서번호도 존재하지 않겠죠. 그럼에도 불구하고 right outer join으로 가져오게 되면 null값이어도 불러들일 수 있겠습니다.

 

여기서 한 가지 생각해볼 수 있는 점은, 굳이 (+)의 위치를 바꿔가면서 쓰지 않아도 해당하는 테이블만 바꿔주면 left outer join의 틀 그대로 (+)는 오른쪽에 두고 right outer join을 써먹을 수 있다는 점입니다. 

 

즉 위에서 했던 e.department_id(+) = d.department_id;와 d.department = e.department_id(+);가 동일한 결과값을 낸다는 것이죠. 결과적으로 (+)을 붙이는 곳은 null값이 있는 곳이라는 것만 잘 기억하면 문제없이 풀이가 가능하겠습니다. 의심스럽다면 직접 SQL을 작성해보세요~ 

 

4. FULL OUTER JOIN 

--full outer join - left outer join + right outer join  데이터 다 나오게 만들기

--ansi SQL
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

--Oracle SQL
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e, departments d WHERE e.department_id = d.department_id(+)
UNION
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e, departments d WHERE e.department_id(+)= d.department_id;

크로스조인처럼 빼먹는 영역 없이 두 테이블을 알뜰살뜰하게 이용한다는 점은 똑같지만 크로스조인처럼 모든 행을 교차해서 결과를 출력하지는 않는 풀아우터조인입니다. 집합으로 따지면 합집합의 개념인데요, 두 테이블의 데이터가 하나도 빠짐없이 다 나오게 할 때 사용합니다. 별 다른 특이사항은 없지만 한 가지 유의하실 부분은 안시 SQL에서는 별 문제가 없지만 오라클에서는 풀아우터조인을 하는 것이 살짝 까다롭습니다. left outer join 과 right outer join을 먼저 한 다음에 그 결과를 UNION으로 묶어줘야합니다. 

--차집합

--left outer join 
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e, departments d 
WHERE e.department_id = d.department_id(+) AND e.department_id IS NULL;

--right outer join 
SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e, departments d 
WHERE e.department_id(+) = d.department_id AND e.department_id IS NULL;

이렇게 구했던 것 중에 만약 예를 들어 left outer join 기준으로 Kimberly만 뽑아내고 싶다면 어떻게 해야할까요? 그런 경우에는 AND로 조건을 하나 더 걸어주면 됩니다. 위에서 봤다시피 Kimberly는 department_id가 null값이었습니다. 그러므로 Kimberly만 뽑아내려면, Kimberly만 갖고있는 조건을 걸어주면 되겠죠~ 바로 IS NULL 조건입니다. right outer join 역시 마찬가지입니다. 

SELECT e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id WHERE d.department_id IS NULL OR e.department_id IS NULL;

 

만약 양쪽 테이블에서 각각 null값인것만 데려오고 싶다면 그건 또 어떻게 해야할까요? 조건으로 OR을 걸어주면 되겠습니다. 조심해야할 점은 만약 AND로 걸어버리면 employees에서도 null값이고, departments에서도 null값인것만 가져오게 되는데 그러면 Kimberly만 따라오게 됩니다. OR로 걸어줘야 양 테이블에서 값을 같이 가져올 수 있습니다. 

 

5. SELF JOIN

--emp : 사원, mgr : 상사

SELECT emp.employee_id, emp.first_name, emp.manager_id, mgr.employee_id, mgr.first_name
FROM employees emp, employees mgr WHERE emp.manager_id = mgr.employee_id;

이름에서 알 수 있다시피 스스로 자기테이블에 조인을 거는 것입니다. 하나의 테이블로 사용하지만 마치 자웅동체처럼 두 개의 테이블로 나눠서 생각하면 됩니다. 편의를 위해서 FROM 절에서 구분하기 쉽게 별명을 만들어주는 것이 좋겠죠 이때 AS는 사용하면 안됩니다. 그냥 employees emp처럼 써야합니다. 이것이 왜 필요한가? 에 대해 말하자면 예를 들어 employees 테이블은 사원 번호와 매니저 번호가 한 곳에 있습니다. 그런데 만약 이런 경우에는 어떻게 할까요?

 

Luis라는 사람에게는 누가 매니저인지 궁금합니다. 그런데 셀프조인을 해주지 않으면 아 Luis의 매니저 번호는 108번이구나, 그럼 employee_id가 108번인 사람이 누군지 찾아야겠군! 이렇게 내려가야햐합니다. 그러나 셀프조인을 해주면 다음과 같이 한 테이블에서 우리가 원하는 값을 찾아서 옆에 붙여넣어줄수있기 때문에 보다 더 효율적입니다. 

 

참고자료 : https://way2tutorial.com/sql/sql-join.php