3. 사례 이해를 위한 기본사항

집합 연산자#

집합 연산자
  • SELECT 문의 결과 SET 과
  • 다른 SELECT 문의 결과 SET 을 이용해
  • 합집합, 교집합, 차집합 등의
  • 새로운 결과 SET 을 만들어 낼 때 사용되는 연산자
집합간 비교 조건

집합 연산자로 연결돤 SELECT 문들은 같은 구조를 가져야 한다.

  • 컬럼의 수와 데이터 형이 순서대로 일치해야 한다.

결과 SET 의 컬럼 이름

  • 첫번째 SELECT 문의 컬럼명
오라클이 제공하고 있는 집한 연산자
  • UNION 합집합 (중복 자료 배제)
  • UNION ALL 합집합 (중복 자료 포함)
  • MINUS 차집합
  • INTERSECT 교집합

SORT 가 일어나지 않는 것은 UNION ALL 뿐이다.

UNION 과 UNION ALL#

공통점

  • 두 DATASET 의 합집합

차이점

  • UNION
    • 중복된 ROW 가 있으면 그 중 하나의 ROW 만 포함
    • 내부적으로 정렬 작업
  • UNION ALL
    • 중복된 ROW 가 있더라도 결과를 모두 보여줌
    • 정렬이 일어나지 않아 UNION 보다는 빠른 응답을 보여줌
caution

위 설명은, DB 가 COST BASE MODE 라면 다를 수 있다. RULE BASE MODE 일 경우 해당된다.

UNION ALL 에 ORDER BY 가 필요한 경우
SELECT EMP_ID FROM TEMP
UNION ALL
SELECT EMP_ID FROM TCOM
ORDER BY 1; -- 두 SELECT 문의 실제 컬럼 명이 대개 다르기 때문에 순서를 이용한다.

INTERSECT#

두 DATASET 의 교집합

  • 정렬이 일어난다.
SELECT EMP_ID FROM TEMP
INTERSECT
SELECT EMP_ID FROM TCOM;

MINUS#

두 DATASET 간의 차집합

SELECT EMP_ID FROM TEMP
MINUS
SELECT EMP_ID FROM TCOM;

조인(JOIN)#

두 개 이상의 테이블이나, DATASET 을 서로 연결하여 데이터를 검색하고자 할 때 사용.

  • FROM 절에 조인할 테이블들을 기술한다.
  • 조건절에서 테이블간의 연결이 이루어질 조건들을 기술한다.
tip

SQL TUNING 의 많은 부분이 JOIN 의 사용과 관련있다.

조인의 종류

  • CARTESIAN PRODUCT
  • EQUI JOIN
  • NON-EQUI JOIN
  • OUTER JOIN
  • SELF JOIN

CARTESIAN PRODUCT#

조건 절에 조인을 위한 조건이 기술되지 않은 경우

  • 테이블 단위로 나올 수 있는 RECORD 수의 곱으로 결과가 생성된다.
SELECT NO, A.EMP_ID, A.EMP_NAME, A.SALARY
FROM TEMP A, MOD_TEST B
WHERE B.NO <= 2
AND A.LEV = '과장'

EQUI JOIN#

조건절에 기술된 조인 조건을 EQUAL 연산자로 연결하는 경우

SELECT A.EMP_ID, A.EMP_NAME, A.DEPT_CODE, B.DEPT_NAME
FROM TEMP A, TDEPT B
WHERE B.DEPT_CODE = A.DEPT_CODE
EQUI JOIN 이 되지 못하는 경우
  • PRIMARY KEY 가 두 컬럼 이상으로 구성된 복합 키를 가진 테이블 간의 조인에서
  • 실수로 그 중 하나를 조인 조건에서 빼 먹은 경우
  • EQUI JOIN 도 아니고 CARTESIAN PRODUCT 도 아닌 쓸모없는 자료가 나온다.

NON EQUI JOIN#

조건절에서 조인 조건이 = 이 아닌 다른 연산기호로 주어지는 경우

