[DBMS] Join, Sub Query

* Join 이란
하나의 테이블뿐이 아니라,  여러개의 테이블에서 정보를 가져와서 결과를 만들어 주는 기법을 Join 이라 합니다. 

관계형 데이터베이스(RDBMS) 의 가장 핵심 기술중 하나.
ANSI join 방식이 있고, DBMS마다 특화된 방식이 있긴 하나 하나 원리를 알면 다른 방법은 손쉽게 알수 있습니다.

 

* 카티션 곱 (Cartesian Product)

- 두개의 테이블을 JOIN 하게 되면, 각 테이블의 레코드들의 모든 조합이 출력된다.
- WHERE 나 ON 등으로 JOIN 조건이 주어지지 않으면 모든 카티션곱이 출력된다.

-> t_emp 는 14개, t_dept 는 4개  
그래서 t_emp X t_dept → 56개로 이루어진 카티션 곱이 나온다.

** 출력하는 필드에 별명을 붙일수 있듯이 테이블에도 별명을 붙여서 사용 가능하다.   

복수개의 테이블에서 같은 이름의 필드가 있는 경우는 반드시 서로 다른 테이블 별명을 사용하여 접근해야 한다.

* Join 의 종류

Equi Join (등가 Join)
일반적으로 많이 쓰이는 Join 이며, 양쪽 테이블에 같은 조건이 존재할 경우만 값을 가져오는 것

 

-- 예) #6101 
-- t_student 테이블과 t_department 테이블을 사용하여 학생이름, 1전공 학과번호, 1전공 학과 이름을 출력하세요

-- MySQL 구문.
SELECT s.name 학생이름, s.deptno1 학과번호, d.dname 학과이름
FROM t_student s, t_department d
WHERE s.deptno1 = d.deptno  -- JOIN 조건
;

-- ANSI SQL 구문
SELECT s.name 학생이름, s.deptno1 학과번호, d.dname 학과이름
FROM t_student s JOIN t_department d
		ON s.deptno1 = d.deptno  -- JOIN 조건
;


-- 제2전공은?
-- null 값은 JOIN 조건에 참여 안함.
SELECT s.name 학생이름, s.deptno2 제2전공번호, d.dname 학과이름
FROM t_student s, t_department d
WHERE s.deptno2 = d.deptno  -- JOIN 조건

-- 연습 #6102)
-- t_student 테이블, t_professor 테이블 을 join하여 
-- ‘학생이름’, ‘지도교수번호’, ‘지도교수이름’ 을 출력하세요

-- MySQL 구문
SELECT s.name "학생이름", s.profno "지도교수", p.name "지도교수이름" 
FROM t_student s, t_professor p 
WHERE s.profno = p.profno;

-- ANSI 구문
SELECT s.name "학생이름", s.profno "지도교수", p.name "지도교수이름"
FROM t_student s JOIN  t_professor p 
ON s.profno = p.profno;

-- #6103
-- MySQL
SELECT s.name "학생이름", d.dname "학과이름", p.name "교수이름"
FROM t_student s, t_department d, t_professor p
WHERE s.deptno1 = d.deptno AND s.profno = p.PROFNO;

-- ANSI
SELECT s.name "학생이름", d.dname "학과이름", p.name "교수이름"
FROM t_student s
	JOIN t_department d ON s.deptno1 = d.deptno -- 먼저 이 JOIN 을 실행후 나온 결과값과 
	JOIN t_professor p ON s.profno = p.PROFNO   -- 다음 JOIN 수행

- #6105
-- t_student - t_professor 테이블 join 하여 
-- 제1전공(deptno1) 이 101번인 학생들의 
-- 학생이름과 지도교수 이름을 출력하세요

-- MySQL
SELECT s.name "학생이름", p.name "교수이름"
FROM t_student s, t_professor p
WHERE 
	s.profno = p.PROFNO  -- JOIN 조건  카티션곱에서 걸러내기
	AND s.deptno1 = 101  -- 검색 조건.  카티션곱 만들기 전에 걸러내기
