[Oracle] SQL문법 3.집합 연산자와 JOIN

집합 연산자(SET OPERATOR)

집합 연산자란

• 여러개의 쿼리문을(=SELECT 결과물) 가지고 하나의 쿼리문으로 만드는 연산자이다
• 여러개의 SELECT문을 하나로 연결한다
• 집합 연산자로 결합된 컬럼의 결과 컬럼 또한 데이터타입이 동일해야 한다
• 양 쿼리문의 결과를 합쳐서 한개의 테이블로 보여줘야 하기 때문에 각 쿼리문의 SELECT절 부분이 같아야 한다
  => 조회할 컬럼이 동일해야 한다

[ UNION ]

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' -- 6명 조회됨
UNION
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000; -- 8명 조회됨
-- 하지만 중복값이 제거되어 총 14명이 아닌, 6명이 출력된다

• 합집합
• 여러개의 쿼리 결과를 합치는 연산자로 중복된 영역은 제외한다
  => 두 쿼리문의 수행 결과를 합친 후 중복값을 한번 제거한다
• 필수적으로 양쪽 쿼리문을 실행 한 후 합치는 것이기 때문에 성능상 자주 사용하는 것을 권장하지 않는다

[ UNION ALL ]

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' -- 6명 조회됨
UNION ALL
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000; -- 8명 조회됨
-- 중복값을 제외하지 않아, 둘을 더한 값인 총 14명이 출력된다

• 합집합 + 교집합
• 여러 쿼리 결과를 합치는 연산자로 중복된 영역 모두 포함하여 합친다
  => 중복 제거를 하지 않는다

[ INTERSECT ]

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' -- 6명 조회됨
INTERSECT
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000; -- 8명 조회됨
-- 두 쿼리의 중복되는 부분인 총 2명이 출력된다

• 교집합
• 여러 쿼리 결과의 중복된 결과만 추출한다
• EXISTS 또는 IN 서브쿼리를 이용한 SQL문으로 변경 가능하다

[ MINUS ]

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' -- 6명 조회됨
MINUS
SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > 3000000; -- 8명 조회됨
-- 선행쿼리문에서 후행쿼리문과 중복되는 부분인 2명을 뺀 4명이 출력된다

• 차집합
• 선행 쿼리문 결과값에서 후행 쿼리문 결과값을 뺀 나머지 부분을 추출한다
  => 선행 쿼리문 결과값 - 교집합(1회)
• NOT EXISTS 또는 NOT IN 서브 쿼리를 이용한 SQL문으로 변경 가능하다

JOIN

JOIN이란

• 두개 이상의 테이블에서 데이터를 함께 조회하고자 할 때 사용되는 구문이다
• 수행결과는 하나의 결과물(Result Set)으로 나오게 된다
• 사용 목적
  • 관계형 데이터베이스에서는 중복의 최소화를 위해 최소한의 데이터로 각각의 테이블에 데이터를 보관하고 있다
    => 즉 원하는 데이터 테이블을 새로 만들기보다 JOIN구문을 이용하여 각 테이블간의 관계를 맺고 함께 조회를 하는 것이다
    => 단 무작정 JOIN을 하는 것이 아니라, 일반적으로 테이블의 식별값인 Primary Key와 테이블 간의 연결고리(Foreign Key)에 해당되는 칼럼을 매칭시켜서 조회해야 한다
• JOIN을 여러번 사용도 가능하다
• JOIN은 문법상 ORACLE 전용구문과 ANSI구문으로 나누어진다
  => ANSI구문 : American National Standards Institute 미국 표준 협회에서 표준 SQL문을 정립해 놓은 것이다. 표준 SQL문이기 때문에 DBMS의 종류에 제약을 받지 않는다

ORACLE 전용구문	ANSI구문
등가 조인(EQUI JOIN)	내부조인(INNER JOIN) -> JOIN USING/ON
포괄 조인	외부 조인(OUTER JOIN) -> JOIN USING
(LEFT OUTER JOIN)	왼쪽 외부조인(LEFT OUTER JOIN)
(RIGHT OUTER JOIN)	오른쪽 외부조인(RIGHT OUTER JOIN)
	전체 외부 조인(FULL OUTER JOIN) : 오라클에서 사용불가
