오라클(Oracle) - (DQL) : 조인(JOIN)

JOIN

• 두 개 이상의 테이블에서 데이터를 가져와서 연결하는데 사용되는 SQL 기능
• 테이블의 식별 값인 Primary Key와 테이블 간 공통 값인 Foreign Key 값을 사용하여 조인

INNTER JOIN : 두 테이블에서 일치하는 데이터만 선택

LEFT JOIN : 왼쪽 테이블의 모든 데이터와 오른쪽 데이터에서 일치하는 데이터를 선택

RIGHT JOIN : 오른쪽 테이블의 모든 데이터와 왼쪽 데이터에서 일치하는 데이터를 선택

---

✅ 1대N, N대1, 1대1 등 어느 테이블을 기준으로 보느냐에 따라 부르는 말이 다름.

      예) EMP 테이블의 DEPTNO 와 DEPT 테이블의 DEPTNO는 EMP 테이블을 기준으로 할 때에는 1대N

✅ Primary Key는 테이블에서 가장 중요한 값. 즉, 고유한 값이 있는 컬럼 (예. EMP 테이블 - EMPNO)

     => NOT NULL, UNIQUE

✅ Foreign Key는 다른 테이블과 연결될 때 참조할 수 있는 값 (예. EMP 테이블 - DEPTNO / DEPT테이블 - DEPTNO)

     => Primary Key는 동시에 다른 테이블의 Foreign Key가 될 수 있음.

✅ 추후 자바와 연결할 때.. DEPT 테이블을 클래스라고 생각하면 DEPTNO는 인스턴스 필드(ArrayList 타입)..

      DEPTNO를 통해 EMP 객체를 불러오는 형태로 설계하게 됨. (JAVA 입출력 스트림 예제 참조!!)

 

유의사항

⬇️ EMP 테이블과 DEPT 테이블을 조인하고, 모든 열을 선택한 후 반환하는 쿼리..

SELECT *
	FROM EMP, DEPT;

 

이하 생략..더 많음

🐸 EMP와 DEPT 테이블이 어떻게 조인되는지에 대한 조건이 지정되지 않았기 때문에, CROSS JOIN(카테시안 곱)으로 간주되어 EMP 테이블의 모든 레코드와 DEPT 테이블의 모든 레코드가 결합됨.

🐶 ???? 개굴.

🐸 DEPT의 DEPTNO 컬럼은 4개. EMP 컬럼은 총 12개(=12명).

      DEPT의 DEPTNO가 EMP 테이블의 모든 데이터와 결합되어 12 * 4 (카테시안 곱) 개의 행이 출력됨.

 

📌 필자는 이 부분이 이해가 잘 안가서 Java의 이중 for문과 비슷하다고 이해했음.

 

따라서, 원하는 데이터를 출력하려면 열 이름을 비교하는 조건식으로 조인해야 한다.

⚠️ 오라클 문법

SELECT *
	FROM EMP, DEPT
	WHERE EMP.DEPTNO = DEPT.DEPTNO
	ORDER BY EMPNO;

⚠️ ANSI 문법 (미국 표준 문법)

SELECT *
	FROM EMP E JOIN DEPT D
	ON E.DEPTNO = D.DEPTNO
	ORDER BY EMPNO;

🐸 EMP 테이블의 부서 번호와 DEPT 테이블의 부서 번호가 일치하는 레코드만 선택된다.

     이후 모든 열을 선택하고, EMPNO 기준으로 정렬하여 결과 반환!

     이 때, ⭐테이블에 별칭을 설정해야해. 아주 중요하다.

테이블 별칭 설정

• 양쪽 테이블 모두에 존재하는 열에 대해서는 테이블 이름을 명시해야 함.
  (Java의 this 같은??)
• 근데 테이블 이름이 길면(예.DEPARTMENTNUM) 타이핑이 길어져 가독성이 떨어지니 줄임말(별칭) 지정

SELECT EMPNO, ENAME, E.DEPTNO, DNAME, JOB
	FROM EMP E, DEPT D -- 오라클 조인 방식
	WHERE E.DEPTNO = D.DEPTNO AND E.JOB = 'MANAGER';

등가 조인 (INNER JOIN)

