[ORACLE] JOIN (ORACLE & ANSI)

 

---

오라클과 ANSI JOIN 알아보기

---

 

 

1. JOIN 이란?

---

JOIN 은 두 개의 테이블에 분리되어 있는 정보를 하나의 테이블에 합해서 같이 보고자 하는 명령이다. SELECT 에 사용된다.

 

사원들에 정보를 담은 emp 테이블과 dept 테이블이 있다고 하자. 두 테이블에 공통적으로 deptno 가 들어가 있으며 emp 테이블에는 사원명(ename), dept 테이블에는 부서명(dname)이 있다.

 

만약 우리가 사원명이 'SCOTT'인 사원의 부서명을 알고 싶다면

1. emp 테이블에서 ename 이 SCOTT 인 사원을 찾고

2. 해당 사원의 deptno 를 dept 테이블에서 다시 검색해야 한다.

SELECT dname
FROM dept
WHERE deptno=(SELECT deptno FROM emp WHERE ename='SCOTT');

 

위와 같이 서브 쿼리를 이용해 검색할 수도 있다. 하지만 이 방법은 최초 부서번호(emp 테이블의 deptno)를 조회하기 위해 부서번호를 제외한 다른 정보 조회는 포기해야 한다는 단점이 있다.

 

이럴 때, JOIN 을 이용하면 두 테이블의 데이터를 손실 없이 출력할 수 있는 것이다.

 

 

 

2. CROSS JOIN

---

두개 이상의 테이블이 JOIN 된다.

SELECT * FROM emp, dept;

 

각 테이블의 필드들이 나란히 나열되어 필드는 두 테이블의 필드 개수를 합한 만큼이 생성되고, 한쪽 레코드에 다른 쪽 테이블 레코드들이 한번씩 매칭되어 곱셉의 결과만큼 레코드가 생성되는 사실상 가장 최악의 결과를 얻는 조인 방식이다.

 

 

emp 테이블과 dept 테이블의 필드를 합해 총 12개의 필드가 생성되었으며 레코드가 다른 테이블의 레코드와 매칭되어 52개의(..) 레코드가 생성됐다.

 

 

 

3. EQUI JOIN (컬럼 일치)

---

JOIN 대상이 되는 두 테이블에서 공통적으로 존재하는 필드(컬럼)의 값이 일치하는 행을 연결하여 결과를 생성한다.

 

SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno;

 

emp 테이블과 dept 테이블에서 공통적으로 존재하는 필드인 deptno. 이런 deptno 의 값이 두 테이블에서 같은 행끼리 연결하여 결과를 생성한 것이다.

 

SELECT *
FROM emp a, dept b
WHERE a.deptno = b.deptno;

 

테이블에 변수를 붙여 표현할 수도 있다.

 

 

예제1. rentlist 테이블의 대여일자(rentdate), 대여도서번호(bnum), 대여회원번호(mnum), 할인금액(discount)를 출력하되, booklist의 도서제목(subject)를 도서번호(bnum) 옆에 출력한다면?

SELECT rentdate, bnum, subject, mnum, discount
FROM rentlist a, booklist b
WHERE a.bnum = b.booknum;

-- a 테이블과 b 테이블의 필드명이 각각 한 개도 중복되지 않을 때
SELECT rentdate, bnum, subject, mnum, discount
FROM rentlist a, booklist b
WHERE a.bnum = b.booknum;

-- 두 테이블이 동일한 이름의 필드명이 존재해서 구분해주어야 할 경우
SELECT a. rentdate, a.bnum, b.subject, a.mnum, a.discount
FROM rentlist a, booklist b
WHERE a.bnum = b.booknum;
-- best 방법

-- a 테이블은 모든 필드를, b 테이블은 일부 필드만 표시할 경우
SELECT a.*, b.subject, b.rentprice, b.grade
FROM rentlist a, booklist b
WHERE a.bnum = b.booknum;

 

JOIN 시 두 테이블에 동일한 이름의 필드명이 존재할 경우를 대비해 각각의 필드명에 변수값을 붙여주는 것이 좋다.

 

 