SELECT B.LEV 직급,
A.EMP_ID 사번,
A.EMP_NAME 성명,
A.BIRTH_DATE 나이,
A.LEV 현재직급
FROM TEMP A, EMP_LEVEL B
WHERE A.BIRTH_DATE BETWEEN ADD_MONTHS(SYSDATE, -1*TO_AGE * 12)
AND ADD_MONTHS(SYSDATE, -1*FROM_AGE*12)
AND B.LEV = '과장'

OUTER JOIN#

두 개 이상의 테이블 조인 시,

  • 한쪽 테이블의 행에 대해 다른 쪽 테이블에 일치하는 행이 없더라도,
  • 다른 쪽 테이블의 행을 NULL 로 하여 행을 Return 한다.
SELECT COLUMN1, COLUMN2, ...
FROM TABLE1, -- 기준 테이블
TABLE2, ...
WHERE TABLE1.COLUMN = TABLE2.COLUMN (+)

  • TABLE1 에 없고 TABLE2 에만 있는 값은 리턴되지 않는다.

  • 통상의 JOIN 에서는 리턴되지 않는 다른 쪽 테이블의 행 모두와 JOIN 이 일어난다.

OUTER JOIN 연산자 (+)

OUTER JOIN 연산자 (+) 를 지정한 테이블에 대해서는

  • 일시적으로 특별한 NULL 열이 작성된다.
  • OUTER JOIN 기호는 컬럼 명의 바로 뒤에 붙는다.
  • NULL 열이 작성되어야 하는 쪽에 기호가 붙는다.
  • 하나의 테이블이 OUTER JOIN 의 대상으로 삼을 수 있는 테이블의 수는 한 개 뿐이다.
SELECT A.EMP_ID, B.EMP_ID
FROM TEMP A, TCOM B
WHERE B.WORD_YEAR(+) = '2001'
AND B.EMP_ID(+) = A.EMP_ID;

JOINING TABlE ITSELF (SELF JOIN)#

같은 테이블을 마치 다른 테이블처럼 인식시켜 JOIN 하는 것

자신의 JOIN 에서는 테이블에 반드시 ALIAS 를 사용한다.

SELECT ALIAS1.COLUMN1, ALIAS2.COLUMN2, ....
FROM TABLE1 ALIAS1, TABLE1 ALIAS2...
WHERE ALIAS1.COLUMN = ALIAS2.COLUMN;
  1. 1단계 순환 참조가 필요한 경우에 많이 사용된다.
  2. 한 ROW 에 두개의 RECORD 정보를 보여주고자 할 때

서브 쿼리 (SUB QUERY)#

SELECT 한 결과를 DML 어디서든 사용 가능

  • 조건 비교에 사용
    • SELECT, DELETE, UPDATE 시의 WHERE 절에 사용 될 수 있다.
  • UPDATE, INSERT 에 사용
서브쿼리

Query 의 조건에서 컬럼과 비교되는 상대 값으로 또 다른 쿼리의 결과가 사용되는 경우

InnerQuery (SUB QUERY)

  • 또 다른 쿼리

OuterQuery (MAIN QUERY)

  • InnerQuery 에서 나온 결과를 비교조건으로 사용하는 쿼리

서브쿼리의 종류

  • SINGLE ROW
  • MULTI ROW
  • MULTI COLUMN
  • CORRELATED

각각 중복되어 사용될 수 있으므로

  • SINGLE ROW MULTI COLUMN
  • MULTI ROW MULTI COLUMN
  • SINGLE ROW CORRELATED
  • MULTI ROW CORRELATED

SINGLE ROW 서브쿼리#

  • SELECT 에서 결과로 나오는 행의 수가 단 한 행
  • 이 중 한 컬럼을 SUB QUERY 로 이용한다.
SELECT EMP_ID, EMP_NAME
FROM TEMP
WHERE SALARY = (SELECT MAX(SALARY) FROM TEMP);

SUB QUERY 가 조건의 비교문으로 사용 될 때

  • 연산자가 IN, ANY, ALL, EXISTS 등이 아닌 경우
  • SUB QUERY 에서 나오는 결과 행의 수가 1 보다 클 수 없다.

MULTI ROW 서브쿼리#

서브쿼리의 결과 행이 한 행 이상

  • IN, ANY, ALL, EXISTS 등의 연산시에만 가능하다.