카테시안곱	교차조인(CROSS JOIN)
자체조인(SELF JOIN)
비등가조인 (NON EQUI JOIN)

[ EQUI JOIN(등가) / INNER JOIN(내부) ]

• 연결시키고자 하는 칼럼 값들이 일치하는 행들만 JOIN해서 조회한다
  => 일치하지 않는 값들은 결과에서 제외한다
• 가장 일반적으로 사용하는 ‘=’에 의한 JOIN이다
• 일치하지 않는 값이거나 NULL인 값은 조회되지 않는다

ORACLE (등가조인)

• [표현법]

  SELECT 조회하고자 하는 컬럼명들,
  FROM 조인하고자 하는 테이블명들
  WHERE 연결할 칼럼에 대한 조건을 제시 (=);
  

  • FROM절에 ‘,’ 로 구분하여 합칠 테이블명들을 나열하고 WHERE절에서 합치기에 사용할 컬럼명을 명시 및 조건을 제시한다
  • WHERE절에서 FROM절에 사용한 테이블 별칭 이용이 가능하다
  • 테이블에서의 별칭은 AS를 쓰지 않고 공백으로만 구분시킨다
  • JOIN시 추가적인 조건을 더 넣고 싶다면 AND로 추가해 주면 된다

• 예시)

  SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE
  FROM EMPLOYEE, DEPARTMENT
  WHERE DEPT_CODE = DEPT_ID;
  --EMPLOYEE 테이블의 DEPT_CODE 값과 DEPARTMENT테이블의 DEPT_ID가 일치하는 경우에만 조회된다
  

• 에러발생 예시)

  SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
  FROM EMPLOYEE , JOB
  WHERE JOB_CODE = JOB_CODE;
  -- 에러발생 : column ambiguously defined -> 칼럼명이 애매모호하다
  

  • 연결할 칼럼명이 일치(EMPLOYEE - JOB_CODE/ JOB - JOB_CODE)하는 경우 확실히 어느 테이블의 컬럼인지 제시해주어야 한다
  • 별칭 사용시 : FROM EMPLOYEE E, JOB J 와 같이 테이블의 별칭을 붙이고 WHERE E.JOB_CODE = J.JOB_CODE; 와 같이 컬럼의 출처를 알려주게끔 작성하면 된다
  • 별칭 사용 없을 시 : WHERE EMPLOYEE.JOB_CODE = JOB.JOB_CODE; 와 같이 컬럼명 앞에 테이블명을 붙여주면 된다
  • 별칭을 부여하거나 테이블 명을 그대로 사용하거나 둘중 자유롭게 선택하면 되지만 둘을 혼합해서 사용할 수는 없으니 한쪽으로 통일시켜야 한다

ANSI (내부조인)

• 연결에 사용하려는 컬럼 명이 같은 경우 USING구문, 다른경우 ON구문을 사용한다
• 기본적으로 JOIN은 INNER JOIN이며 OUTER JOIN은 반드시 OUTER JOIN을 명시해야한다
• FROM 절에 기준이 되는 테이블을 하나만 기술한다
• 이후 JOIN 절에서 함께 조회하고자 하는 테이블과, 매칭시킬 컬럼에 대한 조건도 같이 기술한다
• JOIN시 추가적인 조건을 더 넣고 싶다면 WHERE절을 추가하여 조건을 제시하면 된다

• [ON 구문 표현법]

  SELECT 조회할 컬럼명들 나열
  FROM 기준으로 삼을 테이블 1개만 제시
  JOIN 조인할 테이블 1개 제시 ON (연결할 칼럼에 대한 조건을 제시(=))
  

  • 연결할 컬럼 명이 다른 경우 무조건 ON구문만 사용 가능하다

• ON 구문 예시

  SELECT EMP_ID, EMP_NAME, DEPT_CODE, DEPT_TITLE
  FROM EMPLOYEE
  /*INNER*/ JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID;
  -- INNER 는 생략 가능하다
  

