[SQL-Oracle]JOIN 함수

🔘 TABLE JOIN : 두 개이상의 테이블에 저장된 행을 서로 결합하여 원하는 컬럼값을 검색하는 기능

1) EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여,부서번호 검색

SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP;

2) DEPT 테이블에 저장된 모든 부서의 부서번호,부서이름,부서위치 검색
SELECT DEPTNO,DNAME,LOC FROM DEPT;

SELECT DEPTNO,DNAME,LOC FROM DEPT;

--위에서 두 테이블에 있는 정보들을 검색하기 위해 SELECT를 두 번 썼는데, 이를 한 번에 나열하고 FROM 뒤에 두 테이블 이름을 모두 써봤다.

--EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색
--두 개이상의 테이블에 저장된 행을 결합하여 검색하기 위해서는 반드시 두 개이상의 테이블에 행을 결합하기 위한 조건을 제공하여 검색 처리
--카다시안 프로덕트(CATSIAN PRODUCT) : 두 개이상의 테이블의 행을 결합조건 없이 검색한 경우 발생되는 결과로 테이블의 모든 행을 교차 결합하여 검색 결과 제공

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP,DEPT;

 

이렇게 검색할 경우, 아래와 같이 전체사원들(14명)이 부서별로 구분되어 여러번 검색된다. 

하지만 우리가 원하는건 이게 아니라 각 사원들과 그 사원들이 속해있는 부서만 검색하고 싶다.

이 때, EQUI JOIN 을 사용해보았다. 

🔘 동등결합(EQUI JOIN) : 두 개이상의 테이블의 행을 결합하기 위한 조건에 = 연산자를 사용하여 조건이 참(TRUE)인 행만 결합하여 검색하며 결합조건이 맞지 않는 테이블의 행은 결합되지 않기 때문에 미검색 처리

--EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색
--결합조건 : EMP 테이블의 DEPTNO 컬럼값과 DEPT 테이블의 DEPTNO 컬럼값이 같은 행만 결합 
--결합조건은 WHERE의 조건식으로 표현하여 사용
--행을 결합하기 위한 테이블에 같은 이름의 컬럼이 존재하는 경우 [테이블명.컬럼명] 형식으로 표현하여 사용

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

 

EQUI JOIN 을 썼더니 EMP Table과 DEPT Table에 있는 부서번호를 매칭되어

사원 이름과 그 사원이 속해있는 부서의 이름&위치가 검색되었다. 

그래서 총 14행으로 깔끔하게 검색됨.

하지만 강사님이 이 방법은 속도가 오래 걸리므로 추천하진 않는다고 하심.

아래에서 배우는 JOIN함수를 사용 하라고 하셨다. 

 

🔶참고 🔶

--EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서번호,부서이름,부서위치 검색
--결합조건 : EMP 테이블의 DEPTNO 컬럼값과 DEPT 테이블의 DEPTNO 컬럼값이 같은 행만 결합
SELECT EMPNO,ENAME,SAL,EMP.DEPTNO,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO,ENAME,SAL,DEPT.DEPTNO,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

--검색대상에 같은 컬럼명이 존재할 경우 첫번째를 제외한 나머지 컬럼명은 자동으로 변경되어 검색
SELECT EMPNO,ENAME,SAL,EMP.DEPTNO,DEPT.DEPTNO,DNAME,LOC
	FROM EMP,DEPT
    WHERE EMP.DEPTNO=DEPT.DEPTNO;

--검색대상에 같은 컬럼명이 존재할 경우 컬럼 별칭을 사용하여 검색하는 것을 권장
SELECT EMPNO,ENAME,SAL,EMP.DEPTNO EMP_DEPTNO,DEPT.DEPTNO DEPT_DEPTNO,DNAME,LOC
	FROM EMP,DEPT
    WHERE EMP.DEPTNO=DEPT.DEPTNO;

🔶참고 🔶

