21.2.4(목) DB/DML(SELECT)_ JOIN(2), SUBQUERY(1)

민국·2021년 2월 4일

1. JOIN(2)

5) 자체 조인(SELF JOIN)

: 같은 테이블을 다시 한번 조인하는 경우
즉, 자기 자신의 테이블과 다시 조인을 맺는 경우

*오라클전용

[표현법]
FROM 테이블1 별칭A, 테이블1 별칭B
WHERE 별칭A.컬럼 = 별칭B.컬럼;
=> NULL 값이 있는 경우에는

<예시>
  SELECT E.EMP_ID "사원사번", E.EMP_NAME "사원명", E.DEPT_CODE "사원부서", E.SALARY "사원급여"
       , M.EMP_ID "사수사번", M.EMP_NAME "사수명", M.DEPT_CODE "사수부서", M.SALARY "사수급여" 
  FROM EMPLOYEE E, EMPLOYEE M
  WHERE E.MANAGER_ID = M.EMP_ID(+);

*ANSI

[표현법]
FROM 테이블1 별칭A
JOIN 테이블1 별칭B ON (별칭A.컬럼 = 별칭B.컬럼);

<예시>
  SELECT E.EMP_ID "사원사번", E.EMP_NAME "사원명", E.DEPT_CODE "사원부서", E.SALARY "사원급여"
       , M.EMP_ID "사수사번", M.EMP_NAME "사수명", M.DEPT_CODE "사수부서", M.SALARY "사수급여"
  FROM EMPLOYEE E 
  LEFT JOIN EMPLOYEE M ON (E.MANAGER_ID = M.EMP_ID);

복합조인(JOIN)문

<예시> 자체조인 + 다른테이블 연동
SELECT E.EMP_ID "사원사번", E.EMP_NAME "사원명", E.DEPT_CODE "사원부서", E.SALARY "사원급여", ED.DEPT_TITLE "사수부서명"
     , M.EMP_ID "사수사번", M.EMP_NAME "사수명", M.DEPT_CODE "사수부서", M.SALARY "사수급여", MD.DEPT_TITLE "사수부서명"
FROM EMPLOYEE E 
LEFT JOIN EMPLOYEE M ON (E.MANAGER_ID = M.EMP_ID)
JOIN DEPARTMENT ED ON (E.DEPT_CODE = ED.DEPT_ID)
JOIN DEPARTMENT MD ON (M.DEPT_CODE = MD.DEPT_ID) ;


<예시> 4개의 테이블 연동

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME, LOCAL_NAME
  FROM EMPLOYEE E
  JOIN DEPARTMENT D ON E.DEPT_CODE = D.DEPT_ID
  JOIN JOB J ON J.JOB_CODE = E.JOB_CODE
  JOIN LOCATION L ON D.LOCATION_ID = L.LOCAL_CODE;

2. SUBQUERY(1)

하나의 주된 SQL문(SELECT, INSERT, UPDATE, CREATE,....) 안에 포함된 또 하나의 SELECT문
메인 SQL문을 위해 보조 역할을 하는 쿼리문
즉, 쿼리문 안에 포함된 쿼리문

[표현법]
쿼리문 ( 쿼리문 )...

<예시> 
노옹철 사원과 같은 부서인 사원들에 대해 조회
=> 1) 노옹철 사원 부서조회 2) 부서코드가 D9인 사원조회
이 두 단계를 한번에 합쳐줌

SELECT EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE 
                     FROM EMPLOYEE 
                    WHERE EMP_NAME = '노옹철');

서브쿼리 구분

: 서브쿼리를 수행한 결과값이 몇 행(ROW) 몇 열(COLUMN) 이냐에 따라서 분류됨

단일행 (단일열) 서브쿼리 : 서브쿼리를 수행한 결과값이 오로지 1개일 때
다중행 (단일열) 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 행일 때
(단일행) 다중열 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 열일 때
다중행 다중열 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 행 여러 열일 때

=> 즉, 단일 행 또는 열 일때는 생략가능