• [USING구문 표현법]

  SELECT 조회하고자 하는 칼럼명들
  FROM 기준으로 삼을 테이블명 1개만 제시
  JOIN 조인할 테이블명 1개만 제시 USING (연결할 컬럼명 1개만 제시)
  

  • 컬럼명이 동일한 경우에만 사용가능하며 동일한 컬럼명 하나만 기술하면 알아서 매칭이 진행된다
  • USING을 사용한 구문에는 컬럼 별칭을 사용할 수 없다
    => only simple column names allowed here 에러 발생

• USING구문 예시

  SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
  FROM EMPLOYEE
  JOIN JOB USING (JOB_CODE);
  -- EMPLOYEE의 JOB_CODE와 JOB의 JOB_CODE를 매칭시킨것임
  

자연조인(NATURAL JOIN)

• 등가 조인 방법중 하나이기도 하다
  => EQUI JOIN과 비슷하지만 WHERE 조건절 없이 조인한다
• 동일한 타입과 이름을 가진 칼럼을 조인 조건으로 이용하는 것이다
• 양 테이블에 일치하는 컬럼명이 단 한개만 존재할 경우 자연조건으로 사용시 알아서 해당 컬럼이 매칭된다

• 일치하는 컬럼명이 단 하나여야만 하기 때문에 사용하기 까다롭고 잘 사용되지 않는 구문이기도 하다

  SELECT EMP_ID, EMP_NAME, JOB_CODE, JOB_NAME
  FROM EMPLOYEE
  NATURAL JOIN JOB;
  

[ 포괄조인 / OUTER JOIN(외부) ]

• 테이블간의 JOIN시에 일치하지 않는 행도 포함시켜서 조회가 가능하다
• 반드시 LEFT / RIGHT를 지정해야한다 ( = 기준이되는 테이블을 지정해야 한다 )

=> 일치하는 행 + 지정한 테이블을 기준으로 일치하지 않는 행도 포함시켜서 조회한다

ORACLE (포괄조인)

• [표현법]

  SELECT EMP_NAME, SALARY, DEPT_TITLE
  FROM EMPLOYEE, DEPARTMENT
  WHERE DEPT_CODE = DEPT_ID(+);
  

  • 기준으로 삼을 테이블의 반대 컬럼명에 (+)를 붙여준다

ANSI (외부조인)

• LEFT OUTER JOIN / RIGHT OUTER JOIN

  • 두 테이블중 왼/오른편에 기술된 테이블을 기준으로 JOIN한다
    => 일치하는 것을 찾지 못하더라도 조회하게 된다

• [표현법]

  SELECT EMP_NAME, SALARY, DEPT_TITLE
  FROM EMPLOYEE LEFT /*OUTER*/ JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID;
  -- OUTER 생략 가능
  

  • LEFT 위치에 LEFT / RIGHT 중 하나를 명시하여 어떤 테이블을 기준으로 삼을 것인지 정한다

• FULL OUTER JOIN

  SELECT EMP_NAME, SALARY, DEPT_TITLE
  FROM EMPLOYEE FULL /*OUTER*/ JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID;
  

  • LEFT / RIGHT을 명시하는 것 처럼 FULL을 명시하여 사용한다
  • 두 테이블이 가진 모든 행을 조회한다
  • 일치하는 행들 + LEFT OUTER JOIN 기준 새롭게 추가된 행들 + RIGHT OUTER JOIN 전체를 조회한다
  • 자주 사용되는 구문은 아니다
  • ORACLE에서는 사용 불가하다
    => a predicate may reference only one outer-joined table 오류발생

[ 카테시안 곱 / 교차조인(CROSS JOIN) ]

• Cartesian Product
• 모든 테이블의 각 행들이 서로서로 매핑된 데이터가 조회된다(= 곱집합)
• 두 테이블의 행들이 모두 곱해진 행들의 조합이 출력된다
  => 각각 N개 M개의 행을 가진 테이블이 있다면 출력결과로 N*M만큼의 행을 반환하게 된다
  => 모든 경우의 수를 다 따져서 조회를 하는 것 => 방대한 데이터를 출력하게 되므로 과부하의 위험이 있다

ORACLE (카테시안 곱)

