[Day +39]Join, 서브쿼리

210817 월

 

3. 교차조인(CROSS JOIN, 카티지언 곱)
: 조인되는 테이블의 각 행들이 모드 매핑 된 데이터가 검색 되는 방법

SELECT
EMP_NAME
, DEPT_TITLE
FROM EMPLOYEE
CROSS JOIN DEPARTMENT;

--결국 SELECT... FROM EMPLOYEE, DEPARTMENT;와 같다

4. 비등가조인(NON EQUAL JOIN)
: '=' 등호를 사용하지 않는 조인문
-지정한 컬럼 값이 일치하는 경우가 아닌, 값의 범위에 포함된느 행들을 연결하는 방식

4-1) ANSI

SELECT
EMP_NAME
, SALARY
, E.SAL_LEVEL
, S.SAL_LEVEL
FROM EMPLOYEE E
JOIN SAL_GRADE S ON(SALARY BETWEEN MIN_SAL AND MAX_SAL);

4-2) ORACLE

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

5. 자체 조인(SELF JOIN)
: 같은 테이블을 조인. 자기 자신과 조인을 맺음.

5-1) ANSI

SELECT
E.EMP_ID 사원사번
, E.EMP_NAME 사원이름
, E.MANAGER_ID 관리자사번
, M.EMP_NAME 관리자이름
FROM EMPLOYEE E
JOIN EMPLOYEE M ON(E.MANAGER_ID = M.EMP_ID);

 

5-2) 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;

 

6. 다중조인

 

6-1) ORACLE

SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
,DEPT_TITLE
, LOCAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE);
--**JOIN순서를 바꾸면 되지 않음
--JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE)
--JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID); -> 이거는 실행 불가능

6-2) ANSI

SELECT
EMP_ID
, EMP_NAME
, DEPT_CODE
, DEPT_TITLE
, LOCAL_NAME
FROM EMPLOYEE, DEPARTMENT, LOCATION
WHERE DEPT_CODE = DEPT_ID
AND LOCATION_ID = LOCAL_CODE;

---

SUBQUERY(서브쿼리)
: 하나의 SQL문 안에 포함된 또 다른 SQL문, 메인 쿼리를 위해 보조 역할을 하는 쿼리문
-서브 쿼리를 괄호로 감싸서 이용함

1. 부서 코드가 노옹철 사원과 같은 부서 소속의 직원 명단 조회

1-1) 사원명이 노옹철인 사람의 부서 조회

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

1-2) 부서코드가 D9인 직원 명단 조회

SELECT
*
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';

1-3) 부서코드가 노옹철 사원과 같은 소속의 직원 명단 조회

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

2. 전 직원의 평균 급여보다 많은 급여를 받고 있는 직원의 사번, 이름, 직급코드, 급여 조회
1) 전 직원의 평균 급여 조회 

SELECT 
AVG(SALARY)
FROM EMPLOYEE;

2) 평균 급여보다 많이 받는 직원 조회

SELECT
EMP_ID
, EMP_NAME
, JOB_CODE
, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT 
AVG(SALARY)
FROM EMPLOYEE);

--> 서브 쿼리는 알려지지 않은 기준을 이용한 검색을 위해서 사용함

3) 단일행 서브쿼리(Single Row Subquery)
: 서브쿼리의 조회 결과 값의 행이 1개일 때, 단일행 서브쿼리 앞에는 일반 연산자 사용
<, >, <=, >=, =, !=/^=/<>

3-1) 가장 적은 급여를 받는 직원의 사번, 이름, 직급, 부서, 급여 조회

SELECT
EMP_ID, EMP_NAME, JOB_CODE, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEE);
--WHERE MIN(SALARY);이건 안됨

*서브쿼리는 WHERE절 뿐만 아니라 SELECT, HAVING, FROM절에서도 사용할 수 있음


3-2) 부서별(부서가 없는 사람 포함) 급여의 합계 중 가장 큰 부서의 부서명, 급여 합계를 조회
1) 부서별 급여 합계

SELECT DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE
--부서 코드가 NULL인 사원도 있으므로 전 직원에 대한 조회를 위해 LETF OUTER JOIN
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE;

(

(2) 급여 합계가 가장 큰 부서의 급여 합계 값

SELECT
MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE;

(3) 부서별 급여 합계 중 가장 큰 합계를 가진 부서 조회

SELECT DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE
--부서 코드가 NULL인 사원도 있으므로 전 직원에 대한 조회를 위해 LETF OUTER JOIN
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) = (SELECT
MAX(SUM(SALARY))
FROM EMPLOYEE
GROUP BY DEPT_CODE);

2) 다중행 서브쿼리(Multi Row Subquery)
: 서브 쿼리의 조회 결과 행의 개수가 여러 개일 때
-다중행 서브쿼리 앞에는 일반 연산자 사용할 수 없음

