23-02-03(1) 데이터 베이스(JOIN, 단일행 서브쿼리)

1. ANSI JOIN

 

※ INNER JOIN(내부조인, 교집합)

1) NATURAL JOIN (자연조인)

자연조인의 사용예)

 SELECT STUDNO, NAME, DEPTNO, DNAME
 FROM STUDENT
 NATURAL JOIN DEPARTMENT;
 -- 자연조인 사용

2) USING

 SELECT STUDNO, NAME, DEPTNO, DNAME
 FROM STUDENT
 JOIN DEPARTMENT USING(DEPTNO);

3) ON (?와 ?가 같을때)

 SELECT STUDNO, NAME, STUDENT.DEPTNO, DNAME
 FROM STUDENT
 JOIN DEPARTMENT ON STUDENT.DEPTNO = DEPARTMENT.DEPTNO
 WHERE NAME = '전인하';

※참고

앞에서 배운 집합(UNION, UNIONALL, MINUS 등)은 행을 기준으로 집합하고,

JOIN은 컬럼을 기준으로 집합한다고 생각하면 된다.

 

2. 카티션 곱 (카티션 product)

두개 이상의 테이블에 대해 연결가능한 행을 모두 결합

where절에서 조인 조건절을 생략하거나 잘못 설정한 경우

대용량 테이블에서 발생할 경우 sql명령문의 처리속도 저하

개발자가 시뮬레이션을 위한 대용량의 실험용 데이터를 생성하기 위해 의도적으로 사용 가능

1) CROSS JOIN 사용 예

코드의 가독성을 위해 컴마 구분이 아닌 CROSS JOIN을 기입해서 사용하는게 좋다.

 

3. EQUI JOIN

 연산방법을 가지고 조인하는 것

 조인 대상 테이블에서 공통 칼럼을 '='(EQUAL) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 생성하는 조인 방법

 SQP명령문에서 가장 많이 사용되는 조인 방법

 

1) WHERE절을 이용한 EQUI조인 사용법

FROM 조인대상 테이블을 기술한다. 테이블은 콤마로 구분

WHERE 조인 애트리뷰트와 '='연산자를 사용하여 조인 조건을 기술

 

예제) 학생테이블과 부서 테이블을 EQUI JOIN하여 학번, 이름, 학과번호 소속학과이름, 학과위치 출력

 SELECT S.STUDNO, S.NAME, S.DEPTNO, D.DNAME, D.LOC
 FROM STUDENT S, DEPARTMENT D
 WHERE S.DEPTNO = D.DEPTNO;

 

※ 참고

행 개수를 줄이고 싶으면 GROUPING을 하면 되고,

행 개수를 늘릴거면 카티션곱이나 UNION을 하면 된다.

 

 

2) NATURAL JOIN

 공통된 컬럼명 앞에 별명을 붙일 수 없다.

 SELECT S.STUDNO, S.DEPTNO
 FROM STUDENT S
 NATURAL JOIN DEPARTMENT D;

예제) 자연조인을 이용하여 교수번호, 이름,학과번호, 학과이름 출력

 SELECT PROFNO, NAME, DEPTNO, DNAME
 FROM PROFESSOR 
 NATURAL JOIN DEPARTMENT ;

 

3) JOIN ~USING

 -USING절에 조인 대상 칼럼을 지정

 - 칼럼이름은 조인대상 테이블에서 동일한 이름으로 정의되어 있어야함

 

예제) JOIN ~ USING 절을 이용하여 학번, 이름, 학과번호, 학과이름, 학과위치 출력

 SELECT STUDNO, NAME, DEPTNO, DNAME, LOC
 FROM STUDENT
 JOIN DEPARTMENT 
 USING (DEPTNO);

 

4) NON-EQUI JOIN

BETWEEN a AND b 와 같이  '='조건이 아닌 연산자 사용

 

예제) 교수테이블과 급여등급 테이블을 NON-EQUI JOIN하여 교수별로 등급을 출력

 SELECT P.PROFNO, P.NAME, P.SAL, S.GRADE
 FROM PROFESSOR P
 JOIN SALGRADE S ON P.SAL BETWEEN LOSAL AND HISAL;

4. OUTER JOIN (완전중요 / OUTER JOIN이 제일 어려움)

 EQUI JOIN의 조인 조건에서 양측 칼럼값 중, 

기본적으로 INNER JOIN을 포함

차집합과 비슷함 

예시)

SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
JOIN PROFESSOR P USING(PROFNO);

STUDENT TABLE(16행)을 A집합

PROFESSOR TABLE(8행)을 B집합

집합조건은 PROFNO을 해서 집합형태를 만들었다.

주의해야할 점은 지도교수가 있는 학생은 10명, 지도학생이 있는 교수는 5명 즉, 일대다 ???

JOIN은 A와 B의 중복되는 부분(교집합)을 출력한것이고

