대부분의 조인 질의는 서로 다른 두 테이블의 컬럼을 비교하는 WHERE 절의 조건을 포함한다. 이런 조건을 조인 조건(Join Condition)이라고 한다.

조인을 실행하기 위해서 각 테이블의 로우를 하나씩 가져와 조인 조건이 TRUE로 결정되는 경우에만 결합한다. 조인 조건에 포함되는 컬럼이 반드시 SELECT절에 포함될 필요는 없다.

세 개 이상의 테이블을 조인할 때는 우선 두 개의 테이블과 그 두 테이블의 컬럼에 대응 되는 조인 조건을 이용해서 조인을 한다. 그 후에, 그 두 테이블의 조인 결과의 컬럼과 세 번째 조인할 테이블의 컬럼에 해당하는 조인 조건으로 조인하여 새로운 결과를 만든다.

Tibero는 이러한 과정을 하나의 결과가 나올 때까지 반복한다. 최적화기(Optimizer)는 조인 조건과 테이블에 대한 인덱스와 통계 정보를 사용해 테이블 간의 조인 순서를 정한다. WHERE 절에 조인 조건 이외에 테이블에 하나에 대한 조건도 있을 수 있는데, 이러한 조건은 조인 질의로 반환되는 로우를 더욱 한정한다.

카티션 프로덕트

조인 질의에서 테이블에 대한 조인 조건이 없을 경우 카티션 프로덕트(Cartesian Products)를 반환한다. 카티션 프로덕트는 테이블의 한 로우가 다른 테이블의 모든 로우와 결합되는 것을 말한다.

예를 들어 100개의 로우를 가지는 두 개의 테이블의 카티션 프로덕트는 100 * 100 = 10,000로우이다. 카티션 프로덕트는 이렇게 결과가 너무 많기 때문에 거의 사용되지 않는다. 특별히 카티션 프로덕트가 필요한 경우가 아니라면, 항상 조인 조건을 포함해야 한다. 3개 이상의 테이블을 조인 할 때 그 중 2개의 테이블에 대한 조인 조건이 없었다면, 최적화기는 되도록 카티션 프로덕트가 생기지 않도록 조인순서를 정할 것이다.

동등 조인(Equi Join)은 동등 연산자(=)로 구성된 조인 조건을 포함한 조인이다. 동등 조인은 정해진 컬럼에 대해 같은 값을 가지는 로우를 결합하여 결과로 반환한다.

자체 조인(Self Join)은 하나의 테이블을 사용해서 자신에게 조인하는 것을 의미한다. 동일한 하나의 테이블이 FROM 절에 두 번 사용되기 때문에 별칭을 사용하여 컬럼을 구분한다.

간단한 조인(Simple Join)이라고도 불리는 내부 조인(Inner Join)은 조인 조건을 만족하는 로우만 반환하는 2개 이상의 테이블에 대한 조인이다.

외부 조인(Outer Join)은 일반 조인을 확장한 결과를 출력한다. 외부 조인은 조인 조건을 만족하는 로우뿐만 아니라, 한 테이블의 어떤 로우에 대해 반대편 테이블의 모든 로우가 조인 조건을 만족하지 못하는 경우에도 그 로우를 출력한다.

외부 조인은 왼쪽 외부 조인(left outer join), 오른쪽 외부 조인(right outer join), 완전 외부 조인(full outer join), Partitioned outer join이 있다.

여러 테이블 간의 외부 조인을 수행하는 질의에서 하나의 테이블은 오직 다른 하나의 테이블에 대해서만 NULL을 제공하는 테이블의 역할을 할 수 있다. 따라서, 테이블 A와 B에 대한 조건과 테이블 B와 C에 대한 조건에서 모두 B의 컬럼 쪽에 (+) 연산자를 적용할 수는 없다.

외부 조인 연산자 (+)에는 FROM 절에 외부 조인을 명시할 경우에 다음과 같은 규칙과 제약 조건이 있다.

안티 조인(Anti Join)은 프리디키트의 오른쪽 부분에 해당하는 로우가 없는 왼쪽 부분의 프리디키트에 해당하는 로우를 반환한다. 즉 프리디키트의 오른쪽 부분을 NOT IN의 부질의로 실행했을 때 일치하지 않는 로우를 반환한다.