*single-row subquery returns more than one row => 일반 연산자 사용시 발생하는 오류

(1) IN / NOT IN : 여러 개의 결과 값 중에서 한개라도 일치하는 값이 있다면 혹은 없다면
(2) ANY / < ANY : 여러 개의 결과값 중 한개라도 큰 / 작은 경우
(3) ALL / < ALL : 모든 값보다 큰 / 작은 경우
(4) EXISTS / NOT EXISTS : 여러 개의 결과값 중 해당 값이 존재하는지

2-1) 부서별 최고 급여를 받는 직원의 이름, 직급, 부서, 급여 조회(부서순 정렬)

SELECT 
EMP_NAME,
JOB_CODE,
SALARY
FROM EMPLOYEE
WHERE SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE)
--WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE)
--single-row subquery returns more than one row => IN이 아닌 = 연산자 사용시 발생
ORDER BY 3;

2-2) 대리 직급의 직원들 중에서 과장 직급의 최소 급여보다 많이 받는 직원의 사번, 이름, 직급, 급여를 조회
단, > ANY 혹은 < ANY 연산자를 사용

(1) 급여가 200만원 이상인 대리 직급 직원 조회

SELECT
EMP_ID
, EMP_NAME
, JOB_NAME
, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '대리'
AND SALARY >= 2000000;

(2) 과장 직급 직원의 급여

SELECT
SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '과장';

(3) 대리 직급의 직원들 중에서 과장 직급의 최소 급여보다 많이 받는 직원

SELECT
EMP_ID
, EMP_NAME
, JOB_NAME
, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '대리'
AND SALARY > ANY (SELECT
SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '과장');
--여기는 일반 연산자는 안됨

(4) 차장 직급의 급여의 가장 큰 값보다 많이 받는 과장 직급의 직원 
-사번, 이름, 직급, 급여 조회
-단, > ALL 또는 < ALL 연산자 사용

SELECT
EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '과장'
AND SALARY > ALL (SELECT
SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '차장');

3) 다중열 서브쿼리
: 서브 쿼리 SELECT 절에 나열된 컬럼이 여러 개일 때

 

3-1) 퇴사한 여직원과 같은 부서, 같은 직급에 해당하는 사원의 이름, 직급, 부서, 입사일 조회
(1) 퇴사한 여직원 조회

SELECT
EMP_NAME
, JOB_CODE
, DEPT_CODE
, HIRE_DATE
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO, 8, 1) = '2'
AND ENT_YN = 'Y';

(2) 퇴사한 여직원과 같은 부서, 같은 직급인 사원 조회(단일열로 표현하면)

SELECT
EMP_NAME
, JOB_CODE
, DEPT_CODE
, HIRE_DATE
FROM EMPLOYEE
--같은 부서
WHERE DEPT_CODE = 
(SELECT DEPT_CODE FROM EMPLOYEE WHERE SUBSTR(EMP_NO, 8, 1) = '2'
AND ENT_YN = 'Y')

--같은 직급
AND JOB_CODE = (SELECT JOB_CODE FROM EMPLOYEE WHERE SUBSTR(EMP_NO, 8, 1) = '2'
AND ENT_YN = 'Y');

(3) 다중열 서브쿼리로 변경

SELECT
EMP_NAME
, JOB_CODE
, DEPT_CODE
, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE , JOB_CODE)= 
(SELECT DEPT_CODE, JOB_CODE FROM EMPLOYEE WHERE SUBSTR(EMP_NO, 8, 1) = '2'
AND ENT_YN = 'Y'); 

--> 이렇게 하면 오류 발생
--WHERE DEPT_CODE = 
--(SELECT DEPT_CODE, JOB_CODE FROM EMPLOYEE WHERE SUBSTR(EMP_NO, 8, 1) = '2'
--AND ENT_YN = 'Y'); 
--> too many values 오류

4) 다중행 다중열 서브쿼리
: 서브쿼리 조회 결과 행의 개수와 열의 개수가 여러 개일때

 

4-1) 자기 직급의 평균 급여를 받고 있는 직원의 사번, 이름, 직급, 급여를 조회
단, 급여와 급여 평균은 십만원 단위로 계산한 TRUNC(컬럼명, -5)
(1) 직급별 평균 급여

SELECT
JOB_CODE
, TRUNC(AVG(SALARY), -5)
FROM EMPLOYEE
GROUP BY JOB_CODE;

4-2) 자기 직급의 평균 급여를 받고 있는 직원 조회

SELECT
EMP_ID
, EMP_NAME
, JOB_CODE
, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) IN (SELECT
JOB_CODE
, TRUNC(AVG(SALARY), -5)
FROM EMPLOYEE
GROUP BY JOB_CODE);