• [표현법]

  SELECT EMP_NAME, DEPT_TITLE
  FROM EMPLOYEE, DEPARTMENT
  -- EMPLOYEE의 모든 행 (23개) * DEPARTMENT의 모든 행 (9개) => 207개 행이 조회됨
  

  • WHERE 조건절에 기술하는 조인문이 잘못되었거나 아예 없을 때 발생하게 된다

ANSI (교차조인)

• [표현법]

  SELECT EMP_NAME , DEPT_TITLE
  FROM EMPLOYEE
  CROSS JOIN DEPARTMENT
  -- EMPLOYEE의 모든 행 (23개) * DEPARTMENT의 모든 행 (9개) => 207개 행이 조회됨
  

  • CROSS JOIN 을 기술하여 사용한다

[ 자체조인 (SELF JOIN) ]

• 같은 테이블끼리 조인하는 경우에 사용된다
  => 자기 자신의 테이블과 다시 조인을 맺겠다는 뜻
• 자체조인의 경우 테이블에 반드시 별칭을 붙여줘야 한다
  => 별칭을 붙이면 서로 다른 테이블인 것처럼 사용이 가능하다

ORACLE (자체조인)

• [표현법]

  SELECT E.EMP_ID, E.EMP_NAME, E.MANAGER_ID, M.EMP_NAME
  FROM EMPLOYEE E, EMPLOYEE M
  WHERE E.MANAGER_ID = M.EMP_ID(+);
  -- 동일한 EMPLOYEE 테이블을 불러왔지만 각기 다른 별칭으로 사용했다
  

ANSI (자체조인)

• [표현법]

  SELECT E.EMP_ID, E.EMP_NAME, E.MANAGER_ID, M.EMP_NAME
  FROM EMPLOYEE E
  LEFT JOIN EMPLOYEE M ON E.MANAGER_ID = M.EMP_ID;
  -- 동일한 EMPLOYEE 테이블을 불러왔지만 각기 다른 별칭으로 사용했다
  

[ 비등가조인 (NON EQUI JOIN) ]

• = 를 사용하지 않는 JOIN문이다
• 다른 비교연산자 (ex. >, <, <=, >=, BETWEEN A AND B)를 사용하여 JOIN한다
  => 지정한 칼럼값들이 일치하는 경우가 아니라 범위에 포함되는 경우 매칭해서 조회한다
• =에 비해 효율이 별로 좋지 않아, 가끔 한번씩정도밖에 쓰이지 않는다

ORACLE (비등가조인)

• [표현법]

  SELECT EMP_NAME, SALARY, SAL_GRADE.SAL_LEVEL
  FROM EMPLOYEE, SAL_GRADE
  WHERE SALARY BETWEEN MIN_SAL AND MAX_SAL;
  

ANSI (비등가조인)

• [표현법]

  SELECT EMP_NAME, SALARY, SAL_GRADE.SAL_LEVEL
  FROM EMPLOYEE
  JOIN SAL_GRADE ON SALARY BETWEEN MIN_SAL AND MAX_SAL;
  -- USING은 사용불가하다
  

[ 다중 JOIN ]

• 3개 이상의 테이블들을 조인해서 조회할 때 사용한다
• 동작 상 조인(연결) 순서가 중요하다
  => 조인의 순서를 제대로 지키지 않을 경우 에러 발생

• ORACLE (다중조인)

  • [표현법]

    SELECT EMP_ID 사번, EMP_NAME 사원명, DEPT_TITLE 부서명, JOB_NAME 직급명
    FROM EMPLOYEE E , DEPARTMENT D, JOB J
    WHERE E.DEPT_CODE = D.DEPT_ID(+)
        AND E.JOB_CODE = J.JOB_CODE(+);
    

• ANSI (다중조인)

  • [표현법]

    SELECT EMP_ID 사번, EMP_NAME 사원명, DEPT_TITLE 부서명, JOB_NAME 직급명
    FROM EMPLOYEE E
    LEFT JOIN DEPARTMENT D ON E.DEPT_CODE = D.DEPT_ID
    LEFT JOIN JOB J ON E.JOB_CODE = J.JOB_CODE;