SQL_JOIN (inner join, equi join, non-equi join, outer join, self join)

📌 조인(join)

1. 조인의 개념

1) 하나의 sql명령문에 의해 여러 테이블에 저장된 데이터를 한번에 조회할 수 있는 기능을 말한다.
2) 관계형 데이터베이스 분야의 표준
3) 두개 이상의 테이블을 결합한다는 의미.

2. 조인의 필요성

1) 조인을 사용하지 않는 일반적인 예
학생 주소록을 출력하기 위해 학생들의 학번, 이름, 소속학과, 이름을 검색
학생에 대한 정보 검색하는 단계 필요
학생 정보에서 소속학과번호 정보를 추출하여 소속학과 이름을 검색하는 단계 필요
ex) 사원번호가 7369인 사원의 이름과 부서이름, 부서위치를 출력하세요.

• 사원정보에서 부서번호를 가져온다.
• 부서정보에서 부서이름과 부서위치를 가져온다.

2) 조인을 하는 경우

• 연관관계에 있는 deptno를 조회 조건으로 검색을 하면 한번에 원하는 데이터를 가져올 수 있다.

SELECT *
FROM emp
WHERE empno = 7369;

SELECT *
FROM dept
WHERE deptno = 20;

SELECT e.*, d.*
FROM emp e, dept d
WHERE e.deptno = d.DEPTNO 
AND empno = 7369;

-- ex) ename이 ALLEN인 경우의 부서이름과 부서위치를 가져오되, 
-- 조인을 하지 않은 경우와 조인을 한 경우 두가지를 처리하여 출력하세요. 

-- join을 하지 않은 경우
SELECT ename, deptno
FROM EMP e 
WHERE ename = 'ALLEN'; 

-- 30
SELECT *
FROM DEPT 
WHERE deptno = 30; 

-- join을 한 경우
SELECT d.dname, d.loc, e.* 

--SELECT e.*, d.*
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND empno = 7499;

SELECT d.dname, d.loc, e.* 
--SELECT e.*, d.*
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND loc = 'DALLAS';

SELECT d.dname, d.loc, e.* --SELECT e.*, d.*
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND sal>=3000; 

-- 중첩되는 컬럼(공통컬럼)은 반드시 어떤 테이블 소속인지를 명기해야 되지만, 
-- 컬럼명이 다른 각각의 컬럼은 테이블명이나 테이블의 alias(별칭 e., d.)을 사용하지 않아도 된다. 

SELECT dname, loc, empno, ename, job, hiredate, sal
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND sal>=3000;

SELECT e.*, d.*
FROM emp e, dept d; 
-- 카티션곱 조인 : 부서번호 10, 사원정보 13개 다 표시, 부서20 사원정보 13개 다 표시

-----------------------------------------------------------------
-- SMITH의 부서이름과 부서위치를 확인하기 위해서 join을 하지 않는 경우
SELECT ename, deptno
FROM EMP e 
WHERE ename = 'SMITH'; 

-- 20
SELECT *
FROM DEPT 
WHERE deptno = 20;

SELECT e.*, d.*
FROM emp e, dept d
WHERE e.deptno = d.deptno;
/*
dept라는 부서테이블 정보를 emp라는 사원정보와 연결관계를 설정하는데
부서라는 정보에 여러개의 사원 정보를 연결할 수 있어, 1:다 관계로 설정이 된 연관관계 
테이블이다. 중간에 연결되는 컬럼을 deptno로 연결하여 처리될 수 있게 한다. 
ps) WHERE e.deptno = d.deptno;
위와 같이 중첩되는 컬럼의 경우, 어느 테이블에 종속된 컬럼인지 반드시 앞에 테이블명이나 
테이블명의 별칭으로 구분하여야 한다. 

두개의 테이블은 deptno(부서번호)를 기준으로 연관관계로 설정하기 때문에 사원테이블의 부서번호와 부서테이블의 부서번호를 조인을 하여 (e.deptno = d.deptno;) 확인한다. 
*/

📌 equi join

1. 개념

