[SQL] JOIN(INNER, LEFT, RIGHT, SELF, FULL, NATURAL)

JOIN

 

1. JOIN이란?

: 두 개 이상의 테이블을 서로 연결하여 데이터를 검색할 때 사용하는 방법

: 테이블의 명을 그대로 가져가는 것이 아닌 중복 테이블명 고려를 위해 alias를 사용

: 기본 조인은 INNER JOIN에 해당

[JOIN 기본 형식] JOIN = INNER JOIN
SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1, 테이블2
WHERE 조건절;

SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1 (INNER) JOIN 테이블2
ON 조건
WHERE 추가 조건;

 

1. JOIN을 하기 위해 테이블을 2개 생성

-- CREATE TABLE : 테이블 생성
-- DROP TABLE : 테이블이 있으면 삭제
DROP TABLE test1;
CREATE TABLE test1(
	A VARCHAR(10),
	B VARCHAR(10)
);

DROP TABLE test2;
CREATE TABLE test2(
A VARCHAR(10),
C VARCHAR(20),
D VARCHAR(20));

-- TABLE에 데이터 삽입
INSERT INTO test1 VALUES('a1', 'b1');
INSERT INTO test1 VALUES('a2', 'b2');

INSERT INTO test2 VALUES('a3', 'c3', 'd3');
INSERT INTO test2 VALUES('a4', 'c4', 'd4');
INSERT INTO test2 VALUES('a5', 'c5', 'd5');

 

2. 테이블을 JOIN하여 테이블을 조회

: JOIN을 할 경우 많은 데이터가 생성됨(각 테이블의 데이터의 수를 곱한만큼 생성)

-- t1에 있는 모든 컬럼과 t2에 있는 모든 컬럼을 조회
SELECT t1.*, t2.*
FROM test1 t1, test2 t2;

-- 그냥 테이블을 조회할 경우 두 테이블의 행 수를 곱한만큼 경우의 수 테이블이 생성
/* 출력 결과
A.	B.	A.	B.	C
a1	b1	a3	c3	d3
a2	b2	a3	c3	d3
a1	b1	a4	c4	d4
a2	b2	a4	c4	d4
a1	b1	a5	c5	d5
a2	b2	a5	c5	d5
*/

 

[다른 예제]

-- emp 테이블의 deptno과 dept 테이블의 deptno가 동일한 dname을 출력
SELECT e.empno, e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno;

SELECT e.empno, e.ename, d.dname
FROM emp e JOIN dept d
ON e.deptno=d.deptno;

-- student, exam_01 테이블을 이용해 학번, 이름, 시험점수 조회
SELECT s.studno, s.name, e.total
FROM student s, exam_01 e
WHERE s.studno=e.studno;

SELECT s.studno, s.name, e.total
FROM student s JOIN exam_01 e
ON s.studno=e.studno
ORDER BY 3 desc;


-- student, exam_01, hakjum 테이블을 이용하여 학번, 이름, 시험 점수, 학점 조회
SELECT s.studno, s.name, e.total, h.grade
FROM student s, exam_01 e, hakjum h;
WHERE (s.studno=e.studno) AND (e.total BETWEEN h.min_point AND h.max_point);

SELECT s.studno, s.name, e.total, h.grade
FROM student s JOIN exam_01 e ON s.studno=e.studno 
JOIN hakjum h ON e.total BETWEEN h.min_point AND h.max_point;


-- gogakk, gift 테이블을 이용해 고객의 모든 정보와 고객이 본인의 포인트로 받을 수 있는 가장 좋은 상품 조회
SELECT g.*, t.gname
FROM gogak g JOIN gift t
ON g.point BETWEEN g_start AND g_end
ORDER BY g.point;

SELECT g.*, t.gname
FROM gogak g, gift t
WHERE g.point BETWEEN g_start AND g_end
ORDER BY g.point;


-- emp2, p_grade 테이블을 이용하여 이름, 직위, 급여, 같은 직급의 최소 급여, 최대 급여 조회
SELECT e.name, p.position, p.s_pay, p.e_pay
FROM emp2 e JOIN p_grade p
ON p.position = e.position


