JOIN, SUB QUERY의 효율 비교, ANY/ALL

위의 결과는 EMP와 DEPT 테이블로부터 부서번호와 부서이름, 직원이름,  급여를 나타낸 테이블이다. 이를 검색하는 쿼리는 다음과 같다.

--1992 SQL
SELECT D.DEPTNO, D.DNAME,E.ENAME, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

위의 쿼리문은 다음과 같이 사용하여도 된다.

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

위의 두 쿼리문의 차이점은 FROM 절의 두 테이블의 위치가 바뀐 것 외에는 없다. 그렇다면 두 쿼리문 중에 어느것이 효율이 좋을까? DATA가 큰 테이블을 FROM절과 가깝게 두는 것이 효과가 좋다. 일반적으로 두 테이블의 관계가 부모와 자식으로 표현될 때, 자식 테이블이 크므로 자식테이블이 FROM절에 가까운 것이 좋다고 볼 수 있다. 기본적으로 FROM에 가까운 절을 메모리에 올린 후, 다음 테이블 - 1999 SQL에선 JOIN 다음 테이블 - 을 가져오므로, 먼저 메모리에 올라가 있는 테이블이 상대적으로 접근이 쉽다.

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

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

그렇다면 위의 두 쿼리문과 같이 SELECT 문의 COLUMN의 경우엔 어느 것이 효율성이 좋은가. 두 쿼리문 모두 결과값은 이상이 없이 같은 결과를 보여준다. 하지만 이 경우, SELECT문에서 필요한 DATA를 가져올 때 테이블의 명을 정의 해 놓았다면 해당 테이블에 직접 접근하여 가져오지만, 없다면 해당 COLUMN이 있는 테이블을 찾아야 하기 때문이다.

NATURAL JOIN
다음의 쿼리문은 처음의 값과 같은 결과를 가지는 NATURAL JOIN이다.

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

NATURAL JOIN의 경우, 사용하기에 간편하다. 물론, 사용할 때에는 두 테이블간에 ALIAS 가 있으면 오류가 되고, 두 테이블 간 공통 컬럼이 1개만 있어야 한다는 제약조건이 있기는 하지만, 그외의 것에 자세하게 설명할 필요가 없다.

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

위의 쿼리와 같이 USING을 사용하여 공통된 COLUMN을 지정하여 보일 수도 있다. 하지만 NATURAL JOIN의 경우, 필요한 DATA를 가져오기 위해서는 전부를 검색한다는 문제로 효율성이 떨어진다.

SUB QUERY : DATA처리가 용의하도록 QUERY의 처리 결과를 사용하여 검색, 요약 한다.
EMP테이블의 값을 다음과 같이 UPDATE 해보자.

UPDATE EMP SET SAL =950
WHERE EMPNO = 7876;
UPDATE EMP SET SAL = 1300
WHERE EMPNO = 7499;

SELECT * FROM EMP;

이 EMP에서 부서번호 10 에 속한 사원중 SAL이 가장 최저인 사람의 사원번호, 사원명, 급여를 가져오자.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO =10 AND SAL = (
SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO = 10);

위의 테이블에서 필요한 값을 가져오기 위해 그룹 함수 MIN을 이용하여 최소 SAL의 정보를 가져왔다. 이를 전체에서 찾아 해당 정보를 보여주는 식으로 간단하게 처리가 가능하게 해준다. 

이번엔 MGR 컬럼에 등록된 사원들의 부서번호, 사원번호, 사원명, 급여를 보여보자.

SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP
WHERE EMPNO IN (SELECT DISTINCT MGR
  FROM EMP) ;

SELECT DISTINCT E1.DEPTNO,E1.EMPNO,E1.ENAME,E1.SAL
FROM EMP E1 JOIN EMP E2
ON E1.EMPNO = E2.MGR
WHERE E1.EMPNO = E2.MGR;

위의 쿼리의 결과는 순서는 다르지만 결과적으로 같은 내용을 나타낸다. 하지만 위의 쿼리문과 다음 쿼리문의 경우의 효율은 어느것이 좋은지 생각해 보자. 서브쿼리는 RECORD를 가져오려 할 때마다 서브쿼리가 실행된 별도의 값에서 필요한 값을 찾아서 처리해야 한다. 하지만 JOIN의 경우는 조회하게 될 DATA를 정리하여 그 안에서 필요한 조건에 대한 처리를 하게 되므로 효율성은 JOIN이 좋게 된다.

이번엔 MGR 컬럼에 등록되지 않은 사원들의 부서번호, 사원번호, 사원명, 급여를 가져오자.

(SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP)
MINUS
(SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP
WHERE EMPNO IN (SELECT DISTINCT MGR
  FROM EMP));