OUTER JOIN의 LEFT와 RIGHT는 JOIN 앞에 붙으며 LEFT를 사용하면 STUDENT 테이블을 기준으로 하여

SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
LEFT JOIN PROFESSOR P USING(PROFNO);

A집합과 B집합의 중복되는부분(지도교수가 있는 학생)를 전부 출력하고 중복되지않는 A집합의 6개의 행도 전부 출력한다.

RIGHT를 사용하면 PROFESSOR을 기준으로 하여

SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
RIGHT JOIN PROFESSOR P USING(PROFNO);

B집합의 중복되는부분(담당교수가 있는 학생)을 전부 출력하고, 중복되지않는 B집합(담당학생이 없는 교수)도 전부 출력한다.

A, B집합의 합집합 상태를 보려면 FULL이라는 키워드를 쓰면된다.

SELECT STUDNO, S.NAME, PROFNO, P.NAME
FROM STUDENT S
FULL JOIN PROFESSOR P USING(PROFNO)
ORDER BY 1, 3;

OUTER JOIN 연산자 사용금지

※ 집합 예 삽입 필요

 

5. SELF JOIN

 하나의 테이블 내에 있는 칼럼끼리 연결하는 조인이 필요한 경우 사용

 조인 대상 테이블이 자신 하나라는 것 외에는 EQUI JOIN과 동일

-- 1번
SELECT D1.DNAME || '의 상위학과는 ' || NVL(D2.DNAME, '없습니다') A
FROM DEPARTMENT D1, DEPARTMENT D2
WHERE D1.COLLEGE = D2.DEPTNO(+);
-- 2번
SELECT D1.DNAME || '의 상위학과는 ' || NVL(D2.DNAME, '없습니다') A
FROM DEPARTMENT D1 LEFT JOIN DEPARTMENT D2
ON D1.COLLEGE = D2.DEPTNO;

 

※ 서브쿼리

1. 개요

 하나의 SQL명령문의 결과를 다른 SQL명령문에 전달하기 위해 두개이상의 SQL.명령문을 하나의 SQL명령문으로 연결하여 처리하는 방법

 

1-1) 필요성

예 : 교수테이블에서 '전은지'교수와 직급이 동일한 모든 교수의 이름 검색

 - 처리방식

  (ㄱ) 교수테이블에서 '전은지'교수의 직급 검색 SQL명령문 실행

  (ㄴ) 교수테이블의 직급 칼럼에서 1에서 얻은 결과 값과 동일한 직급을 가진 교수 검색 명령문 실행

 (ㄱ), (ㄴ) SQL명령문 처리과정을 통합하여 하나의 SQL명령문으로 처리

2. 단일행 서브쿼리

 - 서브쿼리에서 단일행 및 단일컬럼을 검색하여 메인쿼리에 반환하는 질의문

 - 메인쿼리의 WHERE절에서 서브쿼리의 결과와 비교할 경우에는 반드시 단일행 비교연산자중 하나만 사용가능

    - 단일 행 비교연산자 : =, >, >=, <, <>, <=

 - 서브쿼리의 결과로 하나의 행만이 출력되어야 함

 

1) '='연산자를 이용한 단일행 서브쿼리

예제) 사용자 아이디가 'jun123'인 학생과 같은 학년인 학생의 학번, 이름, 학년을 출력

SELECT STUDNO, NAME, GRADE
FROM STUDENT
WHERE GRADE = (
    SELECT GRADE
    FROM STUDENT
    WHERE USERID = 'jun123'
);

예제2) 101번 학과 학생들의 평균 몸무게보다 몸무게가 적은 학생의 이름, 학과번호, 몸무게 출력

SELECT NAME, DEPTNO, WEIGHT 
FROM STUDENT
WHERE WEIGHT < (SELECT AVG(WEIGHT)
                FROM STUDENT
                WHERE DEPTNO = 101)
ORDER BY DEPTNO;

예제3) 20101번 학생과 학년이 같고(서브쿼리1), 키는 20101번 학생보다 큰 학생(서브쿼리2) 의 이름, 학년, 키를 출력

SELECT NAME, GRADE, HEIGHT
FROM STUDENT 
WHERE GRADE =  (SELECT GRADE
                FROM STUDENT
                WHERE STUDNO = 20101)
AND HEIGHT > (SELECT HEIGHT
            FROM STUDENT
            WHERE STUDNO = 20101);

 

※ 서브쿼리와 조인을 비교한 예제)

SELECT STUDNO, NAME, DEPTNO, 
    (SELECT DNAME FROM DEPARTMENT D WHERE D.DEPTNO = S.DEPTNO) DNAME
FROM STUDENT S;
-- 서브쿼리를 사용한 예


SELECT STUDNO, NAME, DNAME
FROM STUDENT
NATURAL JOIN DEPARTMENT;
-- 자연조인을 사용한 예