-- emp2 , p_grade 테이블을 이용하여 이름, 직위, 나이, 본인의 나이에 해당하는 예상 직급 조회
SELECT e.name, p.position, YEAR(CURDATE())-YEAR(e.birthday) "나이", p.position
FROM emp2 e, p_grade p
WHERE YEAR(CURDATE())-YEAR(e.birthday) BETWEEN p.s_age AND p.e_age;


-- gogak, gift 테이블을 이용하여 노트북을 받을 수 있는 고객긔 이름, 포인트, 상품명 조회
SELECT g.gname, g.point, t.gname
FROM gogak g, gift t
WHERE  g.point >= t.g_start AND t.gname='노트북';

SELECT g.gname, g.point, t.gname
FROM gogak g JOIN gift t ON g.point >= t.g_start
WHERE t.gname='노트북';

---

2. LEFT JOIN이란?

: 왼쪽 테이블을 기준으로 일치하는 행만 결합되고, 일치하지 않는 부분은 null 값으로 채워짐

: 즉, 조건절을 만족하지 않더라도 왼쪽 테이블에 있는 모든 행을 가져옴

SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1 LEFT JOIN 테이블2
ON 조건
WHERE 추가 조건;

SELECT s.studno, s.name, p.name
FROM student s LEFT JOIN professor p
ON s.profno=p.profno;

---

3. RIGHT JOIN이란?

: 오른쪽 테이블을 기준으로 일치하는 행만 결합되고, 일치하지 않는 부분은 null 값으로 채워짐

: 즉, 조건절을 만족하지 않더라도 오른쪽 테이블에 있는 모든 행을 가져옴

SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1
RIGHT JOIN 테이블2
ON 조건
WHERE 추가 조건;

SELECT s.studno, s.name, p.name
FROM student s RIGHT JOIN professor p
ON s.profno=p.profno;

---

4. FULL JOIN이란?

: FULL JOIN과 RIGHT JOIN의 테이블 값을 모두 유지

: MYSQL에서는 FULL JOIN을 지원하지 않아, UNION을 사용

SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1
FULL JOIN 테이블2
ON 조건
WHERE 추가 조건
--------------------------------
SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1
RIGHT JOIN 테이블2
ON 조건
WHERE 추가 조건
UNION
SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1
RIGHT JOIN 테이블2
ON 조건
WHERE 추가 조건;

SELECT s.studno, s.name, p.name
FROM student s LEFT JOIN professor p
ON s.profno=p.profno
UNION
SELECT s.studno, s.name, p.name
FROM student s RIGHT JOIN professor p
ON s.profno=p.profno;

---

5. SELF JOIN이란?

: 하나의 테이블을 가지고 조인을 진행

: 하나의 테이블을 가지고 조인을 진행하기 때문에 각 테이블에 alias를 지정하여 이름을 다르게 설정해야 함

SELECT 테이블1.칼럼, 테이블1.칼럼
FROM 테이블1
SELF JOIN 테이블1
ON 조건
WHERE 추가 조건

-- dept2 테이블을 이용하여 부서의 모든 정보와 각 부서의 상위 부서명을 조회
SELECT d.*, de.area
FROM dept2 d LEFT JOIN dept2 de
ON d.pdept=de.dcode;

SELECT d.*, de.*
FROM dept2 d, dept2 de
ORDER BY 1;

-- emp 테이블을 이용하여 직원의 사번, 이름, 담당 매니저(MGR)사번과 이름 조회
SELECT e1.empno, e1.ename, e1.mgr, e2.ename
FROM emp e1, emp e2
WHERE e1.mgr=e2.mgr;

---

6. NATURL JOIN이란?

: 두 테이블에서 동일한 컬럼명( 두 테이블 간의 동일한 이름, 타입을 가진 컬럼)을 갖는 컬럼은 모두 조인

: 위 SELF JOIN에서 USING을 사용하는 대신 NATURAL JOIN으로 변경 가능

SELECT 테이블명1.칼럼명1, 테이블명2.칼럼명2
FROM 테이블명1 NATURAL JOIN 테이블명2

SELECT e.*, d.*
FROM emp e JOIN dept d ON e.deptno=d.deptno;