1)조인 대상 테이블에서 공통 컬럼을 '='(equal) 비교를 통해서 같은 값을 가지는 행을 연결하여
결과를 생성하는 조인 방법
2) SQL 명령문에서 가장 많이 사용하는 조인 방법
3) 조인 애크리뷰트(속성)

2. where 절을 이용한 equi join 사용법

select 테이블1.컬럼명, 테이블2.컬럼명
from 테이블1, 테이블2
where 테이블1.컬럼명 = 테이블2.컬럼명

1) from 조인 대상 테이블을 기술한다. 테이블은 콤마(,)로 구분
2) where : 조인 애트리뷰트와 '=' 연산자를 사용하여 조인 조건을 기술

3. oracle에서 지원하는 natural join

1) 자연조인을 이용한 equi join

• 오라클 9i 버전부터 equi join을 자연 조인이라고 명명
• where절을 사용하지 않고 natural join 키워드 사용
• 오라클에서 자동적으로 테이블의 모든 컬럼을 대상으로 공통 컬럼을 조사한 후, 내부적으로 조인문 생성
• 테이블 간에 컬럼명과 type이 같은 것을 확인하여, 같은 데이터가 있는 row단위로 연결하여 출력한다.

2) 기본 형식
select 테이블1.컬럼명, 테이블2.컬럼명
from 테이블1 natural join 테이블2

3) 주의
조인 애트리뷰터에 테이블 별명을 사용하면 오류가 발생

SELECT *
FROM emp NATURAL JOIN dept; -- deptno가 같은 type이고 같은 이름이어서 데이터를 확인하여 동일한 데이터가 있는 row단위로 출력한다. 

-- 테이블 생성
CREATE TABLE emp200 
AS SELECT * 
FROM emp 
WHERE deptno=10;

CREATE TABLE dept200 
AS SELECT * 
FROM dept; 

-- ex) emp200과 dept200을 natural join하여 부서명, 사원번호, 사원명, 직책명, 급여를 출력하세요. 
SELECT dname, empno, ename, job, sal 
FROM emp200 NATURAL JOIN dept200;

📌 join ~ using절을 이용한 equi join

1. using에 조인 대상 컬럼을 지정

컬럼 이름은 조인 대상 테이블에서 동일한 이름으로 정의되어 있어야 함.

2. 사용법

select 테이블.컬럼명, 테이블2.컬럼명
from 테이블1 join 테이블2
using(컬럼명)

3. 주의

조인 애트리뷰트에 테이블 별명을 사용하면 오류가 발생

SELECT *
FROM emp JOIN dept
USING (deptno);

📌 non-equi join

1. <,between a and b 와 같이 '=' 조건과 동일하지 않고, 범위를 지정하여 조인 하는 경우를 말한다.

2. 사용 예)
   급여의 등급테이블을 사원 정보의 급여 테이블과 non-equi join 하여 처리한다.

SELECT ename, sal, grade 
FROM emp e, salgrade s
WHERE sal BETWEEN losal AND hisal;

CREATE TABLE SALGRADE02
AS SELECT * 
FROM SALGRADE;

-- ex) emp와 salgrade02를 조인하여 사원명 급여 급여등급을 출력하세요. 
SELECT ename, sal, grade
FROM emp, SALGRADE02 
WHERE sal BETWEEN losal AND hisal;

CREATE TABLE DEPT03
AS SELECT * FROM DEPT d;

SELECT loc, losal, hisal
FROM SALGRADE02, DEPT03
CREATE TABLE SALGRADE04
AS SELECT grade loc, losal rempno, hisal hiempno
FROM SALGRADE s;

SELECT empno, ename, loc, rempno, hiempno
FROM emp, SALGRADE04 s4
WHERE empno BETWEEN s4.REMPNO AND s4.HIEMPNO
ORDER BY empno;

📌 outer join

1. 개요

1) equi join의 조인 조건에서 양측 컬럼 값 중, 어느 하나라도 null이면 '='비교 결과가 거짓이
되어 null값을 가진 조인 결과로 출력이 불가
null에 대하여 어떠한 연산을 적용하더라도 연산 결과는 null