;

-- ANSI
SELECT s.name "학생이름", p.name "교수이름"
FROM t_student s 
	JOIN t_professor p ON s.profno = p.PROFNO AND s.deptno1 = 101 
-- WHERE s.deptno1 = 101
;

* Non-Equi Join (비등가 Join)
Equi Join은 서로 같은 조건 (=)을 가진 데이터를 Join 해서 가져오는 방법.
그러나 ‘크거나 작거나 하는 경우의 조건도 있을수 있습니다’  그러한 조건으로 join 을 수행하는 것을 Non-Equi Join 이라 합니다.

- 비등가 조인 (Non-Equi Join)

-- #6201) 
-- t_customer 테이블, t_gift 테이블을 join 하여  
-- 고객의 마일리지 포인트별로 받을수 있는 상품을 조회하여 
-- 고객의 '이름(c_name)'과 포인트(c_point) 상품명(g_name)을 출력하세요
-- BETWEEN ~ AND ~ 사용

SELECT * FROM t_customer; -- 일단 함 보자
SELECT * FROM t_gift;

-- MySQL
SELECT c.c_name "고객명", c.c_point "POINT", g.g_name "상품명"
FROM t_customer c, t_gift g
WHERE c.c_point BETWEEN g.g_start AND g.g_end;  

-- ANSI
SELECT c.c_name "고객명", c.c_point "POINT", g.g_name "상품명"
FROM t_customer c 
	JOIN t_gift g ON c.c_point BETWEEN g.g_start AND g.g_end;  

--  #6202) 연습 
-- 앞 예제에서 조회한 상품의 이름(gname)과 필요수량이 몇개인지 조회하세요
-- (그룹함수 동원되어야 한다)

-- MySQL
SELECT g.g_name "상품명", count(*) "필요수량"
FROM t_customer c, t_gift g
WHERE c.c_point BETWEEN g.g_start AND g.g_end
GROUP BY g.g_name;

-- ANSI
SELECT g.g_name "상품명", count(*) "필요수량"
FROM t_customer c JOIN t_gift g
ON c.c_point BETWEEN g.g_start AND g.g_end
GROUP BY g.g_name;

-- #6204
-- MySQL
SELECT c.c_name "고객명", c.c_point "POINT", g.g_name "상품명"
FROM t_customer c, t_gift g 
WHERE g.g_start <= c.c_point
AND g.g_name = '산악용자전거';

-- ANSI
SELECT c.c_name "고객명", c.c_point "POINT", g.g_name "상품명"
FROM t_customer c 
	JOIN t_gift g ON g.g_start <= c.c_point 
WHERE g.g_name = '산악용자전거';

-- #6203 연습
SELECT * FROM t_credit;
-- MySQL	
SELECT s.name "학생이름", e.total "점수", h.grade "학점"
FROM t_student s, t_exam01 e, t_credit h
WHERE s.studno = e.studno
	AND
	e.total BETWEEN h.min_point AND h.max_point;
	
-- ANSI 구문 
SELECT s.name "학생이름", e.total "점수", h.grade "학점"
FROM t_student s
	JOIN t_exam01 e ON s.studno = e.studno 
	JOIN t_credit h ON e.total 
	BETWEEN h.min_point AND h.max_point;

-- #6205 연습
SELECT * FROM t_post;

-- MySQL 
SELECT e.name "이름", year(now()) - year(BIRTHDAY) + 1 "현재나이",
	ifnull(e.post, '') "현재직급", p.post "예상직급"
FROM t_emp2 e, t_post p
WHERE year(now()) - year(BIRTHDAY) + 1 BETWEEN p.s_age AND p.e_age;

-- ANSI
SELECT e.name "이름", year(now()) - year(BIRTHDAY) + 1 "현재나이",
	ifnull(e.post, '') "현재직급", p.post "예상직급"
