[SQL] 조인(JOIN)

* 조인(JOIN)

 

- 두개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것이다. 한개 이상의 테이블에서 데이터를 조회하기 위해 사용한다.

- 두 테이블을 묶어서 추출된 결과를 새로운 테이블을 만들어 집어 넣어 보여주는 형식이다. 새로운 테이블은 데이터베이스 딕셔너리에 저장된다.

 

- 조인은 왜 필요할까?

- 두개의 테이블이 서로 릴레이션 관계를 맺고 있을때, 반드시 두개의 테이블을 조합해서만 원하는 정보를 얻을 수 있는 상황이 발생했다 치자.

- 이때, 두 테이블 모두 각각 검색해서 정보를 도출해야한다. 조회를 2번이나 해야한다는 뜻이다.

- 하지만 JOIN을 사용하면 2번 조회를 거칠 필요없이 한번만 검색을 해도 된다.

- 두 테이블을 한번에 묶어 보여주기 때문이다.

 

- JOIN은 두개 이상의 테이블을 결합해야만 원하는 결과를 얻을 수 있을때 한번의 검색으로 원하는 결과를 얻을 수 있도록 제공한다.

- JOIN의 종류로는 내부조인, 외부조인, 상호조인, 셀프조인, 안시 조인, 동등 조인, 세미 조인, 카타시안 조인이 있다.

 

조인 연산자에 따른 구분	동등 조인, 안티 조인
조인 대상에 따른 구분	셀프 조인
조인 조건에 따른 구분	내부 조인, 외부 조인, 세미 조인, 카타시안 조인
기타	안시 조인

 

 

 

 

 

 

* 내부 조인(INNER JOIN)

 

- 내부 조인은 조인 중에서 가장 많이 사용되는 조인이다. 일반적으로 JOIN이라고 말하면 내부조인이다.

 

- 특정 부서 번호에 대한 부서이름은 무엇인지 부서(DEPT) 테이블에 있다.

- 특정 사원에 대한 부서명을 알아내기 위해서는 부서 테이블에서 정보를 얻어와야 한다.

- 각 테이블의 기본키와 참조키는 사원번호(DEPTNO)로 구분한다.

 

 

 

- 이름이 SCOTT인 사원이 소속되어 있는 부서의 이름이 무엇인지 알아보려한다.

- SCOTT이란 사원의 이름을 알기위해선 사원 테이블에서 SCOTT이 어떤 부서번호를 가지고 있는지 검색해야한다.

- 다음 부서 테이블에서 부서번호를 검색해서 SCOTT의 부서를 얻어낸다.

- 지금까지 SCOTT의 부서이름을 알기위해서 총 2번의 검색을 했다.

 

 

 

 

- 하지만 조인을 사용하면 두 테이블의 결과를 하나로 묶어 보여준다.

- 더 편리하게 데이터를 조회할 수 있게 된다.

 

 

 

 

 

 

* 동등 조인 (EQUI JOIN)

 

- 가장 기본이 되며 일반적인 조인 방법이다.

- 동등 조인은 WHERE절에서 등호('=') 연산자를 사용해 2개 이상의 테이블이나 뷰를 연결한 조인이다.

- 즉 등호 연산자를 사용한 WHERE절 조건에 만족하는 데이터를 추출하는 조인이다.

- 이때, WHERE절에 기술한 조건을 조인 조건이라고 한다. 조인 조건은 컬럼 단위로 기술한다. 

 

 

 

- 예시

- 사원 정보를 출력할 때 각 사원들이 소속된 부서의 상세 정보를 출력하기 위해서 두개의 테이블을 조인할 것이다.

 

 

- 사원 테이블(EMP)과 부서 테이블(DEPT) 테이블의 공통 컬럼인 DEPTNO의 값이 일치(=)되는 조건을 WHERE 절에 조건을 걸어 사용했다.

- 테이블을 조인하려면 일치되는 공통 컬럼을 사용해야 한다. 컬럼의 이름이 같게 되면 혼동이 오기 때문에 컬럼 이름 앞에 테이블 이름을 써줘야 한다.

 

 

- 위 사진은 동등 조인을 한 결과이다. 부서 번호를 기준으로 같은 값을 가진 사원 테이블의 컬럼과 부서 테이블의 컬럼이 결합된다.

 

 

 

 

