DAY08. SQL JOIN (Join의 종류, Cross Join, Inner Join, Outer Join)

JOIN의 종류 (물리적 조인)

Nested Loops Join

Sort Merge Join

Hash Match Join

 

 

 

FOREIGN KEY조인

테이블에 없는 값을 다른 테이블을 참조(연결)하여 출력

<조인 절차>
1. 기본키가 포함된 테이블(master table) 생성
2. 기본키가 포함된 테이블에 레코드 삽입
3. 외래키가 포함된 테이블(transaction table) = 거래테이블 생성
4. 외래키가 포함된 테이블에 레코드 삽입
* 조인 테이블 삭제 : 역순. 외래키 포함 테이블 삭제->기본키 포함 테이블 삭제
* 강제 테이블 삭제 : DROP TABLE 테이블명 CASCADE CONSTRAINT;

 

1. 기본키가 포함된 테이블 생성

CREATE TABLE goods(
gcode NUMBER(2) PRIMARY KEY, -- 주키. 상품코드
gname VARCHAR(20), -- 상품명
price INT -- 단가
);

2. 기본키가 포함된 테이블에 레코드 삽입

INSERT INTO goods VALUES (10, '사과', 5000);
INSERT INTO goods VALUES (20, '복숭아', 8000);
INSERT INTO goods VALUES (30, '포도', 3000);

3. 외래키가 포함된 테이블 생성

CREATE TABLE sale(
gcode NUMBER(2) PRIMARY KEY,
sale_date DATE,
su NUMBER(3),
FOREIGN KEY(gcode) REFERENCES goods(gcode)-- 외래키
);

4. 외래키가 포함된 테이블에 레코드 삽입

INSERT INTO sale VALUES(10, sysdate, 5); 
INSERT INTO sale VALUES(20, sysdate, 10);
INSERT INTO sale VALUES(30, sysdate, 8);

INSERT INTO sale VALUES(40, sysdate, 8); -- error. WHY? 참조 무결성 제약조건 위배. 마스터 테이블에 없는 gcode 추가 불가능

DB반영

COMMIT;

 

물리적 join의 별칭 지정

SELECT g.gcode, g.gname, s.su, g.price -- (출처(별칭).칼럼명)
FROM goods g, sale s -- 한 칸 사이띄기로 테이블 별칭 지정 가능. SELECT, WHERE절에서는 AS필요
WHERE g.gcode = s.gcode -- 조인조건(필수). 두 테이블을 연결할 때, 어떤 칼럼을 가지고 연결할 것인가 
AND s.su >= 8;-- 일반조건

 

 

 

 

 

JOIN의 종류 (논리적 조인)

Cross Join 조건 없이 테이블 연결

Inner Join 조인 조건에 만족하는 행만 대상으로 조인

Outer Join 조인 조건에 만족하지 않는 행도 포함하는 조인

Self Join

 

 

 

Cross Join

= 카티전 조인

특별한 키워드 없이 SELECT 문의 FROM 절에 테이블을 콤마로 연결

SELECT * FROM EMP, DEPT;

* Cross Join의 결과는 아무런 의미를 갖지 못한다.

*조인 결과가 의미를 갖으려면 조인할 때 조건을 지정해야 한다.

 

 

 

 

 

Inner Join

Inner JOIN은 가장 많이 사용하는 조인 방법

조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결하여 결과를 생성

조건이 지정되지 않으면 cross join과 같은 연산을 수행

 

SELECT *
ANSI Inner Join -- 표준FROM emp, dept
WHERE emp.deptno=dept.deptno; -- (테이블 출처.공통칼럼)

*두 칼럼에 자료가 있는 경우에만 조인 -> 14개의 행, 11개의 열

SELECT *
FROM emp, dept
WHERE emp.deptno=dept.deptno -- 조인조건
AND ename='SCOTT'; -- 일반조건

칼럼명의 모호성 해결 : 칼럼명 표기, 별칭 표기

SELECT ename, dname, deptno -- 유일 칼럼은 출처표기 생략 가능
FROM emp, dept
WHERE emp.deptno = dept.deptno -- error. WHY? SELECT절과 WHERE절에 칼럼명 표기가 없음
AND ename='SCOTT';

SELECT emp.ename, dept.dname, emp.deptno -- 칼럼명 표기
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND ename='SCOTT';

SELECT e.ename, d.dname, e.deptno
FROM emp e, dept d -- 별칭 설정
WHERE e.deptno=d.deptno
AND ename='SCOTT';

SELECT ename, dname, emp.deptno -- deptno는 출처 밝혀야함 
FROM emp INNER JOIN dept
ON emp.deptno = dept.deptno -- 조인 조건
WHERE ename = 'SCOTT'; -- 일반 조건

[예제] 뉴욕에서 근무하는 사원의 이름과 급여를 출력하시오.(EMP, DEPT 이용)
sub query로도 풀이 가능

SELECT ename, sal
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND loc = 'NEW YORK';

[예제] ACCOUNTING 부서 소속 사원의 이름, 입사일, 근무지역을 출력하시오.

SELECT ename, hiredate, loc, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND dname = 'ACCOUNTING';

[예제] 직급이 MANAGER인 사원의 이름, 부서명을 출력하시오

SELECT ename, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND job = 'MANAGER';

