2. ORACLE/2.1 이론정리

ORACLE_04_이론정리_JOIN, 서브쿼리

마느링 2022. 4. 4. 19:02

    참고 ] having 절 : 그룹화한 결과를 필터링 할때 필터링 조건을 기술하는 절..
           where 절에는 그룹함수를 사용하지 못하지만, having 절에는 그룹함수를 사용할 수 있다.

 

    JOIN : Relation Database Management System (=RDMBS) 에서는 데이터의 중복(=무결성)을

            피하기 위해서 테이블을 분리하고 그럼으로서 테이블들 간 관계가 형성이 되었다. 
            그런데 분리된 테이블에서 데이터를 추출해낼때 사용하는 문법이 JOIN 이다.
            
            참고 ] 오라클역시 er형태의 데이터베이스이다.
            ER : 엔티티(테이블)끼리의 관계를 이야기 하고 이런 관계들로 테이블을 관리하는 데이터베이스를 관계형 
                  데이터베이스라고 한다. 
            
            참고 ] 관계형데이터베이스에서는 여러개의 테이블에서 동시에 검색하는 기능은 이미 가지고 있다.
                    이때 여러개의 테이블에서 데이터 동시에 검색하면 Cartesion Product가 만들어지는데 
                    이 결과에는 정확하지 않은 데이터도 포함되어 있다.
                    따라서 정확한 데이터만 필터링해서 꺼내와야 하는데 이때 이 필터링 하는 작업이 JOIN이라고 한다.
                    
            종류 ] Inner Join : 나열된 테이블들의 결과 집합 안에서 꺼내오는 조인
                        - Equi Join : 조인을 할때 동둥비교연산자로 조인을 하는 경우 
                        - Non Equi Join : 조인을 할때 동등비교연산자 이외의 연산자로 조인하는 경우
                   Outer Join : Catesian Product에 포함되지 않는 데이터를 가져오는 조인
         형식 : 테이블이름, 필드이름 =  테이블이름.필드이름(+) 이때 (+)는 NULL로 표현되어야 할 테이블쪽에 붙여준다.
                   Self Join : 조인을 하는데 대상테이블이 같은 테이블을 사용하는 조인
         

-- 영문색상이름 테이블
DROP TABLE ecolor;
CREATE TABLE ecolor (
    ceno NUMBER(3) -- 영문 칼라 일련번호
        CONSTRAINT ECLR_NO_PK PRIMARY KEY,
    code VARCHAR2(7) -- 칼라 코드값
        CONSTRAINT ECLR_CODE_UK UNIQUE
        CONSTRAINT ECLR_CODE_NN NOT NULL,
    name varchar2(20)
        CONSTRAINT ECLR_NAME_NN NOT NULL
);


-- 데이터추가
INSERT INTO
    ecolor
VALUES(
    100, '#FF0000', 'red'
);

INSERT INTO
    ecolor
VALUES(
    101, '#00FF00', 'green'
);

INSERT INTO
    ecolor
VALUES(
    102, '#0000FF', 'blue'
);

INSERT INTO
    ecolor
VALUES(
    103, '#000000', 'purple'
);

commit;
-- 영문칼라테이블 조회
SELECT * FROM ecolor;
COMMIT; -- 메모리의 작업영역에서 작업한 내용을 데이터베이스에 적용시키는 명령


CREATE TABLE kcolor (
    ckno NUMBER(3)
        CONSTRAINT KCLR_NO_PK PRIMARY KEY,
    code VARCHAR2(7)
        CONSTRAINT KCLR_CODE_UK UNIQUE
        CONSTRAINT KCLR_CODE_NN NOT NULL,
    name varchar2(20)
        CONSTRAINT KCLR_NAME_NN NOT NULL
);

-- 데이터추가
INSERT INTO
    kcolor
VALUES(
    100, '#FF0000', '빨강'
);

INSERT INTO
    kcolor
