KOSA fullstack 교육 (JOIN)

JOIN

• 하나 이상의 테이블에서 질의를 던지는 경우 발생한다.
• 일반적인 경우 Primary Key와 Foreign Key 값의 연관에 의해서 발생한다.
• Primary Key를 가지고 있는 테이블이 부모가 되고, Foreign key를 가지고 있는 테이블이 자식이 된다.
• JOIN의 종류는 여러가지가 있지만 대표적으로 EQ JOIN, Self JOIN, Outer JOIN이 있다.
• PK, FK의 관계 지정이 없어도 논리적인 값들의 연결만으로도 JOIN 작업을 할 수 있다.

 

각각의 사원이 어느 부서에서 일하는 지 검색해보자

JOIN을 사용하지 않는다면 모든 테이블을 다 꺼내봐야 할 것이다.

SELECT * FROM EMP;
SELECT * FROM DEPT;
SELECT * FROM EMP, DEPT;

 

Cartesian Product란?

 emp와 dept 테이블의 수평적인 결합 
    1) 조인 조건을 생략했을 때
    2) 조인 조건을 잘못줬을 때

 

 

EquiJoin(= 연산자를 사용하는 가장 흔한 조인)

• 조인 조건은 WHERE 절에 사용
• 조인 조건은 두 테이블의 공통 컬럼으로 한다.

SELECT * FROM emp, dept
WHERE emp.deptno = dept.deptno;

 

Table Alias 사용! 

• 나열하고자 하는 컬럼명을 명시해야한다. 
• . * 지양

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

 

다음과 같이 실행하면 에러가 난다. 이유는? 

SELECT empno, ename, sal, deptno, dname, loc
FROM emp e, dept d
WHERE e.DEPTNO = d.DEPTNO;

-- deptno 가 누구꺼인지 모르기 때문에 에러가 난다. 
-- 머신은 모든 테이블을 다 돌려서 찾고, 두 테이블이 모두 가지고 있다면 에러를 반환한다.
-- 그러므로 성능을 올리기 위해 alias 를 붙이는 것이 좋다. (엄청 성능 좋아짐!)

 

수정한 버전

SELECT e.empno, e.ename, e.sal, d.deptno, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;

 

 

EquiJoin 예제)

사원이름, 급여, 부서번호, 부서이름, 부서위치를 조회
단, 급여가 2000달러 이상이고 30번 부서에 한해서만 조회

빨간색은 기본 조건 연산이고, 핑크색은 조인 연산 이다.

 

 

ON, USING

• 두 테이블간의 조인 시 가장 많이 사용되는 키워드
• 조인 할 컬럼명이 같은 경우 ON,USING 다 사용가능하지만 
• 컬럼명이 다른 경우에는 ON만 사용할 수 있다.

기본적으로 ON은 이러한 문법으로 사용한다. 

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

 

예제)
사원의 급여가 3000달러 이상이 되는 사원의 사원번호, 사원이름, 급여, 부서번호, 부서이름을 검색한다.
WHERE EQUIJOIN과, JOIN ON 사용 

-- EQUI JOIN ver
SELECT e.empno, e.ename, e.sal, d.deptno, d.dname 
FROM emp e, dept d
WHERE e.deptno = d.deptno AND e.sal >= 3000;
-- JOIN ON ver
SELECT e.empno, e.ename, e.sal, d.deptno, d.dname 
FROM emp e JOIN dept d ON e.deptno = d.deptno 
WHERE e.sal >= 3000;

JOIN ON 버전에서, WHERE절에 조건을 추가했지만

바로 AND로 조건을 추가해도 가능하다. 

-- JOIN ON ver
SELECT e.empno, e.ename, e.sal, d.deptno, d.dname 
FROM emp e JOIN dept d ON e.deptno = d.deptno 
AND e.sal >= 3000;

 

여기서 ON과 USING의 차이가 나온다

ON : AND절 뒤에 사용 가능
USING : AND 절 뒤에 사용 불가

 

USING 예시

-- USING절 사용
SELECT e.empno, e.ename, e.sal, deptno, d.dname
FROM emp e JOIN dept d USING (deptno);

e.deptno = d.deptno 처럼 동일한 필드를 2번 부르지 않는다는 장점이 있다. 

USING절의 특성으로, 프로젝션에서 e.deptno -> deptno로 하는 게 더 좋다!!

(USING 쓰면 deptno는 자동으로 단일화되므로 e.deptno, d.deptno 중 구분 안 해도 됨)

 

 