※ 서브쿼리를 수행한 결과가 몇행 몇열이냐에 따라 사용가능한 연산자도 달라짐

1) 단일행 서브쿼리(SINGLE ROW SUBQUERY)

: 서브쿼리의 조회 결과 값이 오로지 1개일 때
=> 일반 연산자(=, !=, <=, > , ....) 사용가능

<예시 1>
SELECT EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE 
                     FROM EMPLOYEE 
                    WHERE EMP_NAME = '노옹철');
                    
<예시 2>
SELECT EMP_ID, EMP_NAME, JOB_CODE
FROM EMPLOYEE
WHERE SALARY >= (SELECT ROUND(AVG(SALARY)) 
                  FROM EMPLOYEE);

2) 다중행 서브쿼리(MULTI ROW SUBQUERY)

: 서브쿼리의 조회 결과 값이 여러 행 일 때

[표현법]

(NOT) IN (서브쿼리1, 서브쿼리2, ...)
: 여러 개의 결과 값 중에서 한개라도 일치하는 값이 있으면/ 없다면
ANY = OR개념

<예시>
선동일 또는 유재석 사원과같은 부서인 사원들 조회 (사원명, 부서코드, 급여)
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE DEPT_CODE IN (SELECT DEPT_CODE
                     FROM EMPLOYEE
                     WHERE EMP_NAME IN ('선동일', '유재식'))
  AND EMP_NAME NOT IN ('선동일', '유재식');

비교대상 > ANY (서브쿼리1, 서브쿼리2...) =>
: 여러개의 결과값 중에서 하나라도 클 경우
여러개의 결과값 중에서 가장 작은 값보다 클 경우
비교대상 < ANY (서브쿼리1, 서브쿼리2...)
: 여러개의 결과값 중에서 "하나라도" 작을 경우
여러개의 결과값 중에서 가장 큰 값보다 작을 경우

<예시>
대리직에도 불구하고 과장보다 급여 많이받는 직원조회
SELECT E.EMP_ID, E.EMP_NAME, J.JOB_NAME, E.SALARY
FROM EMPLOYEE E
JOIN JOB J ON E.JOB_CODE = J.JOB_CODE
WHERE SALARY > ANY (SELECT SALARY
                    FROM  EMPLOYEE E
                    JOIN JOB J ON E.JOB_CODE = J.JOB_CODE
                    WHERE JOB_NAME = '과장')
AND J.JOB_NAME = '대리';

ALL = AND 개념
- 비교대상 > ALL (서브쿼리1, 서브쿼리2...)
  : 여러개의 결과값 중에서 "모든" 값보다 클 경우
  여러개의 결과값 중에서 가장 큰값보다 클 경우
- 비교대상 < ALL (서브쿼리1, 서브쿼리2...)
  : 여러개의 결과값 중에서 "모든" 값보다 작을 경우
  여러개의 결과값 중에서 가장 큰값보다 작을 경우

<예시>
과장 중에 > 차장 급여
SELECT E.EMP_ID, E.EMP_NAME, J.JOB_NAME, E.SALARY
FROM EMPLOYEE E
JOIN JOB J ON E.JOB_CODE = J.JOB_CODE
WHERE SALARY > ALL (SELECT SALARY
                    FROM  EMPLOYEE E
                    JOIN JOB J ON E.JOB_CODE = J.JOB_CODE
                    WHERE JOB_NAME = '차장')
AND J.JOB_NAME = '과장';

3) (단일행) 다중열 서브쿼리

: 조회 결과 값은 한 행이지만 나열된 컬럼(열)수가 여러개 일 때

[표현법]
FROM 테이블
WHERE (비교대상1, 비교대상2) = (비교대상1, 비교대상2)

<예시>
하이유와 같은 직급, 같은 직종인 사람들 조회
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE, JOB_CODE) = (SELECT DEPT_CODE, JOB_CODE
                                FROM EMPLOYEE
                                WHERE EMP_NAME = '하이유');