위와 같이 전체에서 MGR에 포함된 리스트를 가져와 MINUS연산을 하면 간단하게 알 수 있다. 하지만 이 경우엔 테이블에 대한 쿼리가 3개나 된다. 이보다 더 효율을 높일 수 있다. 이전의 MGR에 속한 직원을 가져오는 쿼리문에서 사용된 IN 구문을 NOT IN으로 바꿈으로 처리만 하면 된다.

SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP
WHERE EMPNO NOT IN (SELECT DISTINCT MGR "LIST"
  FROM EMP
  WHERE MGR IS NOT NULL)

다만, IN의 경우는 전체 리스트에서 포함이 된 결과 한개만 일치해도 되므로 수학적 연산으로 말하자면 OR연산이다. 이를 NOT 처리하게 되면 AND 연산이 되어야 하지만, 위의 결과에서 MGR에 속한 직원중 최고위에 위치한 KING의 경우는 상위 MGR이 없으므로 NULL을 반환하였다. 즉, NULL에 대한 AND연산이므로 모든 값이 NULL 처리가 되고, 이에 대한 비교가 불가능해진다. 그러므로 서브쿼리 내의 WHERE절에저 NULL인 값을 제외시켜야 한다.

SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP
WHERE EMPNO != ALL (SELECT DISTINCT MGR "LIST"
  FROM EMP
  WHERE MGR IS NOT NULL);

위와 같은 방식으로 ALL을 이용하여서도 처리하여도 된다.

ALL :  LIST의 모든 값에 대한 연산으로, 수학적으로 볼 때 LIST의 값 전부에 대한 AND연산을 의미한다. NOT IN은 !=ALL 로 표현이 가능하다.
ANY :  LIST의 어떤 값에 대한 연산으로, 수학적으로 볼 때 LIST의 값 최소 1개에 대한 OR연산을 의미한다. IN은 = ANY로 표현이 가능하다.
ALL과 ANY의 경우 MS-SQL에서는 사용할 필요가 많으므로 필수로 꼭 알아 놓아야 한다.

SELECT *
FROM EMP
WHERE SAL = (SELECT MAX(SAL) FROM EMP);

SELECT *
FROM EMP
WHERE SAL >= ALL(SELECT SAL FROM EMP);

위의 두 쿼리는 SAL이 가장 높은 값을 가져온다. 즉, ALL과 ANY등을 이용함으로써 그룹함수의 역할을 간단하게 구현할 수도 있다.

SELECT *
FROM EMP
WHERE COMM = (SELECT MAX(COMM) FROM EMP);

SELECT *
FROM EMP
WHERE COMM >= ALL(SELECT COMM FROM EMP);

다만, 위의 두 쿼리와 같이, 그룹함수와 다르게 NULL을 신경써야 한다. 그룹함수의 경우에는 NULL 값에 대하여 무시하고 처리하나, ALL연산의 경우에는 포함하므로 주의해야 한다. 그러므로 LIST를 구하는 서브쿼리에 WHERE 조건을 달아 다음과 같이 수정하면 된다.

SELECT *
FROM EMP
WHERE COMM >= ALL(SELECT COMM FROM EMP
                                WHERE COMM IS NOT NULL);

EMP 테이블에서 SAL가 최고인 사원을 제외한 나머지 사원들의 정보를 추출하여보자. 이는 그룹함수 MAX를 사용한 후 그보다 작은 수를 처리해도 되지만 다음과 같은 처리도 가능하다.

SELECT *
FROM EMP
WHERE SAL < ANY(SELECT SAL FROM EMP);

이를 바꿔서 EMP 테이블에서 SAL가 최저인 사원을 제외한 나머지 사원들의 정보를 추출하여 보자.

SELECT *
FROM EMP
WHERE SAL > ANY(SELECT SAL FROM EMP);

위의 두 쿼리문은 ANY를 사용하여 비교를 한다. 첫번째 쿼리문의 경우에는 < ANY를 사용하므로써 어느값보다 작은 조건이므로, 가장큰 값의 경우를 제외하고 모든 값이 되는것이며, 두번째 쿼리문의 > ANY의 경우에는 이와 반대로 어떤 값보다 크면 되므로 가장 작은 값만이 비교를 충족시키지 못하므로 이를 제외한 모든 값을 보여준다. 

위에서 구해보았던 MGR 컬럼에 등록이 된 사원들에 대한 부서번호, 사원번호, 사원명, 급여 정보를 ANY를 이용하여 추출해보자.

SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP
WHERE EMPNO = ANY (
SELECT DISTINCT MGR
FROM EMP);

그리고 ALL을 이용하여 MGR 컬럼에 등록이 안된 사원들에 대해서도 부서번호, 사원번호, 사원명, 급여를 추출해야 한다.

SELECT DEPTNO, EMPNO, ENAME, SAL
FROM EMP
WHERE EMPNO != ALL(
SELECT DISTINCT MGR
FROM EMP
WHERE MGR IS NOT NULL);