세미 조인(Semi Join)은 프리디키트의 오른쪽의 다수의 로우에 해당하는 왼쪽 부분의 로우를 중복 없이 처리하는 EXIST 부질의와 같은 로우를 반환한다. 부질의가 WHERE 절의 OR로 연결되어 있으면 세미 조인과 안티 조인으로 변환되지 않는다.

PRIOR

CONNECT BY 절에는 다른 조건식에서는 포함되지 않는 특별한 연산자가 사용되는데, 로우 간의 상하 관계를 나타내기 위한 PRIOR 연산자이다.

PRIOR 연산자가 포함된 조건식은 다음과 같은 형식을 갖는다.

PRIOR expr = expr
expr = PRIOR expr

PRIOR 쪽의 연산식의 결과 값을 갖는 로우가 반대 쪽의 연산식의 결과 값을 갖는 로우의 부모가 된다.

예를 들어 두 개의 컬럼 EMPNO와 MGRNO를 포함하는 테이블 EMP에 대하여 다음의 조건식을 이용하여 계층 질의를 수행한다면, 특정 (부모) 로우의 EMPNO 컬럼 값과 같은 MGRNO 컬럼 값을 갖는 모든 로우는, EMPNO 컬럼 값을 갖는 로우의 자식 로우가 된다.

PRIOR EMPNO = MGRNO

다음의 테이블 EMP2 내의 로우 중에서 EMPNO 컬럼 값이 27인 (부모) 로우에 대하여 EMPNO 컬럼 값이 35, 42인 로우가 자식 로우가 된다. 두 컬럼 모두 MGRNO 컬럼 값이 27이기 때문이다.

     EMPNO ENAME        ADDR                 SALARY      MGRNO
---------- ------------ ---------------- ---------- ----------
        35 John         Houston               30000         27
        54 Alicia       Castle                25000         24
        27 Ramesh       Humble                38000         12
        69 James        Houston               35000         24
        42 Allen        Brooklyn              29000         27
        87 Ward         Humble                28500         35
        24 Martin       Spring                30000         12
        12 Clark        Palo Alto             45000          5

CONNECT BY 절의 조건식은 여러 단순 조건식이 연결된 복합 조건식이 될 수 있다. 하지만, PRIOR 연산자를 포함한 단순 조건식은 반드시 하나만 포함되어야 하며, 0개 또는 2개 이상이 포함된 경우에는 에러를 반환한다. CONNECT BY 절의 조건식은 부질의를 포함할 수 없다.

CONNECT_BY_ROOT

계층 질의에서만 사용되는 또 다른 특수 연산자로 CONNECT_BY_ROOT 연산자가 있다.

CONNECT_BY_ROOT 연산자를 사용하면 데이터베이스는 루트 로우의 데이터를 이용하여 컬럼 값을 반환한다.

다음은 CONNECT_BY_ROOT 연산자를 사용한 예이다.

SQL> SELECT ENAME, CONNECT_BY_ROOT ENAME MANAGER,
            SYS_CONNECT_BY_PATH(ENAME, '-') PATH
     FROM EMP2
     WHERE LEVEL > 1
       CONNECT BY PRIOR EMPNO = MGRNO
       START WITH ENAME = 'Clark';

ENAME           MANAGER         PATH
--------------- --------------- -----------------------
Martin          Clark           -Clark-Martin
James           Clark           -Clark-Martin-James
Alicia          Clark           -Clark-Martin-Alicia
Ramesh          Clark           -Clark-Ramesh
Allen           Clark           -Clark-Ramesh-Allen
JohnClark       Clark           -Ramesh-John
Ward            Clark           -Clark-Ramesh-John-Ward

7 rows selected.

계층 질의는 재귀적(Recursive)으로 실행된다.

먼저 하나의 루트 로우에 대해 모든 자식 로우를 검색한다. 그 다음 각 자식 로우의 자식 로우를 다시 검색한다. 이러한 방법으로 다시는 자식 로우가 발견되지 않을 때까지 계속 진행한다. 만약 무한 루프가 되면 에러를 반환한다.

계층 질의의 결과를 출력하는 순서는 깊이 우선(Depth-First) 순서를 따른다.

다음의 그림은 테이블 EMP2의 계층 질의 결과로 구성된 계층 관계의 한 예이며, 원 안의 값은 EMPNO 컬럼의 값이다. 이때, 로우의 출력 순서는 12, 27, 35, 87, 42, 24, 54, 69이다.

