SQL4,5 JOIN,SUBQUERY

2023. 1. 16. 18:45

JOIN

JOIN	한 개 이상의 테이블에서 테이터를 조회하기 위해 사용하는 것으로, 수행 결과는 하나의 Result Set으로 나오게 됨

오라클 전용 구문	FROM절에 ',＇로 구분하여 합치게 될 TABLE명을 기술하고, WHERE절에 합치기에 사용할 컬럼명을 명시
SELECT EMP_ID , EMP_NAME , DEPT_CODE , DEPT_TITLE FROM EMPLOYEE, DEPARTMENT WHERE DEPT_CODE = DEPT_ID; 연결에 사용할 두 컬럼명이 다른 경우 그냥 사용	SELECT EMP_ID , EMP_NAME , EMPLOYEE.JOB_CODE , JOB_NAME FROM EMPLOYEE, JOB WHERE EMPLOYEE.JOB_CODE = JOB.JOB_CODE; 연결에 사용할 두 컬럼명이 같은 경우 테이블명, 컬럼명으로 작성해서 구분해서 사용
SELECT EMP_ID , EMP_NAME , E.JOB_CODE , JOB_NAME FROM EMPLOYEE E, JOB J WHERE E.JOB_CODE = J.JOB_CODE;

ANSI 표준 구문	연결에 사용하려는 컬럼명이 같은 경우 USING()을 사용하고, 다른 경우 ON()을 사용	SELECT EMP_ID , EMP_NAME , JOB_CODE , JOB_NAME FROM EMPLOYEE JOIN JOB USING(JOB_CODE);
ANSI 표준 구문	연결에 사용하려는 컬럼명이 같은 경우 USING()을 사용하고, 다른 경우 ON()을 사용	SELECT EMP_ID , EMP_NAME , DEPT_CODE , DEPT_TITLE FROM EMPLOYEE JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

INNER JOIN	두 개 이상의 테이블을 조인할 때, 일치하는 값이 없는 행은 조인에서 제외 됨 이것을 INNER JOIN이라고 하며, 명시적으로 사용하지 않을 시에는 기본적으로 INNER JOIN 이다.
SELECT EMP_NAME , DEPT_TITLE FROM EMPLOYEE JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);	SELECT EMP_NAME , DEPT_TITLE FROM EMPLOYEE LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

OUTER JOIN	하지만 일치하지 않은 값 또한 JOIN에 포함시킬 수도 있다. 이것을 OUTER JOIN이라고 하며, 반드시 OUTER JOIN임을 명시해야 함.
1. LEFT OUTER JOIN	합치기에 사용한 두 테이블 중에 왼쪽 편에 기술된 테이블의 컬럼 수를 기준으로 JOIN을 할 때 사용
2. RIGHT OUTER JOIN	합치기에 사용한 두 테이블 중에 오른쪽 편에 기술된 테이블의 컬럼 수를 기준으로 JOIN을 할 때 사용
3. FULL OUTER JOIN	합치기에 사용한 두 테이블이 가진 모든 행을 결과에 포함 시킬 경우 사용
SELECT EMP_NAME , DEPT_TITLE FROM EMPLOYEE RIGHT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);	SELECT EMP_NAME , DEPT_TITLE FROM EMPLOYEE FULL JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);
오라클 전용 SELECT EMP_NAME , DEPT_TITLE FROM EMPLOYEE,DEPARTMENT WHERE DEPT_CODE(+) = DEPT_ID;	SELECT EMP_NAME , DEPT_TITLE FROM EMPLOYEE,DEPARTMENT WHERE DEPT_CODE(+) = DEPT_ID(+); ** 에러발생, (+)는 한쪽에만 사용할 수 있다.

CROSS JOIN	카테이션곱(Cartensian projuct) 조인되는 테이블의 각 행들이 모두 매핑된 데이터가 검색되는 조인 방법으로, 검색되는 데이터 수는 행의 컬럼수 x 행의 컬럼수로 나오게 됨
	SELECT EMP_NAME , DEPT_TITLE FROM EMPLOYEE CROSS JOIN DEPARTMENT;