- 예시2

- AND를 이용해서 조건을 추가할 수도 있다.

- 사원 테이블(EMP)과 부서 테이블(DEPT) 테이블의 공통 컬럼인 DEPTNO의 값이 일치하면서 이름이 SCOTT인 사람의 부서명을 출력해보자.

 

 

 

 

 

- 예시3

- 두 테이블에 동일한 이름의 칼럼을 사용해서 출력하려하면 컴파일러는 인식하지 못한다. 이름이 같은 칼럼이 어느 테이블 소속인지 불분명하기 때문에 애매모호한 상태라는 오류 메시지가 출력된다.

 

 

 

 

 

- 이런 문제를 해결하기 위해선 동일한 이름의 컬럼은 컬럼명 앞에 테이블 명을 명시적으로 기술해줘야한다. 그러면 컬럼이 어느 테이블 소속인지 구분할 수 있게된다.

 

 

 

 

 

- 테이블 이름에 별칭을 붙여 더 쉽게 테이블을 명시적으로 나타낼 수도 있다.

- FROM 절 다음에 테이블 이름을 명시하고 공백을 둔 다음에 별칭을 지정하면 된다.

 

 

 

 

 

 

 

* 셀프 조인(SELF JOIN)

 

- 셀프 조인은 서로 다른 두 테이블이 아닌 동일한 한 테이블을 사용해 조인하는 방법이다.

- SELF JOIN이란 말 그대로 자기 자신과 조인을 맺는 것이다.

- 테이블 한개에서 2가지 이상의 컬럼을 조회할 때, 테이블의 어떤 것들을 조회할 것인지 이름을 지정해줘야 한다.

 

- 예시

- KING의 사원번호와 매니저번호(MGR)가 같은 사람 모두의 이름과 직업을 출력하라.

 

SELECT E1.ENAME, E1.JOB FROM EMP E1,EMP E2 WHERE E1.MGR = E2.EMPNO AND E2.ENAME='KING';

 

- SELECT문에서 같은 테이블 이름을 2개로 나눠서 별칭을 붙여줘서 칼럼들을 구분해줬다.

 

 

 

 

 

 

* 외부 조인(OUTER JOIN)

 

- 일반 조인을 확장한 개념이다.

- 조인 조건에 만족하는 데이터 뿐만 아니라, 어느 한 쪽 테이블에 조인 조건에 명시된 컬럼에 값이 없거나(NULL) 해당 로우가 아예 없더라도 데이터를 모두 출력한다.

 

- 사원 테이블에 직업이 PRESIDENT인 KING만 매니저번호(MGR)가 빠져있다(NULL). KING은 회사의 사장이므로 MGR 컬럼 값이 NULL이다. KING은 MGR 값이 NULL이 므로 위의 SELF JOIN에서 조건을 만족하지 않아 출력에서 제외되었다.

- 조인 조건에 만족하지 못했더라도 해당 행을 출력하고 싶을때 사용하는 것이 외부 조인이다.

 

- 외부조인은 NULL 값이여서 제외된 행을 결과에 포함시킬 수 있으며 "+" 기호를 조인 조건에서 정보가 부족한 컬럼 이름 뒤에 덧붙인다.

- 사원 번호(EMPNO)가 NULL인 사원은 없으므로 manager.empno 뒤에 "+" 기호를 덧붙인다.

 

 

- 예시

SELECT employee.ename,
             manager.ename
FROM emp employee, emp manager
WHERE employee.mgr = manager.empno(+);

 

- 결과

 

 

- SELF JOIN에선 나오지 않았던 NULL 값이 뜨게 된다.

 

 

 

 

 

* 안시 조인(ANSI JOIN)

 

- 안시 조인은 ANSI SQL 문법을 사용한 조인을 말하며, 위에 설명한 모든 조인을 ANSI SQL을 사용해서 변환이 가능하다.

- 기존 문법과 안시 조인의 차이점은 조인 조건이 WHERE절이 아닌 FROM절에 들어간다는 점이다.

 

 

- ANSI INNER JOIN(안시 내부 조인)

- 기존 문법

SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2...
FROM 테이블 A, 테이블 B
WHERE A.컬럼1 = B.컬럼1;

 