또한 AND 사용 불가이기 때문에 WHERE을 써주어야 한다. 

-- USING절 사용
SELECT e.empno, e.ename, e.sal, deptno, d.dname
FROM emp e JOIN dept d USING (deptno)
-- AND e.sal >= 3000;
WHERE e.sal >= 3000;

 

SELFJOIN에 대해 알아보자 

• 하나의 테이블을 쪼개서 생각
• 하나의 테이블을 사원 입장에서, 관리자 입장에서 나눠서 질의하는 경우를 말한다.
• 이때 조인 조건으로 공통적인 컬럼을 사용할 수 없다. (USING 사용 불가)

 

-- 특정 사원의 상사 이름을 조회 

1. 특정 사원을 조회 ename, .. ALLEN 
2. ALLEN 에 해당하는 MGR을 찾아서... 7698
3. 다시 EMP 테이블의 empno를 찾고 여기에 해당하는 ename을 찾는다. . .BLAKE
   
   

emp에서 ALLEN을 찾고, 그의 상사로 또 다시 emp를 찾기 때문에 SELF 조인이 적합하다!

 

단계별로 진행

-- step1
SELECT empno, ename, mgr FROM emp;
SELECT empno, ename FROM emp;

-- step2
SELECT * FROM (SELECT empno, ename, mgr FROM emp) e,
(SELECT empno, ename FROM emp) m;

-- step3
SELECT * FROM (SELECT empno, ename, mgr FROM emp) e,
(SELECT empno, ename FROM emp) m
WHERE e.mgr = m.empno ;

-- JOIN ON ver
-- 컬럼명이 다르기 때문에 USING은 SELF JOIN에서 못쓴다. 
SELECT * FROM (SELECT empno, ename, mgr FROM emp) e
JOIN (SELECT empno, ename FROM emp) m ON e.mgr = m.empno;

-- step 4
SELECT e.ename 사원이름, m.ename 상사이름 
FROM emp e, emp m
WHERE e.mgr = m.empno AND e.ename = 'BLAKE';

 

OUTER JOIN 

 

A, B 테이블을 조인할 경우
양쪽 모두에 들어있는 데이터가 아니더라도 표시하고 싶을 때 Outer Join을 사용 

LEFT OUTER JOIN | RIGHT OUTER JOIN | FULL OUTER JOIN
(데이타 있는 쪽을 기준으로 한다)

 

 

RIGHT OUTER JOIN 

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

 

 

LEFT OUTER JOIN

• 특정 사원의 상사의 이름을 검색 
• KING은 MGR이 존재하지 않기 때문에 컬럼값이 null
• e.mgr 이 부분이 Null로 나오기 때문에 13행이 출력 

SELECT ifnull(CONCAT(e.ename, '의 상사이름은', m.ename, '입니다.'),CONCAT(e.ename,'의 상사는 없습니다')) Info
FROM emp e
LEFT OUTER JOIN emp m
ON e.mgr = m.empno ;

 

FULL OUTER JOIN

• mysql 에서는 full outer join이 없고 대신 union을 사용한다.

CREATE TABLE outera(sawonid int); -- 10,20,40 
CREATE TABLE outerb(sawonid int); -- 10,20,30 

insert into outera values(10);
insert into outera values(20);
insert into outera values(40);

insert into outerb values(10);
insert into outerb values(20);
insert into outerb values(30);

select * from outera;
select * from outerb;

select sawonid from outera
UNION select * from outerb;

 

집합 연산자 

INTERSECT: 두 SELECT 결과에서 겹치는 행만 가져온다(오라클 전용)

UNION: 두 SELECT 결과를 합쳐서 출력, 단 같은 값이 두 번 나오면 한 번만 보여줌.

UNION ALL: 두 SELECT 결과를 전부 다 합친다 → 중복되는 값도 다 보여줌

MINUS: 첫 번째 SELECT 결과에서, 두 번째 SELECT 결과에는 없는 것만 가져온다.(오라클 전용)

**CTAS: Create Table As Select 기존의 테이블을 가지고 새로운 테이블을 생성할 때 사용

       -> 제약조건은 적용되지 않는다 !!!

 

CTAS 예시

CREATE TABLE newemp
AS (SELECT * FROM emp);

-- 제약 조건은 적용되지 않는다.
DESC newemp;
SELECT * FROM newemp;

 

이런식으로 내가 원하는 컬럼만 골라서 만들 수 있다.

CREATE TABLE newemp 
AS (SELECT empno, ename, job, sal, deptno
FROM emp WHERE deptno = 20);