NON_EQU JOIN	지정한 컬럼 값이 일치하는 경우가 아닌, 값의 범위에 포함되는 행들을 연결하는 방식
	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);

SELF JOIN	조인은 두 개 이상의 서로 다른 테이블을 연결하기도 하지만, 같은 테이블을 조인하는 경우도 있다. 이러한 경우 자기 자신과 조인을 맺는 것이라 하여 SELF JOIN
	SELECT E.EMP_ID , E.EMP_NAME 사원이름 , E.DEPT_CODE , E.MANAGER_ID , M.EMP_NAME 관리자이름 FROM EMPLOYEE E , EMPLOYEE M WHERE E.MANAGER_ID = M.EMP_ID;

다중 JOIN

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

SUBQUERY

SUBQUERY	하나의 SELECT 문장의 절 안에 포함된 또 하나의 SELECT 문장이다. 서브쿼리는 메인쿼리가 실행되기 이전에 한번만 실행되며, 비교연산자의 오른쪽에 기술해야 하며, 반드시 괄호로 묶어야 한다. 또한 서브쿼리와 비교할 항목은 반드시 서브쿼리의 SELECT한 항목의 개수와 자료형을 일치시켜야 함
1. 단일행 서브쿼리	- 서브쿼리의 조회 결과 값의 개수가 1개 일 때
2. 다중행 서브쿼리	- 서브쿼리의 조회 결과 값의 행이 여러 개 일 때
3. 다중열 서브쿼리	- 서브쿼리의 조회 결과 컬럼의 개수가 여러 개 일 때
4. 다중행 다중열 서브쿼리	- 서브쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개 일 때
5. 상(호연)관 서브쿼리	- 서브쿼리가 만든 결과값을 메인 쿼리가 비교 연산할 때, 메인 쿼리 테이블의 값이 변경되면 서브쿼리의 결과값도 바뀐다.
6. 스칼라 서브쿼리	- 상관쿼리 이면서 결과값이 한 개인 서브쿼리

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

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

단일행(SINGLE ROW) 서브쿼리
전 직원의 급여 평균보다 급여를 많이 받는 직원의 이름, 직급, 부서, 급여 조회	SELECT EMP_NAME , JOB_CODE , DEPT_CODE , SALARY FROM EMPLOYEE WHERE SALARY >= (SELECT AVG(SALARY) FROM EMPLOYEE ) ORDER BY 2;

다중행(MULTIPLE ROW) 서브쿼리	1.다중행 서브쿼리 앞에는 일반 비교연산자 사용 불가 2.사용 가능 연산자 IN / NOT IN, >ANY / ALL / EXIST / NOT EXIST 등
부서별 최고 급여를 받는 직원의 이름, 직급 , 부서, 급여 조회	SELECT EMP_NAME , JOB_CODE , DEPT_CODE , SALARY FROM EMPLOYEE WHERE SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE ) ORDER BY 3;

다중열 서브쿼리
- 퇴사한 여직원과 같은 부서, 같은 직급에 해당하는 사원의 이름, 직급, 부서, 입사일을 조회	SELECT EMP_NAME , JOB_CODE , DEPT_CODE , HIRE_DATE FROM EMPLOYEE WHERE (DEPT_CODE, JOB_CODE) IN (SELECT DEPT_CODE , JOB_CODE FROM EMPLOYEE WHERE SUBSTR(EMP_NO,8,1) =2 AND ENT_YN = ‘Y’);

다중행 다중열 서브쿼리
직급별 최소 급여를 받는 직원의 사번, 이름, 직급, 급여 조회	SELECT EMP_ID , EMP_NAME , JOB_CODE , SALARY FROM EMPLOYEE WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE , MIN(SALARY) FROM EMPLOYEE GROUP BY JOB_CODE) ORDER BY 3;

상(호연)관 서브쿼리
관리자가 있는 사원들 중 관리자의 사번이 EMPLOYEE테이블에 존재하는 직원의 사번, 이름, 소속부서, 관리자사번을 조회	SELECT EMP_ID , EMP_NAME , DEPT_CODE , MANAGER_ID FROM EMPLOYEE E WHERE EXISTS (SELECT EMP_ID FROM EMPLOYEE M WHERE E.MANAGER_ID = M.EMP_ID);