TABLE ALIAS : 테이블명 대신 사용할 수 있는 별칭을 제공하는 기능 - 테이블의 이름을 일시적으로 변경 처리하여 사용
--형식) SELECT 검색대상,... FROM 테이블명 별칭,테이블명 별칭,...
--테이블의 행을 결합할 때 테이블의 이름을 간편하여 표현하거나 하나의 테이블을 다수의 테이블로 표현하기 위한 사용

SELECT EMPNO,ENAME,SAL,E.DEPTNO EMP_DEPTNO,D.DEPTNO DEPT_DEPTNO,DNAME,LOC
	FROM EMP E,DEPT D
    WHERE E.DEPTNO=D.DEPTNO;
    
--테이블에 별칭을 설정한 후 별칭 대신 실제 테이블명을 사용하면 에러 발생
SELECT EMPNO,ENAME,SAL,EMP.DEPTNO EMP_DEPTNO,DEPT.DEPTNO DEPT_DEPTNO,DNAME,LOC
	FROM EMP E,DEPT D
    WHERE E.DEPTNO=D.DEPTNO;

🔘 비동등결합(NON-EQUI JOIN) : 두 개이상의 테이블의 행을 결합하기 위한 조건에 = 연산자외에 다른 연산자를 사용하여 조건이 참(TRUE)인 행만 결합하여 검색하며 결합조건이 맞지 않는 테이블의 행은 결합되지 않기 때문에 미검색 처리

--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,급여 검색
SELECT EMPNO,ENAME,SAL FROM EMP;

--SALGRADE 테이블에 저장된 모든 급여등급의 등급번호,최소급여,최대급여 검색
SELECT GRADE,LOSAL,HISAL FROM SALGRADE;

--EMP 테이블과 SALGRADE 테이블에 저장된 모든 사원번호,사원이름,급여,등급번호 검색
--결합조건 : EMP 테이블의 SAL 컬럼값이 SALGRADE 테이블의 LOSAL 컬럼값부터 HISAL 컬럼값 범위에 포함되는 행만 결합
SELECT EMPNO,ENAME,SAL,GRADE FROM EMP,SALGRADE WHERE SAL>=LOSAL AND SAL<=HISAL;
SELECT EMPNO,ENAME,SAL,GRADE FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL;

 

🔘 외부결합(OUTER JOIN) : 두 개이상의 테이블에서 결합조건에 맞는 행을 결합하여 검색하고 결합조건이 맞지 않은 행은 NULL과 결합하여 검색
--결합 조건식의 테이블에 (+) 기호를 사용하면 결합조건이 맞지 않은 행을 NULL과 결합하여 검색

--EMP 테이블에 저장된 모든 사원의 부서번호를 중복되지 않는 유일한 하나의 값만 검색
SELECT DISTINCT DEPTNO FROM EMP;--검색결과 : 10,20,30

--DEPT 테이블에 저장된 모든 부서의 부서번호,부서이름,부서위치 검색
SELECT DEPTNO,DNAME,LOC FROM DEPT;--검색결과 : 10,20,30,40

--EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색
--결합조건 : EMP 테이블의 DEPTNO 컬럼값과 DEPT 테이블의 DEPTNO 컬럼값이 같은 행만 결합
--40번 부서에 근무하는 사원은 존재하지 않기 때문에 40번 부서에 대한 부서이름과 부서위치 미검색
SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

--사원이 존재하지 않는 부서정보도 검색하기 위해 결합 조건식에서 EMP 테이블에 (+) 기호를 붙여 외부결합으로 검색
--40번 부서에 근무하는 사원이 없는 경우 EMP 테이블에 결합될 행이 없으므로 사원정보의 컬럼값 대신 NULL과 결합되어 검색
SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO(+)=DEPT.DEPTNO;

 

🔘 자기결합(SELF JOIN) : 하나의 테이블에 서로 다른 별칭을 부여하여 2개 이상의 테이블로 구분하여 행을 결합해 검색
--결합되는 두 테이블의 컬럼명이 동일하므로 테이블의 별칭을 사용하여 명확하게 테이블을 구분하여 컬럼명 설정

