[Oracle] OUTER JOIN, SELF JOIN

OUTER JOIN

EQUI JOIN의 경우 한쪽 칼럼중 하나라도 NULL이 있으면 조인 결과로 출력이 안된다.

그래서 한쪽 칼럼이 NULL이 있어도 출력할 필요가 있을 때 OUTER JOIN을 사용한다.

 

OUTER JOIN with (+) 기호

WHERE 절에서 OUTER JOIN의 연산자인 (+) 기호를 사용한다.

조인 조건문에서 NULL 이 존재하는 테이블의 칼럼에 (+) 기호를 사용한다.

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column(+) = table.column 또는 table1.column = table2.colum(+)

제약 사항

• OUTER JOIN에서 IN 연산자는 사용 불가
• 다른 조건과 OR 연산자로 결합 불가

 

EX1) 학생 테이블과 교수 테이블을 조인하여 이름(name), 학년(grade), 지도 교수의 이름(name), 직급(position)을 출력.

(단, 지도 교수가 배정되어 있지 않은 학생 이름도 함께 출력)

SELECT s.name, s.grade, p.name, p.position
FROM student s, professor p
WHERE s.profno=p.profno(+);

=> student 테이블에 학생의 지도교수 profno가 NULL인 학생도 있기에 p.profno에 (+)를 붙여준다.

실행 결과

 

EX2) 학생테이블과 교수 테이블을 조인하여 이름, 학년, 지도 교수 이름, 직급을 출력

(단, 지도 학생을 배정받지 않은 교수 이름도 함께 출력)

SELECT s.name, s.grade, p.name, p.position
FROM student s, professor p
WHERE s.profno(+)=p.profno ORDER BY s.name;

실행 결과

=> 학생의 지도교수(profno)에 없는 교수 또한 출력이 나온 게 보인다.

 

EX1의 예제에서 OUTER JOIN을 쓰고 안쓰고 차이를 자세히 알아보자.

학생 테이블의 name과 profno(지도교수번호)를 출력해봤다.

지도 교수가 없는 학생이 있다.

아래의 쿼리문처럼 학생의 지도교수(profno)와 교수의 코드(profno)를 그냥 조인하면

SELECT s.name, s.grade, p.name, p.position
FROM student s, professor p
WHERE s.profno=p.profno;

 

 

그냥 조인 결과

학생의 지도교수(profno)가 NULL인 6명을 제외하고 10명만 출력된다.

하지만 아래처럼 조건에 OUTER JOIN을 해준다면

WHERE s.profno=p.profno(+);

지도 교수(profno)가 없는 학생의 정보 또한 출력된다. 

 

OUTER JOIN with ~ON

SELECT table1.column, table2.column
FROM table1 [RIGHT | LEFT | FULL] OUTER JOIN table2
ON table1.column = table2.column

 

LEFT OUTER JOIN?

FROM절의 왼쪽에 위치한 테이블에 NULL 값이 있을때 사용

WHERE절의 오른편에 (+) 기호를 추가한 것과 동일한 효과

 

EX) 학생과 지도 교수 정보를 출력한다. (단, 지도 교수가 배정되지 않은 학생의 정보도 출력)

SELECT s.studno, s.name, s.profno, p.name
FROM student s LEFT OUTER JOIN professor p
ON s.profno=p.profno;

SELECT s.studno, s.name, s.profno, p.name
FROM student s, professor p
WHERE s.profno=p.profno(+);

-- 두 쿼리는 동일한 결과이다.

 

RIGHT OUTER JOIN?

FROM절의 오른쪽에 위치한 테이블에 NULL 값이 있을때 사용

WHERE절의 왼쪽편에 (+) 기호를 추가한 것과 동일한 효과

 

EX) 교수와 지도 학생 정보를 모두 출력한다. (단, 지도학생이 배정되지 않은 교수의 정보도 출력)

SELECt studno, s.name, s.profno, p.name
FROM student s
RIGHT OUTER JOIN professor p
ON s.profno=p.profno;

SELECT studno, s.name, s.profno, p.name
FROM student s, professor p
WHERE s.profno(+)=p.profno;

 

FULL OUTER JOIN?

LEFT OUTER JOIN과 RIGHT OUTER JOIN을 동시에 실행한 결과를 출력

 

EX) 교수와 학생 정보 모두 출력. (단, 지도 교수가 없는 학생과 지도 학생이 없는 교수 모두 출력)

SELECT studno, s.name, s.profno, p.name
FROM student s
FULL OUTER JOIN professor p ON s.profno=p.profno;

 

이거 (+) 기호를 양쪽에다 붙이면 되는 거 아니야? 싶지만

안된다. FULL OUTER JOIN ON 을 써야 한다.

 

SELF JOIN

하나의 테이블 내에 있는 칼럼끼리 연결하는 조인

한 테이블에 두 개의 칼럼을 EQUI JOIN으로 연결함.

WHERE 절 또는 JOIN ~ON절로  SELF JOIN이 가능하다.

 

JOIN ~ ON

SELECT table1.column table2.column
FROM table1 JOIN table2
ON table1.column = table2.column

 

EX) 부서 테이블에서 SELF JOIN을 이용하여 부서 이름과 (자신의)상위 부서의 이름을 출력

-- WHERE을 이용한 SELF JOIN
SELECT dept.dname || '의 소속은 ' || org.dname
FROM department dept, department org
WHERE dept.college = org.deptno;

-- JOIN-ON을 사용한 SELF JOIN
SELECT dept.dname || '의 소속은 ' || org.dname
FROM department dept JOIN department org
ON dept.college = org.deptno;

deptno는 자신의 부서 코드이고

college는 자신의 상위 부서이다.