SQL [11] - JOIN(Cross, Equi, non-Equi, Outer)

<JOIN> SQL JOIN이란, 두 개의 다른 테이블의 데이터들을 하나의 테이블로 조회하는 기능이다. 

여러가지 환경에 따라서, 정의과 기능이 달라진다.  

 

1. Cross (카티션 곱) 

두개의 카티션 곱, Cross Join

두 개의 조회된 레코드 수의 곱의 갯수로 레코드가 조회됨

조회되는 레코드의 갯수가 곱의 갯수이므로 사용시 주의해야함

구문 

1. 오라클 방식 : SELECT 컬럼들 또는 *(모든 컬럼) FROM 테이블1, 테이블2; 

2. ANSI (표준) 방식 :  SELECT 컬럼들 또는 *(모든 컬럼) FROM 테이블1 CROSS JOIN 테이블 2;

select * from emp; 

select * from dept; 

오라클 방식 : select * from emp, dept order by empno;

ANSI 방식 : select ename, dname from emp cross join dept;

 

 

4개의 테이블과 14개의 테이블을 CROSS 조인하면 56개의 행을 가진 테이블이 탄생한다.

(실제로 많이 쓰는 조인 타입은 아니다.)

 

2. Equi 등가 조인

두 개의 테이블을 연결해주는 컬럼이 같은 경우 레코드를 선택해주는 레코드 방식
Quiz. 학생 테이블과 학과(department) 테이블을 사용하여 학생이름, 전공학과1코드, 전공학과2코드

 

-- (1) 오라클 방식
select s.name, s.deptno1, d.dname
from student s, department d
where s.deptno1 = d.deptno --조인 컬럼
; 

-- (2) ANSI 방식
select s.name, s.deptno1, d.dname
from student s join department d
on s.deptno1 = d.deptno --조인 컬럼
;

 

-- Quiz. 학생, 교수 테이블을 이용하여 학생 이름과 지도교수 번호 이름을 출력하기
-- 오라클
select s.name, s.profno, p.name
from student s, professor p
where s.profno = p.profno
;

-- ANSI
select s.name, s.profno
from student s join professor p
on s.profno = p.profno
;

 

-- 3개 테이블 조인하기 
-- Quiz. 학생의 이름, 학과이름, 지도교수 이름 출력하기

-- 오라클
select s.name 학생이름, d.dname 학과이름, p.name 지도교수이름
from student s, department d, professor p
where s.deptno1 = d.deptno and s.profno = p.profno
; 

-- ANSI
select s.name 학생이름, d.dname 학과이름, p.name 지도교수이름
from student s join department d 
on s.deptno1 = d.deptno join professor p
on s.profno = p.profno
; 

 

 

3. non-Equi  비등가조인 : 조인 조건을 범위로 지정하기 (나머지는 등가조인이랑 비슷)

 

고객 테이블 : select * from gogak;
상품 테이블 : select * from gift;

Quiz. 고객테이블과 상품테이블에서 고객의 포인트로 받을 수 있는 상품을 출력하기. 고객이름, 포인트, 상품명 조회하기

 

-- 오라클

select g1.gname 고객명, g1.point 포인트, g2.gname 상품명

from gogak g1, gift g2

where g1.point between g2.g_start and g2.g_end

;

 

-- ANSI

select g1.gname 고객명, g1.point 포인트, g2.gname 상품명

from gogak g1 join gift g2

on g1.point between g2.g_start and g2.g_end

;

 

4. Outer

(1) Left Outer Join : 왼쪽 테이블의 모든 정보

 

(2) Right Outer Join : 오른쪽 테이블의 모든 정보

 

(3) Full Outer Join : 양쪽 테이블의 모든 정보 

 

-- (1) left outer join

-- 학생의 이름과 지도 교수의 이름을 조회하기

-- 오라클 방식 
select s.name 학생이름, p.name 지도교수이름
from student s, professor p
where s.profno = p.profno(+)
;
-- ANSI
select s.name 학생이름, p.name 지도교수이름
from student s left outer join professor p
on s.profno = p.profno
;

-- (2) right outer join

-- 학생의 이름과 지도 교수의 이름을 조회하기
-- 오라클 방식 
select s.name 학생이름, p.name 지도교수이름
from student s, professor p
where s.profno (+) = p.profno
;
-- ANSI
select s.name 학생이름, p.name 지도교수이름
from student s right outer join professor p
on s.profno = p.profno
;

-- (3) full outer join

-- 지도 학생이 없는 교수, 지도 교수가 없는 학생 모두 출력하기

-- 오라클 방식 : (+)로 할 수 있는 방법이 없다. 대신 union을 사용해야함 

select s.name 학생이름, p.name 지도교수이름
from student s, professor p
where s.profno = p.profno (+)
union
select s.name 학생이름, p.name 지도교수이름
from student s, professor p
where s.profno (+) = p.profno 
;
-- ANSI
select s.name 학생이름, p.name 지도교수이름
from student s full outer join professor p
on s.profno = p.profno
;