Oracle DB #6 조인 , 서브 쿼리

1.조인

1) 조인의 필요성

- 원하는 정보가 두 개 이상의 테이블에 나누어져 있을 때 여러 번의 질의를 하는 번거로움을 줄이고자 두 개 이상의 테이블을 결합해야만 원하는 결과를 얻을 수 있을 때 한 번의 질의로 원하는 결과를 얻을 수 있는 조인기능을 제공

 

2) Cross Join

- Cross Join으로 특별한 키워드 없이 SELECT 문의 FROM 절에 사원(EMP) 테이블과 부서(DEPT) 테이블을 콤마로 연결하여 연속하여 기술하는 것

SELECT *

 FROM EMP, DEPT;

- Cross Join의 결과 얻어지는 컬럼의 수는 사원 테이블의 컬럼의 수(8)와 부서 테이블의 컬럼의 수를 더한 것이므로 11이 됨. 로우 수는 사원 한명에 대해서 DEPT 테이블의 4개의 로우와 결합되기에 56개(14*4)가 됨.

 Cross Join의 결과를 보면 사원 테이블에 부서에 대한 상세정보가 결합되긴 했지만, 조인 될 때 아무런 조건을 제시하지 않았기에 사원 한명에 대해서 DEPT 테이블의 4개의 로우와 결합된 형태이기에 Cross Join의 결과는 아무런 의미를 갖지 못함

=> 조인 결과가 의미를 갖으려면 조인할 때 조건을 지정해야 함.

 

3) Equi Join

- EQUI JOIN은 가장 많이 사용하는 조인 방법으로서 조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결하여 결과를 생성하는 조인 방법

 

사원 정보를 출력할 때 각 사원들이 소속된 부서의 상세 정보를 출력하기 위해서 두 개의 테이블을 조인한 예

SELECT *

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO;

 

- 사원(EMP) 테이블과 부서(DEPT) 테이블의 공통 컬럼인 DEPTNO의 값이 일치(=)되는 조건을 WHERE 절에 기술하여 사용

- 테이블을 조인하려면 일치되는 공통 컬럼을 사용해야 한다고 하였음. 컬럼의 이름이 같게 되면 혼동이 오기 때문에 컬럼 이름 앞에 테이블 이름을 기술

 

- Equi Join에 AND 연산하기

SELECT ENAME, DNAME

FROM EMP, DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO

AND ENAME='SCOTT';

- 컬럼명의 모호성 해결

- 동일한 이름의 컬럼은 컬럼 명 앞에 테이블 명을 명시적으로 기술함으로서 컬럼이 어느 테이블 소속인지 구분할 수 있게 함

SELECT EMP.ENAME, DEPT.DNAME, EMP.DEPTNO

FROM EMP, DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO

AND ENAME='SCOTT';

- 테이블에 별칭 부여하기

- 테이블 이름에 별칭을 붙이는 방법은 FROM 절 다음에 테이블 이름을 명시하고 공백을 둔 다음에 별칭을 지정하면 됨

SELECT E.ENAME, D.DNAME, E.DEPTNO, D.DEPTNO

FROM EMP E, DEPT D

WHERE E.DEPTNO = D.DEPTNO

AND E.ENAME='SCOTT';

 

 

4) Non-Equi Join

- Non-Equi Join은 조인 조건에 특정 범위 내에 있는지를 조사하기 위해서 WHERE 절에 조인 조건을 = 연산자 이외의 비교 연산자를 사용

 

급여 등급을 5개로 나누어 놓은 salgrade에서 정보를 얻어 와서 각 사원의 급여 등급을 지정해보도록 하자. 이를 위해서 사원(emp) 테이블과 급여 등급(salgrade) 테이블을 조인하도록 하자. 사원의 급여가 몇 등급인지 살펴보는 예제이다.

SELECT ENAME, SAL, GRADE

FROM EMP, SALGRADE

WHERE SAL BETWEEN LOSAL AND HISAL;

 

5) Self Join

- 조인은 두 개 이상의 서로 다른 테이블을 서로 연결하는 것뿐만 아니라, 하나의 테이블 내에서 조인을 해야만 원하는 자료를 얻는 경우가 생김

- 셀프 조인이란 말 그대로 자기 자신과 조인을 맺는 것을 말함

 

6) Outer Join

- 조인 조건에 만족하지 못하였더라도 해당 로우를 나타내고 싶을 때에 사용하는 것이 외부 조인(Outer Join)

- 외부 조인은 NULL 값이기에 배제된 행을 결과에 포함시킬 수 있으며 다음과 같이 “(+)” 기호를 조인 조건에서 정보가 부족한 칼럼 이름 뒤에 덧붙임

 

SELECT employee.ename || ‘의 매니저는 ’

             || manager.ename || ‘입니다.’

FROM emp employee, emp manager

WHERE employee.mgr = manager.empno(+);

 

7) ANSI Cross Join

SELECT * FROM EMP CROSS JOIN DEPT;

 

- ANSI Inner Join

- 새로운 ANSI 조인은 FROM 다음에 INNER JOIN 이란 단어를 사용하여 조인할 테이블 이름을 명시하고 ON 절을 사용하여 조인 조건을 명시하여 다음과 같이 작성함