[예제] 학생테이블(student)과 교수테이블(professor) 조인하기
교수번호(profno) 칼럼을 기준 조인하여 학생명, 학과, 교수명, 교수번호 칼럼을 조회하시오.

SELECT *
FROM student, professor
WHERE student.profno = professor.profno; -- 15개 값 도출 : 교수번호가 없는 학생이 있음

SELECT s.name 학생명, s.deptno1 학과, p.name 교수명, p.profno 교수번호
FROM student s, professor p
WHERE s.profno = p.profno;

[예제] 윗 문제의 결과에서 101 학과만 검색되도록 하시오.

SELECT s.name 학생명, s.deptno1 학과, p.name 교수명, p.profno 교수번호
FROM student s, professor p
WHERE s.profno = p.profno
AND s.deptno1 = 101;

 

 

 

Self join

SELECT e1.*, e2.*
FROM emp e1, emp e2; -- 출력값 모양 : 196행 16열 (행끼리 곱해지고, 열은 더해짐). 자료 부족한 칼럼 값은 NULL

 

 

 

Outer Join : 자료가 부족한 칼럼에 추가, 기준 테이블 없음

= Left outer join (왼쪽 테이블을 기준으로 조인)

SELECT e1.ename 사원명, e2.ename 상사명
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno (+); -- 상사가 없는 대표까지 모두 출력

[예제] EMP 테이블과 DEPT 테이블을 조인하여 사원이름과 부서번호와 부서명을 출력하시오.
DEPT 테이블의 40번 부서와 조인할 EMP 테이블의 부서번호가 없지만, 40번 부서의 이름도 출력하시오

SELECT ename, d1.deptno, dname
FROM emp e1, dept d1
WHERE d1.deptno = e1.deptno (+); -- 자료를 모두 가지고 있는 테이블 : 기준테이블

 

 

 

ANSI Outer Join (LEFT OUTER JOIN)

SELECT *
FROM DEPT01 LEFT OUTER JOIN DEPT02
ON DEPT01.DEPTNO = DEPT02.DEPTNO;

[예제] ANSI Outer Join을 이용해 지도교수가 없는 학생 출력

SELECT s.name 학생명, p.name 교수명
FROM student s LEFT OUTER JOIN professor p
USING(profno);

예문을 SQL문으로 표현하기

SELECT s.name 학생명, p.name 교수명 
FROM student s, professor p
WHERE s.profno = p.profno (+);

[예제] Right Outer Join을 이용하여 지도교수가 없는 학생 출력

SELECT s.name 학생명, p.name 교수명 
FROM student s, professor p
WHERE s.profno(+) = p.profno;

[예제] ANSI Right Outer Join을 이용해 지도교수가 없는 학생 출력 (ANSI Right Outer Join)

SELECT s.name 학생명, p.name 교수명 
FROM student s RIGHT OUTER JOIN professor p
USING(profno); -- USING 절에서 쓰인 조건은 출처표기 없이 SELECT절에서 쓰일 수 있음

 

 

 

 

연습문제

문1) 고객(user_data)테이블과 지불(pay_data)테이블을 inner join하여 다음과 같이 출력하시오.
조건1) 고객ID(user_id), 성별(gender), 연령(age), 직업유형(job), 상품유형(product_type), 지불방법(pay_method), 구매금액(price) 칼럼 출력  
조건2) 고객ID 오름차순 정렬

SELECT u.*, p.product_type, p.pay_method, p.price
FROM USER_DATA u, PAY_DATA p 
WHERE u.user_id = p.user_id
ORDER BY u.user_id;
-- [해설] 전체 고객 10중에서 상품을 구매한 고객 레코드 출력

문2) 문1)의 결과에서 성별이 '여자'이거나 지불방법이 '1.현금'인 경우만 출력하시오.
남 : gender=1, 여 : gender=2

SELECT u.*, p.product_type, p.pay_method, p.price
FROM USER_DATA u, PAY_DATA p 
WHERE u.user_id = p.user_id AND (u.gender=2 OR p.pay_method = '1.현금')
ORDER BY u.user_id;
-- [해설] 일반 조건 추가 : 상품 구매 고객 중에서 특정 고객 조회

문3) 고객(user_data)테이블과 지불(pay_data)테이블을 left outer join하여 다음과 같이 출력하시오.
조건) 고객ID, 성별, 나이, 상품유형, 지불방법 칼럼 출력   

SELECT u.user_id, gender, age, p.product_type, p.pay_method
FROM USER_DATA u, PAY_DATA p 
WHERE u.user_id = p.user_id(+);
-- [해설] 전체 고객 10중에서 상품을 구매하지 않은 고객까지 출력

문4) 고객(user_data)테이블과 반품(return_data)테이블을 이용하여 left outer join하여 다음과 같이 출력하시오.
조건1) 고객ID, 성별, 나이, 거주지역, 반품코드 칼럼 출력   
조건2) 반품한 고객만 출력

SELECT u.user_id, gender, age, resident, return_code
FROM USER_DATA u, RETURN_DATA r
WHERE u.user_id = r.user_id(+) 
AND return_code IS NOT NULL;
-- [해설] 전체 고객 중에서 상품을 반품한 고객만 출력 

-- inner join 표현 
SELECT u.user_id, gender, age, resident, return_code
FROM USER_DATA u, RETURN_DATA r
WHERE u.user_id = r.user_id;