VALUES(
    101, '#00FF00', '녹색'
);

INSERT INTO
    kcolor
VALUES(
    102, '#0000FF', '파랑'
);

commit;


SELECT * FROM ecolor;
SELECT * FROM kcolor;

SELECT
    *
FROM
    ecolor, kcolor
;



SELECT
    *
FROM
    emp e, emp ee
;

SELECT
    ceno cno, e.code, e.name ename, k.name kname
FROM
    ecolor e, kcolor k
WHERE
    e.code = k.code -- 조인조건
;


ANSI JOIN : 질의명령은 데이터베이스(DBMS)에 따라서 약간씩 그 문법이 달라진다.
ANSI 형식이란 미국국립표준협회(ANSI)에서 공통의 질의명령을 만들고자 해서 통일된 방식의 명령을 만들어놓은 것. 
                    따라서 DBMS 를 가리지 않고 실행이 된다.
                               
    1. Cross Join : 오라클의 Cartesion Product 를 생성하는 조인
        형식 ] SELECT 필드이름,..... FROM 테이블 CROSS JOIN 테이블2;
    2. Inner Join : Equi Join, Non Eqi Join, Self Join
        형식 ] SELECT 필드이름,.... 
               FROM 테이블1 (INNER) JOIN 테이블2.... 
               ON 조인조건
               WHERE 일반조건
        참고 ] 조인조건은 ON 절에서 기술하고 일반조건은 WHERE절에서 기술한다.
               INNER JOIN이 가장 일반적인 조인이기 ‹š문에 INNER 라는 단어를 생략하면 INNER JOIN을 

              해석해서 실행하게 된다.
    3. OUTER JOIN : Cartesion
        형식 ] SELECT 필드이름...
               FROM 테이블1 LEFT || RIGHT || FULL OUTER JOIN 테이블2     참고) || : 또는
               ON 조인조건...;
               이때 방향은 데이터가 있는 쪽 테이블을 가리키면 된다.
        참고 ] 조인이 2개이상 되는 경우에는 
        형식 ] SELECT
               FROM 테이블이름 
               JOIN 테이블2
               ON 조인조건
               JOIN 테이블3
               ON 조인조건2;

CROSS JOIN : 사원 정보와 부서정보를 크로스조인하세요.

SELECT
    *
FROM EMP CROSS JOIN DEPT;



INNER JOIN : 사원들의 사원직급, 부서번호, 부서이름을 조회하세요

SELECT EMAME 사원이름, JOB 직급, E.DEPTNO 부서번호, DNAME 부서이름 
FROM EMP E INNER JOIN DEPT D
ON --조인조건절
E.DEPTNO = D.DEPTNO
;
--81년 입사한 사원들의 사원이름, 직급, 입사년도, 부서이름을 조회하세요
SELECT ENAME 사원이름, JOB 직급, TO_CHAR(HIREDATE,'YY') 입사년도,DNAME 부서이름
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE TO_CHAR(HIREDATE,'YY')= '81'
;
--사원들의 사원이름, 급여, 급여등급을 조회하세요. NON EQUI JOIIN
SELECT ENAME, SAL, GRADE
FROM EMP JOIN  SALGRADE
ON SAL BETWEEN LOSAL AND HISAL;

예 ] 사원들의 사원이름, 상사이름을 조회하세요

-- INNERJOIN VER.
SELECT E.ENAME 사원이름, NVL(S.ENAME,'상사없음') 상사이름
FROM
    EMP E JOIN EMP S
ON
    E.MGR = S.EMPNO(+);


-- OUTERJOIN VER.
SELECT
    E.ENAME 사원이름, S.ENAME 상사이름
FROM
    EMP E LEFT OUTER JOIN EMP S
ON
    E.MGR = S.EMPNO;



예 ] 사원들의 사원이름, 부서이름, 급여, 급여등급을 조회하세요

