[Oracle] 조인(join)

들어가기 전에

이 포스팅은 오라클로 배우는 데이터베이스 입문(이지훈 저)을 읽으면서 기억하고자 하는 내용 및 추가적으로 알아본 내용을 담았습니다. 책 전체 내용을 보시고 싶으신 분은 책을 구매하시어 읽어보시길 바랍니다.

조인(join)

조인과 집합 연산자 차이

조인과 집합 연산자

조인은 두 개 이상의 테이블을 연결하여 하나의 테이블처럼 출력할 때 사용합니다. 앞서 배웠던 집합 연산자 역시, 두 개 이상의 테이블을 연결해서 출력한다는 점에서 조인과 집합 연산자는 비슷합니다.

다만, 집합 연산자를 사용한 결과는 두 개 이상의 SELECT 문을 통해 결과 값을 세로로 연결한 것이라면 조인은 두 개 이상의 테이블 데이터를 가로로 연결했다는 점이 서로 다릅니다.

조인들을 벤 다이어그램으로 간단히 보기

데카르트 곱(카세디안 곱, Cartesian product)

각 집합을 이루는 모든 원소의 순서쌍을 데카르트 곱이라고 합니다. 여러 테이블을 연결할 때에, 제대로 조건을 걸지 않으면 서로 연관되지 않은 레코드가 연결되어 불필요한 데이터까지 출력되게 됩니다.

불필요한 데이터가 모두 출력된 데카르트 곱 현상(크로스 조인 = 교차 조인)

• 위 출력 예시를 보면 EMPNO가 7369인 SMITH의 DEPTNO는 20인데, 연결된 DEPT 테이블의 DEPTNO가 20이 아닌 항목들도 존재합니다.
• 실제로 사원별로 각각 하나의 레코드만 나오면 되는데 불필요한 레코드들까지 모두 조회되었습니다.

등가 조인(equi join) = 내부 조인(inner join) = 단순 조인(simple join)

등가 조인은 테이블을 연결한 후에 출력 행을 각 테이블의 특정 열에 일치한 데이터를 기준으로 선정하는 방식입니다. 일반적으로 가장 많이 사용되는 조인 방식입니다. 등가 조인은 내부 조인이라고도 불리며, 내부 조인이란 조인 조건에 해당하는 데이터가 존재할 경우에만 출력하는 조인을 의미합니다.

등가 조인 예시

조인 테이블 개수와 조건식 개수의 관계

조인 조건을 제대로 지정하지 않으면 데카르트 곱 때문에 정확히 연결되지 않아 불필요한 데이터까지 출력될 수 있습니다. 기본적으로, 데카르트 곱 현상이 일어나지 않기 위해서 필요한 조건식의 최소 개수는 조인 테이블 개수에서 하나를 뺀 값입니다.

예를 들어, A와 B, C 테이블을 조인할 경우 최소 2개의 조건식이 필요하며 이는 A와 B를 연결해줄 열 하나와 A와 B가 연결된 상태에서 C를 연결해 줄 열 하나가 더 필요합니다.

비등가 조인(non-equi join)

비등가 조인은 등가 조인 방식 외의 방식을 의미합니다.

예를 들어, 아래와 같이 사원 정보가 들어있는 EMP 테이블과 급여 등급이 들어있는 SALGRADE 테이블이 있다면, 이 두 테이블은 사용하는 열의 일치 여부를 기준으로 조인(등가 조인)할 수 없습니다.

EMP 테이블과 SALGRADE 테이블의 비등가 조인 사용 예시

• 사원의 급여는 SALGRADE 테이블의 특정 등급의 LOW ~ HIGH 사이에 들어오는지 여부를 이용해 결합해야 합니다.

자체 조인 = 내부 조인(inner join)

자체 조인은 하나의 테이블을 여러 개의 테이블처럼 활용하는 조인 방식을 의미합니다. 자체 조인은 하나의 테이블을 여러 번 연결해야 할 때 사용하면 되며, 각 테이블의 별칭을 서로 다르게 지정하여 사용하면 됩니다. 자체 조인 역시, 조인 조건에 해당하는 데이터가 존재할 경우에만 출력하기 때문에 내부 조인이라고도 합니다.

예를 들어, 사원의 사번과 이름 그리고 사원의 상사의 사번과 이름을 하나의 레코드로 출력하고자 한다고 가정합니다. 이 때, 사원 테이블인 EMP 테이블은 아래와 같은 구조이기 때문에 EMP 테이블을 조인해야 합니다.

자체 조인 사용 예시

• WHERE 절에서 E1.MGR = E2.EMPNO이라는 조건을 사용하였기 때문에 E1.MGR이 NULL인 경우(E1.ENAME이 KING인 경우) 결과에서 제외되었습니다(NULL은 동등 비교가 불가능함).
  • 이렇게 한쪽의 데이터가 NULL인 경우에도 출력을 하고자 한다면 외부 조인을 해야 합니다.

외부 조인(outer join)

앞서 자체 조인의 예시에서 사원 이름이 KING인 경우 상사가 존재하지 않아(NULL) 결과에 출력되지 않았습니다. 이러한 레코드 역시 출력하기 위해서는, 두 테이블 간 조인 수행에서 조인 기준 열의 어느 한쪽이 NULL이어도 강제로 출력해야 하며, 이러한 조인 방식을 외부 조인이라고 합니다. 외부 조인은 조인 기준 열의 NULL을 처리하는 것을 목적으로 자주 사용되는 조인 방식입니다.

외부 조인은 좌우를 따로 나누어 지정하며, WHERE 절에 조인 기준 열 중 한쪽에 (+) 기호를 붙여 어느쪽 외부 조인인지 지정합니다.