• 테이블을 연결한 후에 출력 행을 각 테이블의 특정 열과 일치한 데이터를 기준으로 선정하는 방식
• ANSI 조인 방식으로 연결
  ⚠️ 오라클 9까지는 오라클 조인만 사용할 수 있었으나, 오라클 10부터는 ANSI 조인도 사용 가능해짐.

SELECT EMPNO, ENAME, E.DEPTNO, DNAME, JOB
	FROM EMP E JOIN DEPT D
	ON E.DEPTNO = D.DEPTNO
	WHERE E.JOB = 'MANAGER';

🐸기본 예제

🐸 EMP와 DEPT 테이블을 조인하고, 급여가 3000 이상인 사원 정보 출력

(사원번호, 이름, 급여, 입사일, 부서번호, 부서이름)

 

(1) 오라클 문법

SELECT EMPNO, ENAME, SAL, HIREDATE, E.DEPTNO, DNAME
	FROM EMP E, DEPT D
	WHERE E.DEPTNO = D.DEPTNO AND SAL >= 3000;

(2) ANSI 문법

SELECT EMPNO, ENAME, SAL, HIREDATE, E.DEPTNO, DNAME
	FROM EMP E JOIN DEPT D
	ON E.DEPTNO = D.DEPTNO
	WHERE E.SAL >= 3000;

🐸 EMP 테이블 별칭을 E로, DEPT 테이블 별칭은 D로 하여 다음과 같이 등가 조인을 했을 때,

급여가 2500 이상 3500 이하인 사원의 정보가 출력되도록 작성

 

(1) 오라클 문법

SELECT EMPNO, ENAME, SAL, E.DEPTNO
	FROM EMP E, DEPT D
	WHERE E.DEPTNO = D.DEPTNO
	AND SAL BETWEEN 2500 AND 3500;

(2) ANSI 문법

SELECT EMPNO, ENAME, SAL, E.DEPTNO
	FROM EMP E JOIN DEPT D
	ON E.DEPTNO = D.DEPTNO
	WHERE SAL BETWEEN 2500 AND 3500;

비등가 조인

• 동일 열이 아닌 다른 조건을 사용하여 조인할 때 사용하나..자주 사용되지 않음.

🐸 예를 들어..사원들의 급여 등급을 표시하기 위해서는 SAL과 SALGRADE가 일치할 수 없으므로 (아래 테이블 참조)

      SAL은 LOSAL / HISAL 사이에 있어야 한다.

      이런 경우에는 BETWEEN A AND B 연산자를 사용하면 쉽게 처리 가능. 

SELECT * FROM EMP;

SELECT * FROM SALGRADE;

---

SELECT ENAME, SAL, GRADE
	FROM EMP E JOIN SALGRADE S
	ON SAL BETWEEN LOSAL AND HISAL;

자체 조인

• 같은 테이블을 두 번 사용하는 것

🐸 EMP 테이블에서 MGR이 있는 사람 중 각 사원의 MGR 이름을 조회 후 출

SELECT E1.EMPNO, E1.ENAME, E1.MGR,
	E2.EMPNO AS MGR_EMPNO,
	E2.ENAME AS MGR_ENAME
	FROM EMP E1 JOIN EMP E2
	ON E1.MGR = E2.EMPNO;

외부 조인 (OUTER JOIN)

• 내부 조인(INNER JOIN, 등가 조인)의 경우, 한쪽의 컬럼에 값이 없다면 조회되지 않는다.
• 그러나 외부 조인은 공통되지 않은 컬럼도 표시 합니다.

⚠️ 오라클 문법 : (+) 기호를 붙이면 데이터 값이 부족한 테이블에 NULL 값을 갖는 행이 생성되어 데이터 값이 충분한 테이블의 행들이 NULL행에 조인

SELECT ENAME, E.DEPTNO, DNAME
	FROM EMP E, DEPT D
	WHERE E.DEPTNO(+) = D.DEPTNO
	ORDER BY E.DEPTNO;

⚠️ ANSI 문법 (LEFT OUTER, RIGHT OUTER)

OPERATIONS에 값이 없는데도 조회됨 확인.(RIGHT OUTER. !!오른쪽 테이블 기준으로 조회!!)

SELECT ENAME, E.DEPTNO, DNAME
	FROM EMP E RIGHT OUTER JOIN DEPT D
	ON E.DEPTNO = D.DEPTNO
	ORDER BY E.DEPTNO;

NATURAL JOIN