2) 일반적인 equi join의 예
학생 테이블의 학과번호 컬럼과 부서 테이블의 부서번호 컬럼에 대한 equi join한 경우 학생
테이블의 deptno 컬럼이 null인 경우 해당 학생은 결과로 출력되지 않음.
ex) 사원 정보에 해당 부서 40가 없는 경우, 40부서 정보를 출력하지 않음.

3) equi join에서 양측 컬럼 값 중의 하나가 null이지만, 조인 결과로 출력할 필요가 있는 경우
outer join 사용

4) outer join의 예

• 학생 테이블과 교수 테이블을 equi join하여 학생의 지도 교수 이름 출력
• 조건 : 지도 학생을 한 명도 배정받지 못한 교수 이름도 반드시 함께 출력
  ex) 부서 정보에서 사원 정보가 하나도 없는 부서도 함께 출력

2. 기본 형식

(+) 기호를 사용한 outer join

• where절의 조인 조건에서 outer join연산인 (+)기호 사용
• 조인 조건문에서 null이 출력되는 테이블의 컬럼에 (+) 기호 추가(주의)

  select 테이블1.컬럼, 테이블2.컬럼
  from 테이블1, 테이블2
  where 테이블1.컬럼(+) = 테이블2.컬럼
  또는 테이블1.컬럼 = 테이블2.컬럼(+)

SELECT ename, deptno
FROM emp;

SELECT DISTINCT deptno
FROM emp;

SELECT *
FROM dept; 

-- 부서정보 중에 40인 operations는 사원이 등록되지 않는 것을 볼 수 있다. -- 부서별로 사원 현황
SELECT dname, count(e.deptno) 인원
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
GROUP BY dname; 

-- outer join을 통해서 기준이 되는 테이블에 연결되는 데이터가 없더라도 표현할 수 있게 한다. 
SELECT e.*, dname, loc
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno; 

-- ex) EMP_CP100과 dept테이블을 outer 조인하여 부서번호 부서명 사원명 급여를 출력하세요
SELECT d.deptno, dname, ename, sal
FROM EMP_CP100 ec, dept d
WHERE ec.deptno(+) = d.deptno;

SELECT ec.deptno, dname, ename, sal
FROM EMP_CP100 ec, dept d
WHERE ec.deptno(+) = d.deptno; 

-- 사원 정보를 기준으로 매칭된 부서정보를 출력
SELECT *
FROM EMP_CP100 ec, dept d
WHERE ec.deptno = d.deptno(+);

📌 self join

1. 개요

1) 하나의 테이블 내에 있는 컬럼끼리 연결하는 조인이 필요한 경우에 사용된다.
2) 조인 대상 테이블이 자신 하나라는 것 외에는 equi join과 동일하다.

2. where절을 사용한 self join

1) 한 테이블에서 두 개의 컬럼을 연결하여 equi join
2) from 절에서 하나의 테이블에 테이블 별명 지정
예) 부서테이블에 where 절을 사용하여 self 조인 처리

3. 기본 형식

SELECT *
FROM 테이블 상위, 테이블 하위
WHERE 상위.상위컬럼 = 하위.하위컬럼

SELECT *
FROM emp;

SELECT e.empno, e.ename, e.mgr, m.empno, m.ename
FROM emp e, emp m
WHERE e.mgr = m.empno;

SELECT e.ename || '의 관리자는' || m.ename || '입니다!' show
FROM emp e, emp m
WHERE e.mgr = m.empno;

CREATE TABLE emp_family
AS SELECT empno NO, mgr refno, ename name, job role01 
FROM emp;

SELECT * 
FROM emp_family;

--ex) emp_family를 통해, self join으로 -- @@@(하위 name)는 @@(상위 name)의 자식입니다. 

SELECT s.name || '(' || s.role01 || ')는 ' || p.name || '(' || p.role01 || ')의 자식입니다!'
FROM emp_family s, emp_family p
WHERE s.refno = p.no;