왼쪽 외부 조인(left outer join)	WHERE TABLE1.COL1 = TABLE2.COL1(+)
오른쪽 외부 조인(right outer join)	WHERE TABLE1.COL1(+) = TABLE2.COL1

왼쪽 외부 조인(왼쪽)과 오른쪽 외부 조인(오른쪽)

• 왼쪽 외부 조인(left outer join): 왼쪽 열을 기준으로 오른쪽 열의 데이터 존재 여부에 상관 없이 출력
  • 왼쪽 열을 기준으로 오른쪽 열 데이터 유무 상관없이 출력되는 것이므로, E1의 레코드로 KING이 들어왔을 때, KING의 상관이 존재하지 않아도 9열에 출력되었습니다.
• 오른쪽 외부 조인(right outer join): 오른쪽 열을 기준으로 왼쪽 열의 데이터 존재 여부에 상관 없이 출력
  • 오른쪽 열을 기준으로 왼쪽 열 데이터 유무 상관없이 출력하는 것이므로, 상관으로서 존재하지 않는 사원들에 대해 14행부터 출력되었습니다.

앞서 배운 조인들과 기능은 같지만, SQL-99 표준 문법이 나옴에 따라 문법을 다르게 사용할 수 있는 경우가 있어 그러한 조인들에 대해 알아보도록 하겠습니다.

NATURAL JOIN(등가 조인 대신 사용 가능)

FROM TABLE1 NATURAL JOIN TABLE2

NATURAL JOIN은 위에서 배운 등가 조인을 대신해 사용할 수 있는 조인 방식으로, 조인 대상이 되는 두 테이블의 이름과 자료형이 같은 열을 찾은 후 그 열을 기준으로 등가 조인을 해 줍니다.

NATURAL JOIN 사용 예시

• 앞서 보았던 등가 조인은 WHERE 절에 조건을 작성하여 조인을 하였다면, NATURAL JOIN은 FROM 절에 조인 키워드를 넣어 사용합니다.
• EMP 테이블과 DEPT 테이블 모두 동일한 자료형와 이름을 가진 DEPTNO 컬럼이 있어 해당 컬럼을 기준으로 등가 조인이 이루어집니다.
• NATURAL JOIN에서는, 기존 등가 조인과 다르게 기준이 되는 DEPTNO 컬럼에 대해 테이블 이름을 붙이면 안됩니다.

JOIN ~ USING(등가 조인 대신 사용 가능)

FROM TABLE1 JOIN TABLE2 USING ([조인에 사용할 기준열])

NATURAL JOIN과 마찬가지로 JOIN ~ USING 키워드를 사용한 조인 역시 등가 조인을 대신하는 조인 방식입니다.

JOIN ~ USING 키워드를 이용한 조인

• NATURAL JOIN과 마찬가지로 JOIN ~ USING 키워드 조인 역시 FROM 절에 조인 키워드를 넣어 사용합니다.
• JOIN ~ USING 키워드를 이용한 조인은 NATURAL JOIN과 달리, 조인에 사용할 기준열을 명시해주어야 합니다.
• JOIN ~ USING 키워드를 이용한 조인은 NATURAL JOIN과 마찬가지로 기준이 되는 컬럼에 대해 테이블 이름을 붙이면 안됩니다.

JOIN ~ ON

FROM TABLE1 JOIN TABLE2 ON ([조인 조건식])

JOIN ~ ON 키워드를 통한 조인은 가장 범용성 있는 조인으로, 기존에 WHERE 절에 작성하였던 조인용 조건을 FROM 절의 ON 키워드에 명시합니다.

JOIN ~ ON 키워드를 이용한 조인

• JOIN ~ ON 키워드를 통한 조인은 기존에 WHERE 절에 작성하였던 조인용 조건을 FROM 절의 ON 키워드 옆에 작성하기 때문에, 기준이 되는 컬럼을 명시할 때에 어떤 테이블의 컬럼인지 작성해주어야 합니다.
  • DEPTNO 컬럼의 경우 EMP 테이블과 DEPT 테이블 모두에 존재하여 어느 테이블의 컬럼인지 명시해주어야 합니다.

OUTER JOIN

OUTER JOIN 키워드는 외부 조인에 사용합니다. 기존 외부 조인은 WHERE 절에 조인 조건을 작성했지만, OUTER JOIN 키워드는 FROM 절에 외부 조인을 선언합니다.

왼쪽 외부 조인(left outer join)	기존	WHERE TABLE1.COL1 = TABLE2.COL1(+)
	SQL-99	FROM TABLE1 LEFT OUTER JOIN TABLE2 ON ([조인 조건식])
오른쪽 외부 조인 (right outer join)	기존	WHERE TABLE1.COL1(+) = TABLE2.COL1
	SQL-99	FROM TABLE1 RIGHT OUTER JOIN TABLE2 ON ([조인 조건식])
전체 외부 조인 (full outer join)	기존	기본 문법은 없으며, UNION 집합 연산자로 사용 가능
	SQL-99	FROM TABLE1 FULL OUTER JOIN TABLE2 ON ([조인 조건식])

left outer join(왼쪽), right outer join(중간), full outer join(오른쪽)

SQL-99 방식에서 세 개 이상의 테이블에 조인 작성하는 방법(FROM 절에 세 개 이상의 테이블 조인 방법)

FROM TABLE1 JOIN TABLE2 ON ([조건식1])
    JOIN TABLE3 ON ([조건식2])

• FROM 절에 세 개 이상의 테이블 조인은 위와 같이 앞선 조인 구문 옆으로 JOIN을 바로 적어서 다음 조인 구문을 작성하면 됩니다.

참고 자료

• 오라클로 배우는 데이터베이스 입문(이지훈 저)