FROM t_emp2 e JOIN t_post p
ON year(now()) - year(BIRTHDAY) + 1 BETWEEN p.s_age AND p.e_age;

-- 지금까지의 JOIN 은 모두 INNER JOIN 이다.

SELECT s.name, p.name
FROM t_student s, t_professor p
WHERE s.profno = p.profno
;

SELECT s.name, p.name
FROM t_student s INNER JOIN t_professor p
ON s.profno = p.profno
;

-- 카티션곱 --> cross join 이라 함ㄴ
SELECT s.name, p.name
FROM t_student s, t_professor p
;

SELECT s.name, p.name
FROM t_student s CROSS JOIN t_professor p
;

OUTER Join (아우터 조인)
앞서본 equi join, non-equi join 은 Join 에 참여하는 모든 테이블에 데이터가 존재하는 경우에만 결과 값을 출력.

이런 Join 을 INNER Join (이너 조인) 이라 함.
OUTER Join 이란 한쪽 테이블에 데이터가 있고 한쪽 테이블에 없는 경우 데이터가 있는 쪽 테이블의 내용을 전부 출력
필연적으로 OUTER Join 은 DB 성능에 나쁜 영향 줌.

-- OUTER JOIN

-- #6206)
-- t_student 테이블과 t_professor 테이블 Join : 
-- 학생이름과 지도교수 이름을 출력하세요. 
-- 단! 지도교수가 결정되지 않은 학생의 명단도 함께 출력하세요


SELECT s.name, p.name
FROM t_student s LEFT OUTER JOIN t_professor p
ON s.profno = p.profno
;

-- #6207
-- 지도학생이 결정되지 않은 교수도 출력하기
SELECT s.name, p.name
FROM t_student s RIGHT OUTER JOIN t_professor p
ON s.profno = p.profno
;


-- #6208
-- MySQL 은 Full Outer 를 지원하지 않는다.
-- 대신 UNION 을 사용하여 full outer 를 구현

SELECT s.name, p.name
FROM t_student s LEFT JOIN t_professor p
ON s.profno = p.profno
UNION  -- 합집합
SELECT s.name, p.name
FROM t_student s RIGHT JOIN t_professor p
ON s.profno = p.profno
;

SELF Join 
만약 원하는 데이터가 하나의 테이블에 다 들어 있다면?
이럴때 사용하는 것이 SELF Join

-- SELF JOIN
-- '부서명' 과 그 부서의 '상위 부서명'을 출력
SELECT * FROM t_dept2;

-- MySQL
SELECT d1.dname 부서명, d2.dname 상위부서명
FROM t_dept2 d1, t_dept2 d2
WHERE d1.pdept = d2.dcode;

-- ANSI
SELECT d1.dname 부서명, d2.dname 상위부서명
FROM t_dept2 d1 JOIN t_dept2 d2
ON d1.pdept = d2.dcode;

-- #6210
-- 우선 자기보다 빨리 입사한 사람들의 입사일을 나열해보자

SELECT a.profno "교수번호", a.name "교수명", a.hiredate "입사일",
       b.name "빠른교수명", b.hiredate "빠른입사일"
FROM t_professor a LEFT OUTER JOIN t_professor b
ON b.hiredate < a.hiredate
ORDER BY 3
;

-- GROUP BY
SELECT a.profno "교수번호", a.name "교수명", a.hiredate "입사일",
       count(b.hiredate) "빠른사람"
FROM t_professor a LEFT OUTER JOIN t_professor b
ON b.hiredate < a.hiredate
GROUP BY a.profno
ORDER BY 4
;

* Sub Query 
“t_emp 테이블에서 scott 보다 급여를 많이 받는 사람은 누구인가요?”
일단 ‘scott 의 급여’ 를 알아야  ‘scott 보다 많이 받는 사람의 급여’를 조회할수 있다
즉!  2번의 쿼리를 작성해야 하는데,  서버에 2차례 쿼리 I/O 를 발생시키는 것은 성능 저하로 이어집니다.   