• 동등 조인과 비슷하지만 WHERE 조건절 없이 조인하는 방식
• 두 테이블의 동일한 이름(컬럼 또는 열)을 갖는 컬럼은 모두 조인 됨

SELECT EMPNO, ENAME, DNAME
	FROM EMP NATURAL JOIN DEPT;

일반적인 동등조인인 경우는 DEPTNO가 어디 소속인지 모호하여 위 쿼리문처럼 작성하면 에러가 발생합니다.

JOING ~ USING

• 기존의 등가 조인을 대신하는 조인 방식
• FROM table1 JOIN table2 USING(기준열)

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME, LOC
	FROM EMP JOIN DEPT USING(DEPTNO)
	WHERE SAL >= 3000
	ORDER BY DEPTNO, EMPNO;

JOIN ~ ON

• 가장 범용성 있는 JOIN ~ ON 키워드를 사용한 조인 방식
• FROM table1 JOIN table2 ON (조인 조건식)

SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, E.DEPTNO, DNAME, LOC
	FROM EMP E JOIN DEPT D
	ON E.DEPTNO = D.DEPTNO
	WHERE SAL <= 3000
	ORDER BY E.DEPTNO, EMPNO;

연습 문제

🐸 급여(SAL)가 2000 초과인 사원들의 부서 정보, 사원 정보를 부서번호, 부서이름, 사원이름, 급여 표시

 - 오라클 문법과 ANSI 문법으로 표시(NATURAL JOIN, JOIN ~ ON, JOIN ~ USING)

• 오라클 문법

SELECT E.DEPTNO, DNAME, ENAME, SAL
	FROM EMP E, DEPT D
	WHERE E.DEPTNO = D.DEPTNO
	AND SAL > 2000;

• NATURAL JOIN

SELECT DEPTNO, DNAME, ENAME, SAL
	FROM EMP NATURAL JOIN DEPT
	WHERE SAL > 2000;

• JOIN ~ ON

SELECT E.DEPTNO, DNAME, ENAME, SAL
	FROM EMP E JOIN DEPT D
	ON E.DEPTNO = D.DEPTNO
	WHERE SAL > 2000;

• JOIN  ~ USING 

SELECT DEPTNO, DNAME, ENAME, SAL
	FROM EMP JOIN DEPT USING(DEPTNO)
	WHERE SAL > 2000;

🐸 각 부서별 부서번호, 평균 급여, 최대 급여, 최소 급여, 사원 수 출력

 - 오라클 문법과 ANSI 문법으로 표시(NATURAL JOIN, JOIN ~ ON, JOIN ~ USING)

• 오라클 문법

SELECT E.DEPTNO, TRUNC(AVG(SAL)), MAX(SAL), MIN(SAL), COUNT(*)
	FROM EMP E, DEPT D
	WHERE E.DEPTNO = D.DEPTNO
	GROUP BY E.DEPTNO;

• NATURAL JOIN

SELECT DEPTNO, TRUNC(AVG(SAL)), MAX(SAL), MIN(SAL), COUNT(*)
	FROM EMP NATURAL JOIN DEPT
	GROUP BY DEPTNO;

• JOIN ~ ON

SELECT E.DEPTNO, TRUNC(AVG(SAL)), MAX(SAL), MIN(SAL), COUNT(*)
	FROM EMP E JOIN DEPT D
	ON E.DEPTNO = D.DEPTNO
	GROUP BY E.DEPTNO;

• JOIN ~ USING

SELECT DEPTNO, TRUNC(AVG(SAL)), MAX(SAL), MIN(SAL), COUNT(*)
	FROM EMP JOIN DEPT USING(DEPTNO)
	GROUP BY DEPTNO;

🐸 모든 부서 정보와 사원 정보를 부서번호, 부서 이름, 사원번호, 사원이름, 직책, 금여를 사원 이름순으로 출력

- 오라클 문법과 ANSI 문법

• 오라클 문법

SELECT E.DEPTNO, DNAME, EMPNO, ENAME, JOB, SAL
	FROM EMP E, DEPT D
	WHERE E.DEPTNO(+) = D.DEPTNO
	ORDER BY ENAME;

• ANSI 문법

SELECT E.DEPTNO, DNAME, EMPNO, ENAME, JOB, SAL
	FROM EMP E RIGHT JOIN DEPT D
	ON E.DEPTNO = D.DEPTNO
	ORDER BY ENAME;