- ANSI 문법

SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2...
FROM 테이블 A INNER JOIN 테이블 B
ON (A.컬럼1 = B.컬럼1)
WHERE ... ;

 

 

 

 

 

- USING

- 두 테이블에 각각 조인을 정의한 컬림의 이름이 동일하다면 USING절에서 조인할 컬럼을 지정하여 구문을 더 간단하게 표현할 수 있다.

 

SELECT * FROM table1 inner JOIN table2
USING (공통컬럼);

 

- EMP와 DEPT에 DEPTNO라는 같은 이름의 컬럼이 있기 때문에 다음과 같이 간단한 조인문을 쓸 수 있다.

 

SELECT EMP.ENAME, DEPT.DNAME 
FROM EMP INNER JOIN DEPT
USING (DEPTNO);

 

 

- NATURAL

- 두 테이블에 각각 조인을 정의한 컬럼의 이름이 동일하다면 USING절에서 조인할 컬럼을 지정하여 구문을 더 간단하게 표현할 수 있다.

 

SELECT * FROM table1 NATURAL JOIN table2;

 

- EMP와 DEPT에 DEPTNO라는 같은 이름의 컬럼이 있기 때문에 다음과 같이 간단한 조인문을 쓸 수 있다.

 

SELECT EMP.ENAME, DEPT.DNAME 
FROM EMP NATURAL JOIN DEPT;

 

 

 

 

 

- ANSI OUTER JOIN(안시 외부 조인)

- 기존 문법에서는 기준 테이블과 대상 테이블(데이터가 없는 테이블)에서 대상 테이블쪽 조인 조건에 "(+)"를 붙였지만, 안시 외부 조인은 FROM 절에 명시된 테이블 순서에 따라 먼저 명시된 테이블 기준으로 LEFT 혹은 RIGHT를 붙인다.

 

 

- 기존 문법

SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2...
FROM 테이블 A, 테이블 B
WHERE A.컬럼1 = B.컬럼1(+);

 

 

- ANSI 문법

SELECT A.컬럼1, A.컬럼2, B.컬럼1, B.컬럼2...
FROM 테이블 A 
LEFR(RIGHT) [OUTER] JOIN 테이블 B
ON (A.컬럼1 = B.컬럼1)
WHERE ... ;

 

 

 

 

 

- LEFT OUTER JOIN

- LEFT를 붙이면 왼쪽테이블과 오른쪽테이블의 같은것은 전부 출력하고, 다른것은 왼쪽만 출력한다.

- DEPT01 테이블의 20번 부서와 조인할 부서번호가 DEPT02에는 없지만, 20번 부서도 출력되도록 하기 위해서 DEPT01 테이블이 왼쪽에 존재하기에 LEFT OUTER JOIN을 사용하자.

 

SELECT *
FROM DEPT01 LEFT OUTER JOIN DEPT02
ON DEPT01.DEPTNO = DEPT02.DEPTNO;

 

 

- 위 사진을 보면 오른쪽보다 왼쪽이 더 많이 출력되었다.

 

 

 

 

 

- RIGHT OUTER JOIN

- RIGHT를 붙이면 왼쪽테이블과 오른쪽테이블의 같은것은 전부 출력하고, 다른것은 오른쪽만 출력한다.

- DEPT02 테이블에만 없는 30번 부서까지 출력되도록 하기 위해서  RIGHT OUTER JOIN을 사용하자.

 

SELECT *
FROM DEPT01 RIGHT OUTER JOIN DEPT02
USING(DEPTNO);

 

 

 

- 위 사진을 보면 왼쪽보다 오른쪽이 더 많은 데이터가 출력되었다.

 

 

 

 

 

- FULL OUTER JOIN

- FULL OUTER JOIN은 LEFT OUTER JOIN, RIGHT OUTER JOIN 을 합한 형태라고 볼 수 있다.

- 개발자 사이에선 잘 쓰지 않는 조인 방법이다.

 

SELECT *
FROM DEPT01 FULL OUTER JOIN DEPT02
USING(DEPTNO);

 

 

- 위 사진을 보면 왼쪽테이블과 오른쪽테이블의 데이터가 모두 출력되는것을 알 수 있다.