계층 트리의 각 로우는 레벨 값을 갖는다. 루트 로우의 레벨 값은 1이며 자식 로우로 내려가면서 1씩 증가한다. 따라서, 위의 [그림 5.1]에서 EMPNO = 12인 로우는 레벨이 1이며, EMPNO = 27, 24인 로우는 레벨이 2, EMPNO = 35, 42, 54, 69인 로우는 레벨이 3, EMPNO = 87인 로우는 레벨이 4다.

레벨 값은 의사 컬럼인 LEVEL 컬럼의 값으로 출력할 수 있다.

다음은 START WITH 절을 이용하지 않고 계층 질의를 실행하는 SELECT 문의 예이다. 본 예제에서는 모든 로우를 루트 로우로 하여 하부 로우를 출력한다.

SQL> SELECT EMPNO, ENAME, ADDR, MGRNO
     FROM EMP2
       CONNECT BY PRIOR EMPNO = MGRNO;

     EMPNO ENAME        ADDR                  MGRNO
---------- ------------ ---------------- ----------
        12 Clark        Palo Alto                 5
        27 Ramesh       Humble                   12
        35 John         Houston                  27
        87 Ward         Humble                   35
        42 Allen        Brooklyn                 27
        24 Martin       Spring                   12
        54 Alicia       Castle                   24
        69 James        Houston                  24
        27 Ramesh       Humble                   12
        35 John         Houston                  27
        87 Ward         Humble                   35
        42 Allen        Brooklyn                 27
        24 Martin       Spring                   12
        54 Alicia       Castle                   24
        69 James        Houston                  24
        54 Alicia       Castle                   24
        69 James        Houston                  24
        35 John         Houston                  27
        87 Ward         Humble                   35
        42 Allen        Brooklyn                 27
        87 Ward         Humble                   35

21 rows selected.

다음은 START WITH 절을 이용하여 계층 질의를 실행하는 SELECT 문의 예이다. 본 예제에서는 EMPNO = 12인 조건식을 만족하는 하나의 루트 로우에 대해서만 하부 로우를 검색한다.

SQL> SELECT EMPNO, ENAME, ADDR, MGRNO
     FROM EMP2
       START WITH EMPNO = 12
       CONNECT BY PRIOR EMPNO = MGRNO;

     EMPNO ENAME        ADDR                  MGRNO
---------- ------------ ---------------- ----------
        12 Clark        Palo Alto                 5
        27 Ramesh       Humble                   12
        35 John         Houston                  27
        87 Ward         Humble                   35
        42 Allen        Brooklyn                 27
        24 Martin       Spring                   12
        54 Alicia       Castle                   24
        69 James        Houston                  24

8 rows selected.

다음은 WHERE 절을 포함하여 계층 질의를 실행하는 SELECT 문의 예이다. 본 예제에서는 EMPNO = 27인 로우가 WHERE 절때문에 제거되었으나, 그 하부 로우는 모두 그대로 출력된다.

SQL> SELECT EMPNO, ENAME, ADDR, MGRNO
     FROM EMP2
       WHERE ENAME != ’Ramesh’
       START WITH EMPNO = 12
       CONNECT BY PRIOR EMPNO = MGRNO;

     EMPNO ENAME        ADDR                  MGRNO
---------- ------------ ---------------- ----------
        12 Clark        Palo Alto                 5
        35 John         Houston                  27
        87 Ward         Humble                   35
        42 Allen        Brooklyn                 27
        24 Martin       Spring                   12
        54 Alicia       Castle                   24
        69 James        Houston                  24

7 rows selected.

다음은 LEVEL 컬럼을 포함하여 계층 질의를 실행하는 SELECT 문의 예이다.

SQL> SELECT EMPNO, ENAME, ADDR, MGRNO, LEVEL
     FROM EMP2
       START WITH EMPNO = 12
       CONNECT BY PRIOR EMPNO = MGRNO;

     EMPNO ENAME        ADDR                  MGRNO      LEVEL
---------- ------------ ---------------- ---------- ----------
        12 Clark        Palo Alto                 5          1
        27 Ramesh       Humble                   12          2
        35 John         Houston                  27          3
        87 Ward         Humble                   35          4
        42 Allen        Brooklyn                 27          3
        24 Martin       Spring                   12          2
        54 Alicia       Castle                   24          3
        69 James        Houston                  24          3

8 rows selected.