ORACLE DB :: ROW_NUMBER( ) 와 SEQUENCE, INNER JOIN, OUTER JOIN

 

 

ROW_NUMBER( )

 

ROW_NUMBER( ) 함수에 대해 배웠다.

ROW_NUMBER( )는 각 행에 대하여 번호를 붙여준다!

 

SELECT ROW_NUMBER( ) OVER(ORDER BY SAL DESC) "테스트"
     , ENAME "사원명", SAL "급여", HIREDATE "입사일"
FROM EMP;
--==>>
/*
 1	KING	    5000	1981-11-17
 2	FORD	    3000	1981-12-03
 3	SCOTT	    3000	1987-07-13
 4	JONES	    2975	1981-04-02
 5	BLAKE	    2850	1981-05-01
 6	CLARK	    2450	1981-06-09
 7	ALLEN	    1600	1981-02-20
 8	TURNER		1500	1981-09-08
 9	MILLER	    1300	1982-01-23
10	WARD	    1250	1981-02-22
11	MARTIN		1250	1981-09-28
12	ADAMS	    1100	1987-07-13
13	JAMES	    950	1981-12-03
14	SMITH	    800	1980-12-17
*/

 

모양새는 위와 같다.

ROW_NUMBER( ) OVER(ORDER BY 컬럼명 DESC)

이런식으로 정렬의 기준이 되는 컬럼명을 넣어 정렬한 후 그 상태에서 순번을 매긴다.

 

 

ROW_NUMBER( ) 는 행을 삭제했을 경우, 삭제한 행의 번호를 다음 행이 땡겨 받는다.

따라서 게시판의 게시물 번호에 사용하는 경우가 많다!

 

게시물의 관리 목적으로 사용할 때에는 SEQUENCE 나 IDENTITY 를 사용하지만

단순히 게시물을 목록화하여 사용자에게 리스트 형식으로 보여줄 때에는 사용하지 않는 것이 바람직하다.

 

그렇다면 시퀀스는 무엇일까?

 

 

SEQUENCE

 

시퀀스란 자동으로 순차적으로 증가하는 순번을 반환하는 데이터베이스 객체를 말한다.

은행의 번호표라고 생각하면 편하다. 

 

시퀀스 생성 쿼리문은 다음과 같다.

 

CREATE SEQUENCE SEQ_BOARD     				-- 기본적인 시퀀스 생성 구문
START WITH 1                               　 -- 시작값
INCREMENT BY 1                                -- 증가값
NOMAXVALUE                                    -- 최대값
NOCACHE;                                      -- 캐시사용여부

 

이 시퀀스를 테이블의 VALUE 값으로 넣어주려면 다음과 같이 작성하면 된다.

 

INSERT INTO TBL_BOARD VALUES
(SEQ_BOARD.NEXTVAL, '풀숲', '전 풀숲에 있어요', 'java006$', DEFAULT);

 

위 쿼리문에서 SEQ_BOARD.NEXTVAL 부분이 시퀀스 값을 넣어주는 부분이다. 

 

 

 

결론

 

정리하자면, 게시물 관리를 위해 게시물 고유의 번호를 붙여줄 때에는 SEQUENCE 를 사용하고,

게시물을 단순히 목록화 할 때에는 ROW_NUMBER( )를 사용하자.

 

 

 

JOIN

JOIN 의 방법에는 두 가지 버전이 있다!

 

 

먼저,

1. SQL 1992 CODE 를 보자.

 

 

- CROSS JOIN

→ 수학에서 말하는 데카르트 곱 (CARTESIAN PRODUCT)

두 테이블을 모든 경우의 수로 결합하는 방법이다.

 

SELECT *
FROM EMP, DEPT;

 

콤마(,)를 통해 결합한다.

 

 

 

- EQUI JOIN

→ 서로 정확히 일치하는 것들끼리 연결하여 결합시키는 방법이다.

 

SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

 

콤마를 통해 결합하고 WHERE 절을 통해 조건을 부여한다.

 

 

