동일한 값이 없는 행도 반환할 때 사용하는 구문
조건에 해당하지 않는 컬럼값도 결과값에 포함시키기 위함
OUTER JOIN은 USING, ON 조건절을 필수적으로 사용해야 한다.
즉, A, B 테이블을 JOIN할 경우, 조건에 맞지 않는 데이터도 표시하고 싶을 때 OUTER JOIN을 사용한다.
LEFT OUTER JOIN과 RIGHT OUTER JOIN은 기준이 되는 테이블이 조인 수행시 무조건 드라이빙 테이블(첫번째로 ACCESS 되는 테이블)이 된다.
LEFT OUTER JOIN과 RIGHT OUTER JOIN은 OUTER를 생략이 가능하다.
조건에 맞지 않는 데이터도 표시하고 싶을 때 OUTER JOIN을 사용한다.
컬럼 옆에 (+) 연산자를 붙여서도 표현가능. (아래에서 더 상사하게 설명)
좌측테이블이 기준이 되어 결과를 생산
즉, TABLE A, B가 있을 때, A LEFT OUTER JOIN B 일 경우
(TABLE A가 기준이 됨) A와 B를 비교해서 B의 JOIN 컬럼에서 같은 값이 있을 때 해당 데이터를 가져오고, B의 JOIN 컬럼에서 같은 값이 없은 경우에는 B TABLE에서 가져오는 컬럼들은 NULL값으로 채운다.
우측테이블이 기준이 되어 결과를 생산
TABLE A와 B가 있을 때 (TABLE B가 기준이 됨), A와 B를 비교해서 A의 JOIN 컬럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, A의 JOIN 컬럼에서 같은 값이 없는 경우에는 A TABLE에서 가져오는 컬럼들은 NULL값으로 채운다.
조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성 즉, TABLE A와 B가 있을 때(TABLE A, B 모두 기준이 됨)
RIGHT OUTER JOIN 과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일
-> A FULL OUTER JOIN B를 할 경우 A 기준 LEFT OUTER JOIN 결과와 B 기준 RIGHT OUTER JOIN 2개의 결과를 합친 결과가 나온다.
아래와 같은 예시 테이블2개를 만들어 놓고 테스트 진행
TAB1
TAB2
1. TAB1과 TAB2를 바꿔가면서 LEFT OUTER JOIN 실행
SELECT * FROM TAB1 A LEFT JOIN TAB2 B
ON A.COL1 = B.COL1;
SELECT * FROM TAB1 A LEFT JOIN TAB2 B
ON A.COL1 = B.COL1;
2. FULL OUTER JOIN 테스트 진행
SELECT * FROM TAB1 A FULL OUTER JOIN TAB2 B
ON A.COL1 = B.COL1;
SELECT * FROM TAB2 B FULL OUTER JOIN TAB1 A
ON B.COL1 = A.COL1;
나오는 행의 숫자는 같지만 기준이 다르기때문에 결과는 다르다
하지만 행의 숫자를 구하는 COUNT(*) 문제일 경우 쉽게 해결 가능.
SQL결과 건수를 알맞게 나열하라
예시를 여러분들도 직접 확인 하실수 있도록 테이블 작성 코드도 함께 올려놓겠습니다.
DROP TABLE TAB1; DROP TABLE TAB2; create table TAB1 ( COL1 VARCHAR2(5), COL2 NUMBER(3,0), KEY1 VARCHAR2(2) PRIMARY KEY ); insert into tab1 values('BBB', 123, 'B'); insert into tab1 values('DDD', 222, 'C'); insert into tab1 values('EEE', 233, 'D'); insert into tab1 values('FFF', 143, 'E'); create table TAB2 ( KEY2 VARCHAR2(2) PRIMARY KEY, COL1 NUMBER(2, 0), COL2 VARCHAR(5) ); insert into tab2 values('A', 10, 'BC'); insert into tab2 values('B', 10, 'CD'); insert into tab2 values('C', 10, 'DE');
위의 코드를 전체 드래그 해서 복사 붙여넣기 하시면 테이블이 자동으로 생성됩니다.
예시 테이블
TAB1
TAB2
1.
SELECT * FROM TAB1 A INNER JOIN TAB2 B
ON (A.KEY1 = B.KEY2);
2.
SELECT * FROM TAB1 A LEFT OUTER JOIN TAB2 B
ON(A.KEY1 = B.KEY2);
3.
SELECT * FROM TAB1 A RIGHT OUTER JOIN TAB2 B
ON(A.KEY1 = B.KEY2);
4.
SELECT * FROM TAB1 A FULL OUTER JOIN TAB2 B
ON(A.KEY1 = B.KEY2);
5.
SELECT * FROM TAB1 A CROSS JOIN TAB2 B;
5번 까지의 선지를 모두 실행해본 결과 입니다.
답: 2, 4, 3, 5, 12
개인적으로 OUTER JOIN이라고 나올때는 괜찮은데 이상하게 (+)로 나오면 뇌정지가 와서
한번 정리해본다..
테이스 테이블
DROP TABLE SQLD_TAB1; DROP TABLE SQLD_TAB2; DROP TABLE SQLD_TAB3; DROP TABLE SQLD_TAB4; CREATE TABLE SQLD_TAB1 ( COL1 NUMBER(2,0) ); CREATE TABLE SQLD_TAB2 ( COL1 NUMBER(2,0) ); CREATE TABLE SQLD_TAB3 ( COL1 NUMBER(2,0) ); CREATE TABLE SQLD_TAB4 ( COL1 NUMBER(2,0) ); INSERT INTO SQLD_TAB1 VALUES(10); INSERT INTO SQLD_TAB1 VALUES(20); INSERT INTO SQLD_TAB1 VALUES(30); INSERT INTO SQLD_TAB1 VALUES(40); INSERT INTO SQLD_TAB2 VALUES(30); INSERT INTO SQLD_TAB2 VALUES(20); INSERT INTO SQLD_TAB2 VALUES(10); INSERT INTO SQLD_TAB2 VALUES(null); INSERT INTO SQLD_TAB3 VALUES(10); INSERT INTO SQLD_TAB3 VALUES(NULL); INSERT INTO SQLD_TAB3 VALUES(30); INSERT INTO SQLD_TAB3 VALUES(50); INSERT INTO SQLD_TAB4 VALUES(60); INSERT INTO SQLD_TAB4 VALUES(50); INSERT INTO SQLD_TAB4 VALUES(20); INSERT INTO SQLD_TAB4 VALUES(10);
위에 내용을 드래그 붙여넣기 하면 되니까 직접해보는 걸 추천!
만들어진 테이블
SQLD_TAB1
SQLD_TAB2
SQLD_TAB3
SQLD_TAB4
해당 테이블을 가지고 먼저 LEFT OUTER JOIN과 (+)가 같게 나오는지 테스트해보자.
먼저 LEFT OUTER JOIN 코드 부터
SELECT * FROM SQLD_TAB1 A LEFT OUTER JOIN SQLD_TAB2 B ON A.COL1 = B.COL1;
다음은 (+) 연산자
SELECT * FROM SQLD_TAB1 A, SQLD_TAB2 B
WHERE A.COL1 = B.COL1(+);
결과는 같게 나온다.
코드가 조금 다른걸 알 수 있는데,
FROM 절에 JOIN을 없애고 WHERE 절에 (+) 연산자를 통해서 OUTER JOIN을 나타내 주는것이다. 참고로 오른쪽에 (+)가 있을때 LEFT OUTER JOIN이다.
이번에는 예제를 통해서 정리를 해보자
위에서 사용한 테이블로 아래의 코드 결과 값을 맞춰보자.
SELECT * FROM SQLD_TAB1 A, SQLD_TAB2 B, SQLD_TAB3 C, SQLD_TAB4 D
WHERE A.COL1 = B.COL1(+)
AND B.COL1 = C.COL1(+)
AND C.COL1 = D.COL1;
참고로 마지막은 = 기호 만 있는 INNER JOIN이다.
해당 코드의 결과값은 얼마 일까
결론 부터 말하자면 답은 1이 출력된다
WHERE 절 부터 순서대로 JOIN 해서
마지막에 남는 갯수를 계산하면 된다.
하나씩 돌려보자.
위의 WHERE 절을 3개로 분해시켜서
돌려본 결과이다.
JOIN을 총 3번 진행했는데
해당 과정을 AND로 걸러내면 마지막에 10이라는 결과만 남게된다.
그래서 결과는 1이 출력된다.