예제2. rentlist의 대여일자(rentdate), 대여도서번호(bnum), 대여회원번호(mnum) 을 출력하되, booklist 테이블의 도서제목(subject), memberlist 테이블의 회원이름(name)을 각각 도서번호와 회원번호 옆에 출력한다. 또한 매출액(rentprice - discount)도 출력한다.

SELECT r.rentdate AS "대여일자", r.bnum AS "대여도서번호", b.subject AS "도서제목",
	r.mnum AS "대여회원번호", m.name AS "회원성명", b.rentprice - r.discount AS "매출액"
FROM rentlist r, booklist b, memberlist m
WHERE r.bnum=b.booknum AND r.mnum=m.membernum;

 

 

 

4. NON-EQUI JOIN (다른 조건)

---

동일한 컬럼이 없어 다른 조건을 사용하는 JOIN 이다. 조건절에 조인 조건을 ' = ' 이외의 연산자를 이용하여 값이 특정 범위내에 있는지 조사한다.

 

salgrade 테이블

 

예제1. emp 테이블의 사원이름(ename)과 급여(sal), salgrade 테이블의 급여등급(grade) 필드를 출력하되, emp 테이블의 급여(sal)가 salgrade 테이블의 급여최소값(losal)보다 크고 급여최대값(hisal)보다 작은 행을 조인한다.

-- 부등호 AND
SELECT a.ename, a.sal, b.grade
FROM emp a, salgrade b
WHERE a.sal > b.losal AND a.sal < b.hisal;

-- BETWEEN AND
SELECT a.ename, a.sal, b.grade
FROM emp a, SALGRADE b
WHERE a.sal BETWEEN b.losal AND b.hisal;

 

 

 

5. OUTER JOIN

---

JOIN 을 하다보면 조건에 만족하지 못해 결과가 누락되는 문제가 발생한다. OUTER JOIN 은 누락된 결과를 전부 출력하는 JOIN으로, 조건 뒤에 (+) 를 붙여주면 된다.

 

예제1. memberlist 테이블에 회원번호(membernum)과 회원이름(name), rentlist 테이블에 대여일자(rentdate)를 출력하되, 두 테이블의 회원번호가 일치하는 경우만 출력한다.

SELECT m.membernum, m.name, r.rentdate
FROM memberlist m, rentlist r
WHERE m.membernum=r.mnum(+);

(+) 가 붙은 OUTER JOIN

(+) 가 없는 EQUI JOIN

 

대여를 한 적 없어 출력되지 않았던 회원이 (+) 로 OUTER JOIN 됨에 따라 함께 출력된 걸 확인할 수 있다.

 

 

 

6. ANSI JOIN

---

미국 국립 표준 협회(American National Standards Institue, ANSI) 에서 제시한 보편적인 SQL 문법이다.

 

ANSI INNER JOIN

각 테이블에서 조인 조건에 일치하는 데이터만 가져온다.

 

INNER JOIN (테이블명) ON (JOIN 조건)

SELECT ename, dname
FROM emp
INNER JOIN dept ON emp.deptno=dept.deptno;

 

INNER JOIN (테이블명) USING(필드명)

서로 비교되는 필드명이 같을 때 사용한다.

SELECT ename, dname
FROM emp
INNER JOIN dept USING(deptno);

 

사원번호(deptno)가 같은 행의 데이터가 합해진다.

 

 

ANSI OUTER JOIN

조인 조건에 일치하는 데이터 및 일치하지 않는 데이터를 모두 가져온다. 조인 조건에 일치하는 데이터가 없다면 NULL 로 가져온다.

어떤 테이블이 중심이 되느냐에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN 로 세분화한다.

 

RIGHT OUTER JOIN (테이블명) ON (JOIN 조건)

SELECT *
FROM rentlist
RIGHT OUTER JOIN booklist ON rentlist.bnum=booklist.booknum;

 

오른쪽에 있는 booklist 테이블이 중심이 된다.

booklist 의 데이터는 모두 나오지만 rentlist 의 조건에 만족하는 데이터가 있으면 해당 데이터를, 없으면 null이 출력된다.