SELECT EMP_ID, EMP_NAME
FROM TEMP
WHERE DEPT_CODE IN (SELECT DEPT_CODE FROM TDEPT WEHRE AREA = '인천') -- 부서가 하나 이상일 것이다.

MULTI COLUMN 서브쿼리#

서브쿼리에서 결과로 나오는 행(들)이 한 컬럼이 아닌 두 개 이상을 가지는 경우

PRIMARY KEY 컬럼이 두 개 이상인 경우에, KEY 값을 한꺼번에 묶어서 비교하기 위하여 자주 사용된다.

SELECT EMP_ID, EMP_NAME
FROM TEMP
WHERE (DEPT_CODE, EMP_ID) IN
(SELECT DEPT_CODE, BOSS_ID
FROM TDEPT);
부서별 최고 연봉금액과 일치하는 사원의 정보를 읽어오기
SELECT EMP_ID, EMP_NAME, SALARY
FROM TEMP
WHERE (DEPT_CODE, SALARY) IN
(SELECT DEPT_CODE, MAX(SALARY)
FROM TEMP
GROUP BY DEPT_CODE);

CORRELATED 서브쿼리#

InnerQuery 에서 OuterQuery 의 어떤 컬럼 값을 사용하는 경우

서로 값을 주고 받는 연관 관계

SUB QUERY 가 MAIN QUERY 의 값을 이용하고,
SUB QUERY 의 값을 다시 MAIN QUERY 가 다시 이용

자신의 연봉이 자신과 같은 LEV 에 해당하는 직원의 평균 SALARY 보다 많은, 사원의 정보를 읽어옴
SELECT EMP_ID, EMP_NAME
FROM TEMP A
WHERE SALARY > (SELECT AVG(SALARY)
FROM TEMP B
WHERE B.LEV = A.LEV);
성능이슈

Correlated Subquery 는

반드시 하나의 레코드가 조건절에서 비교되기 때문에 InnerQuery 가 한번씩 수행된다.

10000건 짜리는 하나의 SQL 을 수행하기 위해 InnerQuery 가 만 번 수행된다.

InLine View 등 다른 방법을 사용해 보자.

사번이 가장 빠른 사람을 부서장으로 임명하기
UPDATE TDPET A
SET A.BOSS_ID = (SELECT MIN(B.EMP_ID)
FROM TEMP B
WHERE B.DEPT_CODE = A.DEPT_CODE
);
각 사원별 연봉의 10%를 COMMISSION으로 하는 새로운 자료를 TCOM에 INSERT
INSERT INTO TCOM (WORK_YEAR, EMP_ID, BONUS_RATE, COMM)
SELECT '2002', EMP_ID, 0.1, SALARY*0.1
FROM TEMP;

ANY 와 ALL#

ANY 쓰임#

  1. 값들의 집합과 함께 쓰여 비교된다.
    • SALARY > ANY(100, 200, 300 → SALARY > 100
    • 비교되는 값이 무엇인지 알 때에는 굳이 ANY 를 쓸필요가 없다.
  2. 서브쿼리와 함께 쓰인다
    • 대개의 경우
    • 서브쿼리에서의 어떤 한 값 보다 어떻다라고 비교할 때 사용
SELECT EMP_ID, EMP_NAME, SALARY
FROM TEMP
WHERE SALARY > ANY (SELECT SALARY
FROM TEMP
WHERE LEV = '과장')

ALL 쓰임#

  • 집합의 모든 값 보다 어떤가로 비교
  • ANY 처럼 서브쿼리에서 대개 쓰인다.
SELECT EMP_ID, EMP_NAME, SALARY
FROM TEMP
WHERE SALARY > ALL (SELECT SALARY
FROM TEMP
WHERE LEV = '과장')

EXISTS#

서브쿼리가 적어도 하나의 행을 돌려주는지 체크

SELECT EMP_ID, EMP_NAME, SALARY
FROM TEMP A
WHERE EXISTS (SELECT B.SALARY
FROM TEMP B
WHERE B.LEV = '과장'
AND A.SALARY > B.SALARY
);