아우터 조인을 조금 더 실습해보기 위해 표를 개인적으로 제작하였다. 표를 제작하는 방법은 크게 두 가지가 있는데, 하나는 툴을 이용한 방법, 다른 하나는 DDL을 이용하는 방법이다.
1. 표를 이용하는 방법.
표 메뉴를 띄우고 새 테이블을 연다.
테이블의 이름, 들어갈 데이터의 이름과 유형을 결정한다.
이 때 반드시 들어가야 하는 데이터이면 널이 아님, 에 체크한다.
다음 고급을 들어간다.
데이터베이스에는 데이터의 무결성을 위해 개개의 데이터가 가지고 있는 고유한 이름을 설정할 수 있다.
PK가 그 고유한 데이터인데, 이는 PK의 빈 칸을 클릭하면 된다. 이후 PK내부의 데이터는 동일한 값이 들어갈 수 없다.
조인을 이용하기 위해서는 테이블 간 참조가 필요한데, 이 참조를 하는 것이 foriegn key, 외래 키이다.
이 키는 테이블의 참조 값에서 다른 테이블의 private key에 접근하여 참조한다.
편집 -> 제약 조건의 +버튼을 누르면 "새 외래키 생성"이 있다. 새 외래키를 생성하고, 내가 참조할 테이블, 그리고 그 참조할 테이블의 private key와 연결한다. 그리고 로컬 열에서 현재 테이블의 데이터 중에서 참조할 데이터를 찾아 연결한다. 이렇게 연결하면 서로가 서로의 테이블에 참조할 수 있는 권한이 생긴다.
이렇게 기본 키와 외래 키의 제약 조건 확인은
다음 위치에서 확인이 가능하다.
물론 이렇게 툴로 만드는 방법이 있지만, DDL로 테이블을 만들 수도 있다.
CREATE TABLE "REPLY" (
"ID" NUMBER,
"CONTENT" VARCHAR2(200 BYTE) NOT NULL ENABLE,
"BOARDID" NUMBER,
"USERID" NUMBER,
CONSTRAINT "REPLY_PK" PRIMARY KEY ("ID")
CONSTRAINT "REPLY_FK_USER_ID" FOREIGN KEY ("USERID") REFERENCES "BOARD" ("ID")
CONSTRAINT "REPLY_FK_BOARD_ID" FOREIGN KEY ("BOARDID") REFERENCES "USERS" ("ID")
);CREATE TABLE로 먼저 테이블의 이름을 설정한다.
이후, 테이블의 이름과 데이터 타입을 설정한다. ex "ID"는 열의 이름, NUMBER은 데이터 타입이다.
열과 데이터 타입을 모두 설정했으면, 아래에서 기본 키와 외래 키를 설정한다.
기본 키는 단순히 열의 이름만 작성하면 되지만, 외래 키의 경우 어떤 테이블의 어떤 열을 참조하는지도 함께 작성한다.
다음, 시퀀스는 ID같이 증감폭이 일정한 숫자를 만들때 사용한다.
시퀀스는 다음과 같이 만든다.
CREATE SEQUENCE users_seq
INCREMENT BY 1
START WITH 1;
이렇게 테이블의 틀을 제작한다.
테이블의 틀이 만들어졌으니 테이블에 데이터를 집어넣어야 하는데, 이런 명령어를 DML이라고 한다.
INSERT INTO USERS(ID, USERNAME, EMAIL)
VALUES(USERS_SEQ.nextval, 'ssar', 'ssar@nate.com');INSERT를 통해 데이터의 값을 집어넣는다.
앞서 만든 시퀀스는 USERS_SEQ.nextval 로 이용한다.
데이터를 삭제할 때는 delete를 이용한다.
이 데이터들을 디스크에 완전히 저장하기 위해서는 commit를 이용해야한다. 그래야 데이터가 완전한 형태로 저장이 된다.
이 방법을 통해 세개의 테이블을 작성했다. users, board, reply
이 테이블을 서로 참조하는 코드는 다음과 같다.
-inner join
SELECT *
FROM USERS U , BOARD B
WHERE U.ID = B.USERID;
-outer join
SELECT *
FROM USERS U , BOARD B
WHERE U.ID = B.USERID(+);
다음과 같이 아우터 조인을 이용할 경우, 우리가 이너 조인을 통해 찾아낼 수 없었던 데이터를 찾아낼 수 있다.
이 3번 유저만 나오게 할 수 있는 방법이 있다.
1) null을 이용하기
SELECT *
FROM USERS U , REPLY R
WHERE U.ID = R.USERID(+) AND R.ID IS NULL;
다음과 같이 and 조건을 이용해 null값을 찾아내게 하는 방법이다.
2) 집합 연산자 이용하기
집합 연산자 -를 통해 outer에서 inner을 빼버리는 방식도 있다.
SELECT *
FROM USERS U , REPLY R
WHERE U.ID = R.USERID
minus
SELECT *
FROM USERS U , REPLY R
WHERE U.ID = R.USERID(+);
아우터 조인을 이용하여 좋아요와 덧글 갯수를 찾는 코드를 만들어보자.
1) 서브쿼리를 이용한 방법.
SELECT ID, TITLE,
(SELECT USERNAME FROM USERS WHERE B.USERID=USERS.ID) "USERNAME",
(SELECT COUNT(*) FROM REPLY WHERE BOARDID = B.ID) "댓글 수"
FROM BOARD B;
첫번째 쿼리에서는 board에 있는 userid를 이용해 서로 참조되어있던 id와 userid를 where 절을 통해 일치하도록 값을 설정했고, 두번째 쿼리에서는 보드에 참조되어 있던 id의 갯수를 카운트해서 값을 도출하는 서브쿼리이다.
2) outer join을 이용한 방법.
SELECT B.ID, B.TITLE, U.USERNAME, COUNT(R.ID) "댓글 수"
FROM BOARD B, REPLY R, USERS U
WHERE B.ID = R.BOARDID(+) AND B.USERID=U.ID
GROUP BY B.ID, B.TITLE, U.USERNAME
ORDER BY B.ID;세 개의 조인을 설정하여 두 개의 조건을 배열했다(u에 조건을 걸지 않으면 카티션 곱이 적용되어 덧글이 6개로 나옴!)
이 때 count 값이 그룹함수이기 때문에, 남은 데이터에 그룹을 걸어주는 것을 잊지 않도록 한다.
결과는 동일하게 적용된다.