그래서 한번에 여러개의 쿼리를 처리 할수 있는 방법으로 Sub Query 가 제공됩니다.

 

Sub Query 주의사항

1. SubQuery 는 연산자 오른쪽에 위치해야 하며 반드시 괄호로 묶어야 한다.
2. 특별한 경우를 제외하고는 SubQuery절에는 Order By 가 올수 없습니다.
3. 단일행 SubQuery , 다중행 SubQuery 에 따라 연산자를 잘 선택해야 한다.

 

1. 단일행 서브쿼리
- SubQuery 결과가 한개 1행만 나오는 것.
- 단일행 Sub Query의  WHERE 에서 사용되는 연산자
- = , <> , !=, >, >= , <, <= 

-- #7103)연습
-- t_student, t_department 테이블 사용하여 
-- 이윤나 학생과 1전공(deptno1)이 동일한 학생들의 
-- 이름(name)과 1전공이름(dname)을 출력하세요

SELECT deptno1 FROM t_student WHERE name='이윤나';

SELECT s.name "학생이름", d.dname "제1전공"
FROM t_student s, t_department d
WHERE s.deptno1 = d.deptno
	AND s.deptno1 = (SELECT deptno1 FROM t_student WHERE name='이윤나');

-- #7104
SELECT p.name "교수명", 
	date_format(p.hiredate, '%Y-%m-%d') "입사일", 
	d.dname "학과명"
FROM t_professor p, t_department d
WHERE p.deptno = d.deptno
	AND p.hiredate > (SELECT hiredate FROM t_professor WHERE name = '송도권');

-- #7105
SELECT name "이름", weight "몸무게"
FROM t_student
WHERE weight > (SELECT avg(weight) FROM t_student WHERE deptno1=101);

-- #7106
SELECT name "이름", pay "급여", hiredate "입사일"
FROM t_professor
WHERE hiredate = (SELECT hiredate FROM t_professor WHERE name = '심슨')
	AND pay < (SELECT pay FROM t_professor WHERE name = '조인형');

-- #7107) 예제
-- t_emp2, t_dept2 테이블 : 
-- 근무지역 (t_dept2.area) 이 서울 지사인 모든 사원들의 
-- 사번(empno)과 이름(name), 부서번호(deptno)를 출력하세요
SELECT * FROM t_emp2;
SELECT * FROM t_dept2;

SELECT dcode FROM t_dept2 WHERE area = '서울지사';

SELECT empno, name, deptno
FROM t_emp2 
WHERE deptno IN (SELECT dcode FROM t_dept2 WHERE area = '서울지사');

다중행 Sub Query

Sub Query 결과가 2건(row) 이상 출력되는 것을 말합니다.
다중행 Sub Query 와 함께 사용하는 연산자

-- #7108
SELECT pay FROM t_emp2 WHERE post = '과장';

SELECT name "이름", post "직급", concat(format(pay, 0), '원') "연봉"
FROM t_emp2
WHERE pay >ANY (SELECT pay FROM t_emp2 WHERE post = '과장')
;

-- 아래와 같이 단일행 서브쿼리로도 가능.
SELECT name "이름", post "직급", concat(format(pay, 0), '원') "연봉"
FROM t_emp2
WHERE pay > (SELECT min(pay) FROM t_emp2 WHERE post = '과장')
;

-- #7109
SELECT name "이름", grade "학년", weight "몸무게"
FROM t_student
WHERE weight <ALL (SELECT weight FROM t_student WHERE grade = 4 );

-- #7110
SELECT d.dname "부서명", e.name "사원명", e.pay "연봉"
FROM t_emp2 e, t_dept2 d
WHERE e.deptno = d.dcode
AND e.pay < ALL ( SELECT AVG(pay) FROM t_emp2 GROUP BY deptno )
ORDER BY e.pay;