SELECT ENAME 사원이름, DNAME 부서이름,SAL 급여,GRADE 급여등급
FROM EMP E -- 공통적으로 조인하는 기본테이블을 넣어줘야함
JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
JOIN SALGRADE
ON E.SAL BETWEEN LOSAL AND HISAL;


    NATURAL JOIN : 자동조인 반드시 조인 조건식에 사용하는 필드의 이름이 동일하고 
                   반드시 동일한 필드가 한개일때 사용할 수 있는 조인
                   자동으로 중복되는 필드를 사용해서 조인하기 ‹š문에 조인조건을 기술하지 않는다.
        형식 ] SELECT 필드이름....
                FROM 테이블 1
              NATURAL JOIN 테이블2;
              
    USING JOIN : 반드시 조인 조건식에 사용하는 필드의 이름이 동일한 경우 
                 그리고 같은 이름을 필드가 여러개 존재해도 무방하다.
        형식 ] SELECT 필드이름..
               FROM   테이블1
               JOIN   테이블2
               USING  (조인에 사용할 필드이름)  

예 ] 사원들의 사원이름, 부서이름을 조회하세요

SELECT ENAME 사원이름, DNAME 부서이름
FROM EMP
NATURAL JOIN DEPT; --필드를 꺼내서 똑같은 이름의 필드를 꺼내서 JOIN함


예 ] TMP 테이블과 부서정보 테이블을 이용해서 사원들의 사원이름, 부서위치를 조회하세요

--TMP 테이블 생성
CREATE TABLE TMP
AS 
    SELECT
        E.*, DNAME
        FROM EMP E, DEPT D
        WHERE E.DEPTNO = D.DEPTNO;

--TMP 테이블 조회

SELECT ENAME 사원이름, LOC 부서위치
FROM TMP
JOIN DEPT
USING (DEPTNO);



    부질의(SUB QUERY, 서브질의) : 질의명령 안에 다시 질의명령을 포함하는 경우
                                 포함된 그 질의명령을 서브질의 또는 서브쿼리 라고 부른다.
        예 ] 이름이 SMITH인 사원과 같은 부서에 있는 사원들의 정보를 조회하세요
            ==> 이 경우 SMITH의 부서번호를 알아내기 위해서 먼저 질의명령이 실행되어야 한다.
                SMITH 사원의 부서를 뽑고 (부서가 같다면) 각 사원들의 부서번호 비교.........
            ==> 부서번호를 이용해서 정보를 조회
            
            이때 위의 질의명령을 아래 메인 질의명령에서 사용해서 명령을 보내고 조회해서 사용해야 한다.
            이때 서브질의를 감싸는 질의명령을 메인질의 명령이라고 한다.
            서브질의의 위치에 따른 결과
            1. SELECT 절 :  이 부분에 사용되는 질의명령은 결과가 반드시 한행 , 한 필드가 나와야 한다.
            2. FROM 절 : FROM 절에는 테이블이 나열되어야 한다. 그런데 조회질의 명령의 결과는 마치 테이블과 같다.
                         그러면 이 조회 질의 명령의 결과를 테이블처럼 사용할 수 있는 방법
                          FROM 절 안에 들어가는 서브질의를 특별히 마치 테이블과 같다 해서 INLINE TABLE이라고 

                         부른다.
                         사용은 질의명령을 보낼때 사용한 별칭을 사용해서 데이터를 꺼내야 한다.
            3. WHERE 절
                1. 단일행 단일필드로 결과가 발생하는 경우 : 결과를 비교에 사용하면 된다.
                2. 다중행 단일필드로 결과가 발생하는 경우 
                    -서브질의의 결과가 다중행으로 단일필드로 발생하는 경우 이 ‹š 사용하는 연산자가 
                    IN : 여러개의 데이터 중 하나만 맞으면 되는 경우. 여러값 중 하나와 묵시적으로 동등비교 처리한다.
                    ANY : 여러개의 데이터 중 하나만 맞으면 되는 경우 . 대소비교 연산자도 사용 가능
                    ALL : 여러개의 데이터가 모두 맞으면 되는 경우
                          ==> 동등비교는 사용할 수 없고, 대소비교할 ‹š 사용한다.
                3. 다중필드 복수의 필드로 결과가 발생하는 방법
                    EXISTS : 여러개의 데이터 중 하나만 맞으면 되는 경우, 

 