-- 동일한 칼럼명이기 때문에 NATURAL JOIN으로 변경 가능
SELECT e.*, d.dname
FROM emp e  NATURAL JOIN dept d;

---

[심화 예제]

-- student, department 테이블을 이용하여 학번, 이름, 제1전공명, 제2전공명 조회
-- 제2전공이 NULL인 학생들은 NULL인 상태로 나오도록 출력
SELECT s.studno, s.name, d1.dname, d2.dname
FROM student s JOIN department d1 ON s.deptno1=d1.deptno 
LEFT JOIN department d2 ON s.deptno2=d2.deptno;

-- student, department 테이블을 이용하여 컴퓨터정보학부에 속한 학생들의 학번, 이름, 학과 조회
SELECT s.studno, s.name, d.dname
FROM student s JOIN department d
ON s.deptno1=d.deptno AND d.part=100;

SELECT s.studno, s.name, d2.dname, d1.dname
FROM student s JOIN department d1 ON s.deptno1=d1.deptno 
JOIN department d2 ON (d1.part=d2.deptno AND d2.dname='컴퓨터정보학부');

-- student, department 테이블에서 제1전공이나 제2전공으로 인해 '전자제어관'에서 수업을 듣는 학생들 조회
SELECT s.studno, s.name, s.deptno1, s.deptno2, d1.build
FROM student s JOIN department d1
ON s.deptno1=d1.deptno OR s.deptno2=d1.deptno
WHERE d1.build='전자제어관';

SELECT s.studno, s.name, s.deptno1, s.deptno2, d1.build, d2.build
FROM student s JOIN department d1 ON s.deptno1=d1.deptno 
LEFT JOIN department d2 ON s.deptno2=d2.deptno
WHERE d1.build='전자제어관' OR d2.build='전자제어관';

-- emp 테이블에서 자신보다 입사일이 빠른 사람의 수 조회 (NULL 값을 포함하지 않음)
SELECT e1.empno, e1.ename, e1.hiredate, COUNT(e2.hiredate)
FROM emp e1 LEFT JOIN emp e2
ON e1.hiredate > e2.hiredate
GROUP BY e1.empno, e1.ename
ORDER BY 1;

 

---

[심화 예제]

-- student, department 테이블을 이용하여 학번, 이름, 제1전공명, 제2전공명 조회
-- 제2전공이 NULL인 학생들은 NULL인 상태로 나오도록 출력
SELECT s.studno, s.name, d1.dname, d2.dname
FROM student s JOIN department d1 ON s.deptno1=d1.deptno 
LEFT JOIN department d2 ON s.deptno2=d2.deptno;

-- student, department 테이블을 이용하여 컴퓨터정보학부에 속한 학생들의 학번, 이름, 학과 조회
SELECT s.studno, s.name, d.dname
FROM student s JOIN department d
ON s.deptno1=d.deptno AND d.part=100;

SELECT s.studno, s.name, d2.dname, d1.dname
FROM student s JOIN department d1 ON s.deptno1=d1.deptno 
JOIN department d2 ON (d1.part=d2.deptno AND d2.dname='컴퓨터정보학부');

-- student, department 테이블에서 제1전공이나 제2전공으로 인해 '전자제어관'에서 수업을 듣는 학생들 조회
SELECT s.studno, s.name, s.deptno1, s.deptno2, d1.build
FROM student s JOIN department d1
ON s.deptno1=d1.deptno OR s.deptno2=d1.deptno
WHERE d1.build='전자제어관';

SELECT s.studno, s.name, s.deptno1, s.deptno2, d1.build, d2.build
FROM student s JOIN department d1 ON s.deptno1=d1.deptno 
LEFT JOIN department d2 ON s.deptno2=d2.deptno
WHERE d1.build='전자제어관' OR d2.build='전자제어관';

-- emp 테이블에서 자신보다 입사일이 빠른 사람의 수 조회 (NULL 값을 포함하지 않음)
SELECT e1.empno, e1.ename, e1.hiredate, COUNT(e2.hiredate)
FROM emp e1 LEFT JOIN emp e2
ON e1.hiredate > e2.hiredate
GROUP BY e1.empno, e1.ename
ORDER BY 1;