EQUI 조인 시 (+)를 활용하여 결합할 수 있다.

 

SELECT *
FROM TBL_EMP E, TBL_DEPT D
WHERE E.DEPTNO = D.DEPTNO(+);

 

(+) 가 붙어 있는 쪽이 부가적인 테이블이라고 생각하면 편하다.

 

TBL_EMP (E) 테이블의 모든 데이터를 메모리에 먼저 적재한 후

(+)가 있는 쪽 테이블의 데이터를 하나하나 확인하여 결합시키는 형태로 JOIN이 이루어진다.

 

 

 

- NON EQUI JOIN

→ 범위 안에 적합한 것들끼리 연결하여 결합시키는 결합 방법

 

SELECT *
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

 

EMP 테이블의 SAL이 SALGRADE 테이블의 LOSAL과 HISAL 사이에 존재하는 것들끼리 결합시키고 있다.

 

 

 

 

2. SQL 1999 CODE 를 살펴보자!

 

 

이때부터, JOIN 키워드와 ON 키워드가 등장했다.

 

 

- CROSS JOIN

→ 모든 경우의 수로 결합

SELECT *
FROM EMP CROSS JOIN DEPT;

 

 

- INNER JOIN

→ 서로 정확히 일치하는 것끼리만 결합

 

SELECT *
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;

 

1999 CODE 에서는 WHERE 절 대신 ON을 조건절로 사용한다.

또한, INNER JOIN 에서 INNER는 생략이 가능하다.

 

 

- OUTER JOIN

 

OUTER JOIN은 (+)가 붙어있는 EQUI JOIN 과 같다.

 

SELECT *
FROM TBL_EMP E LEFT OUTER JOIN TBL_DEPT D               -- LEFT 테이블이 메인
ON E.DEPTNO = D.DEPTNO;

 

다른 점은 EQUI JOIN에서는 (+)가 붙어있는 테이블이 부가 테이블이었지만,

OUTER JOIN 에서는 메인인 테이블의 방향을 기재한다.

위의 쿼리문에서는 LEFT 테이블이 메인 테이블이다!

 

OUTER JOIN 에서도 OUTER 가 생략 가능하다.

 

 

※ 두 테이블 간 중복되는 컬럼에 대한 소속 테이블을 명시해야 한다!

※ 또한, 소속 테이블을 명시하는 경우 부모 테이블의 컬럼을 참조할 수 있도록 처리해야 한다.

 

SELECT D.DEPTNO "부서번호", DNAME "부서명", ENAME "사원명", JOB "직종명", SAL "급여"
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE JOB IN ('MANAGER', 'CLERK');

 

위의 쿼리문처럼 DEPTNO처럼 중복되는 컬럼에 대하여 소속 테이블을 명시해야 한다.

DEPTNO 테이블이 부모 테이블이므로 D.DEPTNO 라고 명시한다.

 

또한, 두 테이블에 모두 포함되어 있는 중복된 컬럼이 아니더라도 조인하는 과정에서

컬럼의 소속 테이블을 명시해줄 수 있도록 권장한다.(비용 절감을 위해!!!)

 

 

세 개 이상의 테이블 JOIN

 

형식 1. (SQL 1992 CODE)

SELECT 테이블명1.컬럼명, 테이블명2.컬럼명, 테이블명3.컬럼명
FROM 테이블명1, 테이블명2, 테이블명3
WHERE 테이블명1.컬럼명1 = 테이블명2.컬럼명1
  AND 테이블명2.컬럼명2 = 테이블명3.컬럼명2;

 

형식 2. (SQL 1999 CODE)

SELECT 테이블명1.컬럼명, 테이블명2.컬럼명, 테이블명3.컬럼명
FROM 테이블명1 JOIN 테이블명2
ON 테이블명1.컬럼명1 = 테이블명2.컬럼명2
               JOIN 테이블명3
               ON 테이블명2.컬럼명2 = 테이블명3.컬럼명2;