--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,관리자번호(관리자의 사원번호) 검색
SELECT EMPNO,ENAME,MGR FROM EMP;

--EMP 테이블에 저장된 모든 사원의 사원번호,사원이름,관리자번호(관리자의 사원번호),관리자이름(관리자의 사원이름) 검색
--결합조건 : EMP 테이블(WORKER)의 MGR 컬럼값과 EMP 테이블(MANAGER)의 EMPNO 컬럼값이 같은 행만 결합하여 검색
--EMP 테이블(WORKER)의 MGR 컬럼값이 NULL인 사원은 결합조건이 맞지 않아 미검색
SELECT WORKER.EMPNO,WORKER.ENAME WORKER_ENAME,WORKER.MGR,MANAGER.ENAME MANAGER_ENAME 
    FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR=MANAGER.EMPNO;

--EMP 테이블(WORKER)의 MGR 컬럼값이 NULL인 사원도 검색되도록 외부결합 사용
SELECT WORKER.EMPNO,WORKER.ENAME WORKER_ENAME,WORKER.MGR,MANAGER.ENAME MANAGER_ENAME 
    FROM EMP WORKER, EMP MANAGER WHERE WORKER.MGR=MANAGER.EMPNO(+);

--EMP 테이블과 DEPT 테이블에서 SALES 부서에 근무하는 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색
--결합조건 : EMP 테이블의 DEPTNO 컬럼값과 DEPT 테이블의 DEPTNO 컬럼값이 같은 행만 결합
SELECT EMPNO,ENAME,SAL,DNAME,LOC
	FROM EMP,DEPT
    WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME='SALES';

 

---

--위에서 배운 것과 같이 결합조건과 행조건을 WHERE에서 같이 사용하므로 유지보수의 불편함 존재
--1999년에 만들어진 표준 SQL(ANSI-SQL)에서는 테이블의 행을 결합하여 검색하기 위한 기능 제공 

=>즉, 이 때 JOIN이라는 함수를 만들어주었다.

🔘 CROSS JOIN : 결합 테이블의 모든 행을 교차 결합하여 검색 - 결합조건 생략
--형식) SELECT 검색대상,... FROM 테이블명1 CROSS JOIN 테이블명2

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP CROSS JOIN DEPT;

 

🔘 NATURAL JOIN : 결합 테이블에 같은 이름의 컬럼이 하나만 있는 경우 같은 이름의 컬럼의 저장된 값이 같은 행만 결합하여 검색 - 결합조건 생략
--형식) SELECT 검색대상,... FROM 테이블명1 NATURAL JOIN 테이블명2

--EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색
--결합조건 : EMP 테이블의 DEPTNO 컬럼값과 DEPT 테이블의 DEPTNO 컬럼값이 같은 행만 결합
--NATURAL JOIN 기능을 사용하면 EMP 테이블의 DEPTNO 컬럼값과 DEPT 테이블의 DEPTNO 컬럼값을 사용해 자동으로 결합되므로 결합조건 생략
SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP NATURAL JOIN DEPT;

--EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서번호,부서이름,부서위치 검색
--NATURAL JOIN 기능을 사용하면 결합 테이블에 같은 이름의 컬럼을 검색할 때 테이블을 명확히 구분하지 않아도 검색 가능
SELECT EMPNO,ENAME,SAL,DEPTNO,DNAME,LOC FROM EMP NATURAL JOIN DEPT;

🔘 JOIN USING : 결합 테이블에 같은 이름의 컬럼이 2개 이상 있는 경우, 같은 이름의 컬럼 중 하나를 지정하여 지정된 컬럼에 저장된 값이 같은 행만 결합하여 검색 - 결합조건 생략
--형식) SELECT 검색대상,... FROM 테이블명1 JOIN 테이블명2 USING(컬럼명)

SELECT EMPNO,ENAME,SAL,DEPTNO,DNAME,LOC FROM EMP JOIN DEPT USING(DEPTNO);

 