스칼라 서브쿼리 - SELECT절
모든 사원의 사번, 이름, 관리자사번, 관리자명을 조회	SELECT E.EMP_ID , E.EMP_NAME , E.MANAGER_ID , NVL((SELECT M.EMP_NAME FROM EMPLOYEE M WHERE E.MANAGER_ID = M.EMP_ID),’없음’) AS 관리자명 FROM EMPLOYEE E ORDER BY 1;

스칼라 서브쿼리 - WHERE절
자신이 속한 직급의 평균 급여보다 많이 받는 직원의 이름, 직급, 급여를 조회	SELECT EMP_NAME , JOB_CODE , SALARY FROM EMPLOYEE E WHERE SALARY >= (SELECT AVG(SALARY) FROM EMPLOYEE E2 WHERE E2.JOB_CODE = E.JOB_CODE) ORDER BY 2;

스칼라 서브쿼리 – ORDER BY절
모든 직원의 사번, 이름, 소속부서를 조회 후 부서명 내림차순으로 정렬	SELECT EMP_ID , EMP_NAME , DEPT_CODE FROM EMPLOYEE ORDER BY (SELECT DEPT_TITLE FROM DEPARTMENT WHERE DEPT_CODE = DEPT_ID) DESC NULLS LAST;

스칼라 서브쿼리 – ORDER BY절

SELECT
DEPT_ID
, DEPT_TITLE
FROM DEPARTMENT
ORDER BY DEPT_TITLE DESC;

FROM절에서의 서브쿼리(인라인뷰)	FROM절에 서브쿼리를 사용한 것을 인라인뷰(INLINE-VIEW)라고 한다
* ROWNUM은 FROM절을 수행하면서 붙여지기 때문에 top-N분석 시 SELECT절에 사용한 ROWNUM이 의미 없게 됨	SELECT ROWNUM , EMP_NAME , SALARY FROM EMPLOYEE WHERE ROWNUM <= 5;
- FROM절에 이미 정렬이 수행된 서브쿼리(인라인뷰)를 적용 시, ROWNUM이 Top-N분석에 사용될 수 있음	SELECT ROWNUM , EMP_NAME , SALARY FROM (SELECT * FROM EMPLOYEE ORDER BY SALARY DESC ) WHERE ROWNUM <= 5;

WITH	서브쿼리에 이름을 붙여 주고, 인라인뷰로 사용 시 서브쿼리의 이름으로 FROM절에 기술할 수 있다. 같은 서브쿼리가 여러 번 사용될 경우에 중복 작성을 피할 수 있고 실행 속도도 빨라진다는 장점
	WITH TOPN_SAL AS (SELECT EMP_ID , EMP_NAME , SALARY FROM EMPLOYEE ORDER BY SALARY DESC) SELECT ROWNUM , EMP_NAME , SALARY FROM TOPN_SAL;

RANK() OVER

SELECT
  순위
  , EMP_NAME
  , SALARY
FROM (SELECT
      EMP_NAME
      , SALARY
      , RANK() OVER(ORDER BY SALARY DESC) AS 순위
FROM EMPLOYEE ORDER BY SALARY DESC);

DENSE_RANK() OVER

SELECT
  순위
  , EMP_NAME
   , SALARY FROM (SELECT
          EMP_NAME
          , SALARY
       , DENSE_RANK() OVER(ORDER BY SALARY DESC) AS 순위
FROM EMPLOYEE
ORDER BY SALARY DESC);

SMALL

'SQL' 카테고리의 다른 글

SQL7 DML(Data Manipulation Language) (0)	2023.01.17
SQL6 DDL(Data Definition Language) (0)	2023.01.17
SQL3 GROUP BY & HAVING (0)	2023.01.16
SQL2 FUNCTION (0)	2023.01.13
SQL FUNCTION3 (0)	2023.01.12

Leaf Of Grass