예 ] 이름이 SMITH인 사원과 같은 부서에 있는 사원들의 정보를 조회하세요

SELECT *
FROM EMP
WHERE DEPTNO = (
        SELECT DEPTNO
        FROM EMP
        WHERE
        ENAME = 'SMITH'
);




예 ] 사원들의 사원이름, 부서번호, 부서이름, 부서위치 조회

SELECT ENAME 사원이름, DEPTNO 부서번호,(
        SELECT DNAME
        FROM DEPT
        WHERE E.DEPTNO = DEPTNO
    ) 부서이름,
    (
        SELECT LOC
        FROM DEPT
        WHERE E.DEPTNO = DEPTNO
    ) 부서위치
FROM EMP E;



IN : 직급이 MANAGER인 사원과 같은 부서에 속한 사원들의 사원이름, 직급 , 부서번호를 조회하세요

SELECT
    ENAME 사원이름, JOB 직급, DEPTNO 부서번호
FROM EMP
WHERE DEPTNO= IN(
    SELECT
        DEPTNO
    FROM
        EMP
    WHERE
    JOB = 'MANAGER'
);


ANY : 각 부서 평균 급여보다 한 부서라도 급여가 높은 사람들의 사원이름, 사원급여, 부서번호를 조회하세요

SELECT
    ENAME 사원이름, SAL 사원급여, DEPTNO 부서번호
FROM EMP
WHERE SAL> ANY( --한 부서라도....
    SELECT AVG(SAL)
    
    FROM EMP
    
    GROUP BY DEPTNO
);

 

Outer Join

SELECT
    ceno cno, e.code, e.name ename, k.name kname
FROM
    ecolor e, kcolor k
WHERE
    e.code = k.code(+) -- 조인조건
;

self join  
예 ] 사원들의 사원이름, 상사번호, 상사이름, 상사급여 를 조회하세요

SELECT
    e.ename 사원이름, e.mgr 상사번호, s.ename 상사이름, s.sal 상사급여
FROM emp e, emp s
WHERE
    e.mgr = s.empno(+) --(+)Outer Join : king에 상사가 없으니까 
    --null값을 추가하기 위하여 null값이 있는 테이블에 (+)를 추가
;


예 ] 사원들의 사원이름, 직급, 급여, 급여등급을 조회하세요

SELECT
    ENAME 사원이름, JOB 직급, SAL 급여, GRADE 급여등급
FROM EMP, SALGRADE
WHERE
SAL BETWEEN LOSAL AND HISAL
;
--사원들의 사원번호, 사원이름,직급, 부서이름, 부서위치를 조회하세요
SELECT EMPNO 사원번호, ENAME 사원이름, JOB 직급, DNAME 부서이름,LOC 부서위치
FROM EMP E, DEPT D --DEPT가 겹쳐서 구분해주기 위함
WHERE
    E.DEPTNO = D.DEPTNO
;


예 ] 81년 입사한 사원의 사원이름, 직급, 입사일, 부서이름을 조회하세요

SELECT ENAME 사원이름, JOB 직급, TO_CHAR(HIREDATE,'YYYY"년 "MM"월" DD"일"') 입사, DNAME 부서이름

FROM  EMP E, DEPT D

WHERE
    E.DEPTNO = D.DEPTNO --조인조건
    AND TO_CHAR(HIREDATE,'YY')='81'; --일반조건

 

저작자표시 (새창열림)