아래 INNER JOIN 또는 OUTER JOIN을 많이 쓴다.
🔘 INNER JOIN : 결합조건을 제공받아 결합조건이 참(TRUE)인 행만 결합하여 검색 - INNER 키워드 생략 가능
--형식) SELECT 검색대상,... FROM 테이블명1 INNER JOIN 테이블명2 ON 결합조건식

--EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색
--결합조건 : EMP 테이블의 DEPTNO 컬럼값과 DEPT 테이블의 DEPTNO 컬럼값이 같은 행만 결합

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

--EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서번호,부서이름,부서위치 검색
--결합조건 : EMP 테이블의 DEPTNO 컬럼값과 DEPT 테이블의 DEPTNO 컬럼값이 같은 행만 결합
--결합 테이블에 같은 이름의 컬럼이 있는 경우 반드시 테이블을 명시하여 컬럼을 표현하여 사용

SELECT EMPNO,ENAME,SAL,EMP.DEPTNO,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO,ENAME,SAL,DEPT.DEPTNO,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

--EMP 테이블과 SALGRADE 테이블에서 모든 사원의 사원번호,사원이름,급여,등급번호 검색
--결합조건 : EMP 테이블의 SAL 컬럼값이 SALGRADE 테이블의 LOSAL 컬럼값부터 HISAL 컬럼값 범위에 포함되는 행만 결합

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

2개의 테이블 조인
--EMP 테이블과 DEPT 테이블에서 SALES 부서에 근무하는 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색
--결합조건 : EMP 테이블의 DEPTNO 컬럼값과 DEPT 테이블의 DEPTNO 컬럼값이 같은 행만 결합
SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE DNAME='SALES';

SELECT EMPNO,ENAME,SAL,DNAME,LOC
	FROM EMP
    JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO
    WHERE DNAME='SALES';

3개의 테이블 조인
--EMP 테이블과 DEPT 테이블, SALGRADE 테이블에서 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치,등급번호 검색
--결합조건-1 : EMP 테이블의 DEPTNO 컬럼값과 DEPT 테이블의 DEPTNO 컬럼값이 같은 행만 결합
--결합조건-2 : EMP 테이블의 SAL 컬럼값이 SALGRADE 테이블의 LOSAL 컬럼값부터 HISAL 컬럼값 범위에 포함되는 행만 결합

SELECT EMPNO,ENAME,SAL,DNAME,LOC,GRADE
	FROM EMP
    JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO 
    JOIN SALGRADE ON SAL BETWEEN LOSAL AND HISAL;

🔘 OUTER JOIN : 결합조건이 맞는 행만 결합하여 검색하는 것이 아니라 결합조건이 맞지 않는 행을 NULL과 결합하여 검색 - OUTER 키워드는 생략 가능
--형식) SELECT 검색대상,... FROM 테이블명1 {LEFT|RIGHT|FULL} OUTER JOIN 테이블명2 ON 결합조건
--LEFT OUTER JOIN : JOIN 키워드를 기준으로 왼쪽에 위치한 테이블의 모든 행을 검색 처리
--RIGHT OUTER JOIN : JOIN 키워드를 기준으로 오른쪽에 위치한 테이블의 모든 행을 검색 처리
--FULL OUTER JOIN : JOIN 키워드를 기준으로 왼쪽과 오른쪽에 위치한 테이블의 모든 행을 검색 처리

--EMP 테이블과 DEPT 테이블에서 모든 사원의 사원번호,사원이름,급여,부서이름,부서위치 검색
--결합조건 : EMP 테이블의 DEPTNO 컬럼값과 DEPT 테이블의 DEPTNO 컬럼값이 같은 행만 결합
--40번 부서에 근무하는 사원이 없으므로 40번 부서정보 미검색

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

--OUTER JOIN 기능을 사용하여 검색 - 40번 부서에 근무하는 사원이 없어도 사원정보 대신 NULL과 결합하여 검색

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP LEFT JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;--실행오류
SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP RIGHT JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP FULL JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;