데이터베이스는 수많은 테이블과 데이터를 가지는데
여러 테이블 사이에서 특정 데이터를 보고 싶을 경우
JOIN 을 이용하여 새로운 테이블을 만들어 결과를 한번에 보려고 한다.
카티션 곱 ( 곱집합 , CARTESIAN PRODUCT )
두개의 테이블이 있을때
SELECT * FROM EMP; -- 12
SELECT * FROM DEPT; -- 4각각의 테이블을 콤마( , )를 이용해 이으면 모든 데이터가 서로 곱해서 출력된다.
SELECT * FROM EMP, DEPT; -- 12*4, 48개의 로우
두 테이블을 곱하고 정렬시 특정 테이블의 컬럼으로 지정해주지 않으면 결과가 나오지 않는다.
SELECT * FROM EMP A, DEPT B ORDER BY DEPTNO;
# A,B 중 어떤 테이블의 DEPTNO 로 정렬할지 모호해서 에러
SELECT * FROM EMP A, DEPT B ORDER BY A.DEPTNO, EMPNO; # 정상적으로 출력
INNER JOIN
간단히 말하면 두 테이블간의 교집합을 출력한다.
그러므로 INNER JOIN 은 두 테이블의 데이터가 모두 존재해야 출력이 된다.
INNER JOIN 에는 등가조인과 안시조인이 있다.
등가 조인 (EQUI JOIN)
카티션 곱에서 콤마로 나열한 테이블 뒤에 WHERE 조건을 사용하여
두 테이블의 컬럼이 같을 경우를 지정해주면 (A.KEY = B.KEY )
해당 데이터가 존재하면서 연결되는 테이블이 출력된다.
앞에 있는 EMP 를 < 메인테이블 > 뒤에 있는 DEPT 를 < 조인테이블 >이라고 한다.
조건인 A.KEY = B.KEY 는 < 조인 칼럼 > 이라고 한다
-- select * from emp , dept;
select * from EMP as A, DEPT as B
where A.deptno = B.deptno;
안시 조인 (ANSI JOIN)
INNER JOIN 은 JOIN 으로 생략해서 사용할수 있다.
JOIN 을 이용하여 메인, 조인테이블을 위치시키고 ON 뒤에는 조건 A.KEY = B.KEY 을 붙이면
등가 조인과 같은 쿼리가 출력된다.
안시 조인은 특정 DB의 특성을 타지 않아 호환이 잘되는 장점이 있다.
SELECT * FROM EMP A
JOIN DEPT B
ON A.DEPTNO=B.DEPTNO;
JOIN할때는DESC로 구조를 확인해
주로PK(PRIMARY KEY) 로A.KEY = B.KEY조건을 준다
DESC STUDENT;
DESC PROFESSOR;
-- 등가 조인을 이용
SELECT
A.NAME AS STU_NAME,
B.NAME AS PROF_NAME
FROM STUDENT AS A,
PROFESSOR AS B
WHERE A.PROFNO = B.PROFNO;
-- 안시 조인을 이용
SELECT
A.NAME AS STU_NAME,
B.NAME AS PROF_NAME
FROM STUDENT AS A
JOIN PROFESSOR AS B
ON A.PROFNO = B.PROFNO;
A.KEY = B.KEY이외의 조건이 있을 경우AND로 조건을 추가한다.
-- 등가 조인 조건 추가
SELECT
A.NAME AS STU_NAME,
B.NAME AS PROF_NAME
FROM STUDENT AS A,
PROFESSOR AS B
WHERE A.PROFNO = B.PROFNO
AND A.DEPTNO1 = 101;
-- 안시 조인 조건 추가
SELECT
A.NAME AS STU_NAME,
B.NAME AS PROF_NAME
FROM STUDENT AS A
JOIN PROFESSOR AS B
ON A.PROFNO = B.PROFNO
WHERE A.DEPTNO1 = 101;
비등가 조인(NON-EQUI JOIN)
테이블A의 데이터가 다음처럼 테이블B의 범위 안에 있을때 비등가 조인을 이용한다.
-- 앞은 생략
FROM CUSTOMER A JOIN GIFT B
ON A.POINT BETWEEN B.G_START AND B.G_END ;
예를들면 고객의 마일리지 포인트로 바꿀 수 있는 상품을 출력하려고 할 경우
SELECT * FROM CUSTOMER;
SELECT * FROM GIFT;
- 고객 이름으로 교환 가능한 상품을 연결시켜서 출력해보자
SELECT
A.GNAME AS CUS_NAME,
A.POINT AS POINT,
B.GNAME AS GIFT_NAME
FROM CUSTOMER AS A JOIN GIFT AS B
ON A.POINT BETWEEN B.G_START AND B.G_END ; -- 범위안에 있는 포인트
- 이번에는 3개의 테이블을 JOIN 해보자
각 테이블을JOIN을 반복하여 연결 시키고 조건은AND로 추가한다
SELECT
S.NAME AS NAME,
A.TOTAL AS TOTAL,
B.GRADE AS GRADE
FROM STUDENT S JOIN SCORE A JOIN HAKJUM B
ON S.STUDNO = A.STUDNO
AND A.TOTAL BETWEEN B.MIN_POINT AND B.MAX_POINT;
OUTER JOIN
OUTER JOIN 은 메인테이블을 기준으로 메인테이블의 데이터가 존재한다면
조인테이블의 데이터가 NULL 이어도 출력이 된다는게 INNER JOIN 과의 차이점이다.
LEFT OUTER JOIN 이나 RIGHT OUTER JOIN 으로 입력하고
OUTER 를 생략해서 LEFT JOIN , RIGHT JOIN 으로 사용해도 같은 결과가 나온다.
(mySQL 은 FULL OUTER JOIN 을 지원하지 않음..! )
LEFT JOIN>> 학생테이블이 메인이 되고 교수테이블의 데이터가 없더라도NULL로 출력
select s.name, p.name
from student s LEFT JOIN professor p
ON s.profno = p.profno; 
RIGHT JOIN>> 교수테이블이 메인이 되고 학생의 이름 데이터가NULL이어도 출력
select s.name, p.name
from student s RIGHT OUTER JOIN professor p
ON s.profno = p.profno; 
SELF JOIN
테이블 하나에서 두 데이터가 연관있는 새로운 테이블을 만들고 싶다면 SELF JOIN 을 이용한다.
예를들면 다음 테이블에서 사원들의 매니저 이름을 출력하고 싶을때
SELECT EMPNO, ENAME, MGR FROM EMP; 
한 테이블에서 두 컬럼을 조인하면 된다
SELECT
E1.EMPNO,
E1.ENAME,
E2.EMPNO,
E2.ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO;