- ANSI 조인에서는 조인 정보를 ON절에 기술하여 조인 조건을 명확하게 지정하고 다른 조건에 대해서는 WHERE 구문에서 지정하면 됨

SELECT ENAME, DNAME

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO=DEPT.DEPTNO

WHERE ENAME='SCOTT';

 

- USING을 이용한 조인 조건 지정하기

- 두 테이블에 각각 조인을 정의한 컬럼의 이름이 동일하다면 USING 절에서 조인할 컬럼을 지정하여 구문을 더 간단하게 표현할 수 있음

SELECT * FROM table1 JOIN table2

USING (공통컬럼)

 

- NATURAL Join

- 두 테이블에 각각 조인을 정의한 컬럼의 이름이 동일하다면 USING 절에서 조인할 컬럼을 지정하여 구문을 더 간단하게 표현할 수 있음

SELECT * FROM table1 NATURAL JOIN table2

 

- ANSI Outer Join

- 새로운 ANSI 구문에서 Outer Join은 LEFT Outer Join, RIGHT Outer Join 그리고 FULL Outer Join 세 가지 타입의 조인을 제공

- 어느 한쪽 테이블에는 해당하는 데이터가 존재하는데 다른 쪽 테이블에는 데이터가 존재하지 않을 경우 그 데이터가 출력되지 않는 문제점을 해결하기 위해 사용하는 조인 기법

SELECT * FROM table1

[LEFT | RIGHT | FULL] Outer Join table2

 

2. 서브 쿼리

1) 서브 쿼리의 기본 개념

- 서브 쿼리는 하나의 SELECT 문장의 절 안에 포함된 또 하나의 SELECT 문장

- 그렇기에 서브 쿼리를 포함하고 있는 쿼리문을 메인 쿼리, 포함된 또 하나의 쿼리를 서브 쿼리라 함

- 서브 쿼리는 비교 연산자의 오른쪽에 기술해야 하고 반드시 괄호로 둘러쌓아야 함

- 서브 쿼리는 메인 쿼리가 실행되기 이전에 한번만 실행이 됨

 

단일 행 서브 쿼리

- 단일 행(Single Row) 서브 쿼리는 수행 결과가 오직 하나의 로우(행, row)만을 반환하는 서브 쿼리를 갖는 것을 말함

- 단일 행 서브 쿼리문에서는 이렇게 오직 하나의 로우(행, row)로 반환되는 서브 쿼리의 결과는 메인 쿼리에 보내게 되는데 메인 쿼리의 WHERE 절에서는 단일 행 비교 연산자인 =, >, >=, <, <=, <>를 사용

 

2) 서브 쿼리에서 그룹 함수의 사용

- 평균 급여를 구하는 쿼리문을 서브 쿼리로 사용하여 평균 급여보다 더 많은 급여를 받는 사원을 검색해보자

SELECT ENAME, SAL

FROM EMP

WHERE SAL > ( SELECT AVG(SAL)

              FROM EMP);

3) 다중 행 서브 쿼리

- 다중 행 서브 쿼리는 서브 쿼리에서 반환되는 결과가 하나 이상의 행일 때 사용하는 서브 쿼리임. 다중 행 서브 쿼리는 반드시 다중 행 연산자(Multiple Row Operator)와 함께 사용해야 함

종류	의미
IN	메인 쿼리의 비교 조건(‘=’ 연산자로 비교할 경우)이 서브 쿼리의 결과 중에서 하나라도 일치하면 참입니다.
ANY, SOME	메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상이 일치하면 참입니다.
ALL	메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참입니다.
EXIST	메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참입니다.

 

a. IN

- 주어진 문제가 3000 이상 받는 사원이 소속된 부서(10번, 20번)와 동일한 부서에서 근무하는 사원이기에 서브 쿼리의 결과 중에서 하나라도 일치하면 참인 결과를 구하는 IN 연산자와 함께 사용되어야 함.

 

SELECT ENAME, SAL, DEPTNO

FROM EMP

WHERE DEPTNO IN ( SELECT DISTINCT DEPTNO

FROM EMP

WHERE SAL>=3000);

 

b. ALL

- ALL 조건은 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참

- 찾아진 값에 대해서 AND 연산을 해서 모두 참이면 참이 되는 셈이 됨. > ALL 은 “모든 비교값 보다 크냐”고 묻는 것이 되므로 최대값보다 더 크면 참

 

30번 소속 사원들 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사람의 이름, 급여를 출력하는 쿼리문을 작성해보자.

SELECT ENAME, SAL

FROM EMP

WHERE SAL > ALL(SELECT SAL

                FROM EMP

                WHERE DEPTNO =30);

c. ANY

- ANY 조건은 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상만 일치하면 참입니다.

- ANY는 찾아진 값에 대해서 하나라도 크면 참이 되는 셈이 됩니다. 그러므로 찾아진 값 중에서 가장 작은 값 즉, 최소값 보다 크면 참이 됩니다.

 

부서번호가 30번인 사원들의 급여 중 가장 작은 값(950)보다 많은 급여를 받는 사원의 이름, 급여를 출력하는 예제를 작성해보자

SELECT ENAME, SAL

FROM EMP

WHERE SAL > ANY ( SELECT SAL

                  FROM EMP

                  WHERE DEPTNO = 30 );