문과생이 공대에서 살아남기

하나의 SQL문안에 포함되어 있는 또 다른 SQL문

알려지지 않은 기준을 이용해 검색하기 위해 사용

서브쿼리는 메인쿼리의 칼럼을 모두 사용 가능

메인쿼리는 서브쿼리의 칼럼을 사용할 수 없음 (필요하다면 JOIN으로 변경하거나 스칼라 서브쿼리를 사용해야함)

서브쿼리 레벨과 상관없이 항상 메인쿼리 레벨로 결과로 생성되어 1:M의 관계에서도 결과는 1이 됨

주의사항

1. 서브쿼리를 괄호로 감싸서 사용해야함
2. 단일행 또는 복수행 비교 연산자와 함께 사용가능

단일 행 비교연산자는 서브쿼리 결과가 반드시 1건 이하여야 하고

복수 행 비교연산자는 결과 건수와 상관없음

3. 서브쿼리는 ORDER BY를 사용하지 못함, 메인쿼리의 마지막 문장에 위치해야함
4. 사용 가능한 곳 - SELELC, FROM, WHERE, HAVING, ORDER BY, INSERT의 VALUES절, UADATE의 SET절

동작하는 방식에 따른 서브쿼리 분류

• UN-CORRELATED (비연관) 서브쿼리 : 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않은 형태의 서브쿼리

메인쿼리에 값(서브쿼리가 실행된결과)을 제공하기 위한 목적으로 사용

• CORREGATED(연관) 서브쿼리 : 서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태의 서브쿼리

      메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리 조건에 맞는지 확인하고자 할 때 주로 사용

반환되는 데이터의 형태에 따른 서브 쿼리 분류

1. 단일 행 서브쿼리

단일 행 비교 연산자 ( =, >, >=, <, <=, <>) 와 함께 사용할 때는 서브쿼리 결과가 반드시 1건 이하여야 함

2건 이상을 반환하면 SQL문은 실행시간 오류가 발생, 컴파일할 때 알수 없는 오류

예제) SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버

 FROM PLAYER

 WHERE HEIGHT <= (SELECT AVG(HEIGHT) FROM PLAYER)

 ORDER BY PLAYER_NAME;

2. 다중 행 서브쿼리

결과가 2건 이상이면 다중 행 비교 연산자 ( IN, ALL, ANY, SOME)을 사용해야 함

다중행 서브쿼리 비교 연산자는 단일 행 서브쿼리의 비교 연산자로도 사용가능

3. 다중 칼럼 서브쿼리

여러 개의 칼럼이 반환된어 메인쿼리의 조건과 동일하게 비교되는 것을 의미, SQL SERVER에서는 지원되지 않음

예제)  SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

  FROM PLAYER

  WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT) FROM PLAYER GROUP BY TEAM_ID)

  ORDER BY TEAM_ID, PLAYER_NAME;

4. 연관 서브 쿼리

서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리

EXISTS 서브쿼리 : 만족하는 1건만 찾음 추가검색 진행하지 않음

예제) SELECT STADIUM_ID ID, STADIUM_NAME 경기장명

FROM STADIUM A

WHERE EXISTS (SELECT 1 FROM SCHEDULE X WHERE X.STADIUM_ID = A.STADIUM_ID AND X.SCHE_DATE BETWEEN '20120501' AND '20120502');

5. 그 밖에 위치에서 사용 하는 서브쿼리

가. SELECT 절

스칼라 서브쿼리란 ? 한 행, 한 컬럼만을 반환하는 서브쿼리, 단일 행 서브쿼리 (2건이상이면 오류)

예제) SELECT PLAYER_NAME 선수명, HEIGHT 키, (SELECT AVG(HEIGHT) FROM PLAYER X WHERE X.TEAM_ID = P.TEAM_ID) 팀평균키

 FROM PLAYER P;

나. FROM 절

인라인뷰(INLINE VIEW), 동적뷰 (DYNAMIC VIEW)

서브쿼리 결과가 동적으로 생성된 테이블인 것 처럼 사용 가능, 데이터 베이스에는 해당 정보가 저장되지 않음

조인방식을 사용하는 것과 같아서 인라인 뷰의 칼럼은 SQL에 자유롭게 참조 가능

ORDER BY 사용가능,

TOP-N 쿼리 : 인사인 뷰에서 먼저 정렬을 수행하고 결과 중에 일부 데이터만 추출하는 것, ORACLE은 ROWNUM으로 사용가능

예제) SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키

FROM (SELECT PLAYER_NAME, POSITION, BACK_NO, HEIGHT FROM PLAYER WHERE HEIGHT IS NOT NULL ORDER BY HEIGHT DESC)

WHERE ROWNUM <= 5;

• 데이터까지 전부 추출하고싶으면 RANK 함수 사용

다. HAVING 절

그룹핑 결과에 대한 부가적인 조건 주기 위해서

라. UPDATE 문의 SET 절

새로운 테이블에 이전 테이블의 값을 옮기고 싶을 때

NULL값을 반환할 경우 칼럼 결과가 NULL이 될수 있음으로 주의해야함

마. INSERT 문의 VALUES 절

새로운 테이블에 이전 테이블의 값, 또는 수정값을 옮길 때

6. 뷰(VIEW)

가상 테이블(VIRTUAL TABLE) 실제 데이터를 가지고 있지 않음, 뷰 정의(VIEW DEFINITION)을 가지고 있음

뷰 정의를 참조해서 질의를 재 작성하여 질의를 수행

이미 존재하는 뷰를 참조해서도 생성 가능

실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 가진 DBMS도 있음

장점

1. 독립성 : 테이블 구조가 변경되어도 뷰를 사용하는 프로그램 변경 없음
2. 편리성 : 복잡한 질의를 뷰로 생성하여 관련 질의를 단순하게 작업할 수 있음, 해당 SQL을 자주 사용 할때 뷰를 이용하면 편리하게 사용
3. 보안성 : 감추고 싶은 정보를 뷰를 생성할때 제외하고 생성 가능

예제) CREATE VIEW V_PLAYER_TEAM

AS SELECT P.PLAYER_NAME, P.POSITION, P.BACK_NO, P.TEAM_ID, T.TEAM_NAME

FROM PLAYER P, TEAM T

WHERE P.TEAM_ID = T.TEAM_ID;

제거하기 : DROP VIEW V_PLAYER_TEAM;

        DROP VIEW V_PLAYER_TEAM_FILTER;

1. 계층형 질의

계층형 데이터란 ? 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터

가. ORACLE 계층형 질의

SELECT 컬럼명

FROM 테이블

WHERE 조건 AND 조건

START WITH 조건

CONNECT BY [NOCYCLE] 조건 AND 조건

[ORDER SIBLING BY 칼럼명 … ] ;

• STRART WITH : 전개의 시작위치 지정, 루트 데이트 지정
• CONNECT BY : 다음에 전개되는 자식 데이터를 지정하는 구문
• PRIOR 자식 = 부모 : 자식데이터에서 부모방향으로 전개하는 순방향 전개
• PRIOR 부모 = 자식 : 부모데이터에서 자식방향으로 전개하는 역방향 전개
• ORACLE에서는 PRIOR은 SELECT, WHERE절에서도 사용 가능
• NOCYCLE : 데이터를 전개하다 이미 나타난 동일한 데이터가 나타나면 오류가 발생되는데 그것을 방지하기 위해 설정
• ORDER SIBLINGS BY : 형제 노느 사이에서 정렬을 수행
• WHERE : 모든 전개를 수행한 후 지정된 조건을 만족하는 데이터만 추출
• 가상칼럼 제공

예제) SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || 사원 사원, 관리자, CONNECT_BY_ISLEAF ISLEAF

FROM 사원

START WITH 관리자 IS NULL

CONNECT BY PRIOR 사원 = 관리자;

[실행 결과] LEVEL 사원 관리자 ISLEAF

------- -------- ------- -------

1        A                         0

2           B          A          1

2           C          A          0

3              D       C          1

3              E        C          1

• LEFT 데이터는 B, D, E 임 / C는 또 다른 LEFT들이 있기에 0

예제) SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || 사원 사원, 관리자, CONNECT_BY_ISLEAF ISLEAF

FROM 사원

START WITH 사원 = 'D'

CONNECT BY PRIOR 관리자 = 사원;

[실행 결과] LEVEL 사원 관리자 ISLEAF

-------- ----- -------- --------

1         D           C          0

2            C         A         0

3               A                 1

• LEFT 데이터는 A, 역순으로 할때 가장 끝에 있기 때문에

계층형 질의에서 사용되는 함수

예제) SELECT CONNECT_BY_ROOT 사원 루트사원, SYS_CONNECT_BY_PATH(사원, '/') 경로, 사원, 관리자

FROM 사원

START WITH 관리자 IS NULL

CONNECT BY PRIOR 사원 = 관리자;

[실행 결과] 루트사원  경로   사원  관리자

 ----------  -----   ------  ------

A             /A         A

A             /A/B      B      A

A             /A/C      C     A

A             /A/C/D  D     C

A             /A/C/E   E     C

• 모두 시작은 A이기에 루트사원은 A

나. SQL SERVER 계층질의

WITH EMPLOYEES_ANCHOR AS (

SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO, 0 AS LEVEL

FROM EMPLOYEES

WHERE REPORTSTO IS NULL /* 재귀 호출의 시작점 */

UNION ALL

SELECT R.EMPLOYEEID, R.LASTNAME, R.FIRSTNAME, R.REPORTSTO, A.LEVEL + 1

FROM EMPLOYEES_ANCHOR A, EMPLOYEES R

WHERE A.EMPLOYEEID = R.REPORTSTO )

SELECT LEVEL, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO

FROM EMPLOYEES_ANCHOR

GO

• 상위 쿼리를 앵커 맴버, 아래있는 쿼리는 재귀 멤버, OUTER 집합이 되어 INNER 집합인 재귀맴버와 조인을 반복
1. CTE 식을 앵커 맴버와 재귀 맴버로 분할
2. 앵커 맴버를 실행하여 첫 호출 또는 기본 결과 집합 만듬
3. Ti는 입력하고 Ti+1은 출력하여 재귀맴버 실행
4. 빈집합이 될때까지 반복
5. 결과집합 반환
• SORT 구분 컬럼 추가와 조직도와 같은 모습으로 출력하려면 ORDER BY절에서 정렬 필요

2. 셀프조인

동일 테이블 사이의 조인, FROM 절에서 동일 테이블이 두번이상 나타남

테이블 별칭을 반드시 사용해야 함, 칼럼에도 어느 테이블 칼럼인지 식별 필요

SELECT ALIAS명1.칼럼명, ALIAS명2.칼럼명, ...

FROM 테이블1 ALIAS명1, 테이블2 ALIAS명2

WHERE ALIAS명1.칼럼명2 = ALIAS명2.칼럼명1;

차상위 데이터를 추출할 때 누락되지 않도록 OUTER JOIN 사용 가능

조인을 사용하지 않고 연관된 데이터를 조회하는 방법

여러개의 질의의 결과를 연결하여 하나로 결합하는 방식, 2개이상의 질의 결과를 하나로 만들어줌

사용 목적

• 서로 다른 테이블에서 유사한 형태의 결과를 반환하는 것을 하나의 결과로 합치고자 할 때
• 동일 테이블에서 서로 다른 질의를 수행하여 결과를 합치고자 할 때
• 튜닝관점에서 실행계획을 분리하고자 하는 목적

제약 조건

• SELECT 절의 칼럼수가 동일
• 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환 가능해야 함 (동일한 데이터 타입일 필요 없음)

집합 연산자의 종류

SELECT 칼럼명1, 칼럼명2, ...

FROM 테이블명1

[WHERE 조건식 ]

[[GROUP BY 칼럼(Column)이나 표현식 [HAVING 그룹조건식 ] ]

집합 연산자

SELECT 칼럼명1, 칼럼명2, ...

FROM 테이블명2

[WHERE 조건식 ]

[[GROUP BY 칼럼(Column)이나 표현식 [HAVING 그룹조건식 ] ]

[ORDER BY 1, 2 [ASC또는 DESC ] ;

집합연산자는 여러개의 SELECT 문을 연결하는 것에 지나지 않으나

ORDER BY 문은 최종 경과에 대한 정렬으로 마지막 줄에 한번만 기술

UNION 연산자 사용하여 IN연산자와 OR연산자는 정렬 순서만 다를 뿐 같은 결과값을 냄

그룹함수도 집합연산자 사용이 가능함, GUBUN코드를 추가하여 사용 가능

1. STANSARD SQL 개요

새로운 기능을 포함

- STANDARD JOIN 기능 추가 (CROSS, OUTER JOIN 등 새로운 FROM 절 JOIN 기능들)

- SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 SUBQUERY 기능들

- ROLLUP, CUBE, GROUPING SETS 등의 새로운 리포팅 기능

- WINDOW FUNCTION 같은 새로운 개념의 분석 기능들

가. 일반 집합 연산자

E.F.Codd박사가 처음으로 개발

1. UNION 연산 → UNION 기능

수학적 합집합 제공, 공통의 교집합 중복을 없애기 위한 작업

UNION ALL은 공통 집합을 중복해서 그대로 보여줌, 정렬작업이 일어나지 않아서 사용 권고함

2. INTERSECTION 연산 → INTERSECT 기능

수학적 교집합

3. DIFFRENCE 연산 → EXCEPT(ORACLE은 MINUS)기능

수학적 차집합, 첫번째 집합에서 두번째 집합과의 공통집합을 제외한 부분

4. PRODUCT 연산 → CROSS JOIN 기능

곱집합, 생길수있는 모든 데이터 조합으로 양 집합의 M*N건, CARTESIAN PRODUCT로도 불림

나. 순수 관계 연산자

1. SELECT 연산 → WHRER절로 구현 SELECT연산과 절은 다른 의미
2. PROJECT 연산 → SELECT절로 구현 칼럼 선택기능 수현
3. (NATURAL) JOIN 연산 → 다양한 JOIN 기능으로 구현 INNER, OUTER, USING, ON 등 다양하게 발전
4. DIVIDE연산 사용되지 않음

관계형 데이터 베이스

요구사항분석, 개념적 데이터 모델링 → 논리적 데이터 모델링 → 물리적 데이터 모델링

엔티티확정 및 정규화 과정, M:M 관계 분해하는 절타

특히, 정규화과정은 데이터 정합성과 데이터 저장공간 절약을 위해 엔티티 최대한 분리하는 작업으로 3차정규형이나 보이스코드 정규화까지 진행

정규화 이후 하나의 주제에 관련한 여러 엔티티가 나눠지고 그것들은 연결하기 위해 JOIN 작업이 이루어짐

2. FROM 절 JOIN 형태

• INNER JOIN     
• NATURAL JOIN  : INNER JOIN의 하위개념, 동일한 이름을 갖는 모든 칼럼에 대해 EQUI조인 수행
• USING 조건절   : 동일한 칼럼명
• ON 조건절      : WHERE 조인 조건처럼 ON 조건절로 분리하여 표시, 명시적으로 조건 구분할 수 있고 컬럼명이 다르더라도 사용가능
• CROSS JOIN
• OUTER JOIN

3. INNER JOIN

내부 JOIN, 동일한 값이 있는 행으로만 반환

USING 조건절, ON 조건절을 사용

예제) SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME

 FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;

4. NATURAL JOIN

두 테이블간 동일한 이름이 갖는 모든 칼럼에 대해 EQUI JOIN을 수행

USING/ON/WHERE 조건절에서 정의 불가능, SQL SERVER 사용 불가

동일한 칼럼 데이터가 같은 타입이여야하고 별명이나 접두사를 붙일 수 없음, 도메인과 칼럼명이 동일해야함

5. USING 조건절

FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 EQUI JOIN 할수 있음,

SQL SERVER 사용 불가

별명과 접두사를 붙일 수 없음

6. ON 조건절

WHERE 조건절과 불리한 JOIN 조건절, 칼럼명이 달라도 사용가능

임의의 JOIN 조건을 지정하거나 이름이 다른 칼럼명을 JOIN하거나 칼럼을 명시하기 위해 사용

별명이나 접두사를 사용하여 SELECT절에 사용되는 칼럼 명확하게 지정 필요

가독성이 떨어지는 단점

가. WHERE 절과의 혼용

나. ON 조건절 + 데이터 검증 조건 추가

: ON 조건절 안에서도 데이터 검색 조건 추가가능 단, OUTER JOIN에서 추가 조건 경우는 ON 절에 표기

다. ON 조건절 예제

라. 다중 테이블 JOIN

7. CROSS JOIN

CARTESIAN PRODUCT 또는 CROSS PRODUCT로 표현, M*N건으로 조합, 모든 데이터 조합을 말함

WHERE절에 조인조건 추가 가능하지만 INNER JOIN과 같은 효과이기 때문에 사용하는 의미가 없음

8. OUTER JOIN

동일한 값이 없는 행도 반환할 때 사용

ORACLE은 칼럼 뒤에 (+) 표시

단, WHERE절 검색조건의 불분확한 단점, IN이나 OR연산자 사용시 에러, (+) 누락 칼럼 존재시 오류 발생, FULL OUTER JOIN 미지원

가. LEFT OUTER JOIN

왼쪽에 있는 값은 동일한 조인 값이외에도 전부 출력, LEFT JOIN 으로도 생략해서 사용 가능

나. RIGHT OUTER JOIN

오른쪽 있는 값은 동일한 조인 값이외에도 전부 출력, RIGHT JOIN 으로 생략해서 사용 가능

다. FULL OUTER JOIN

왼, 오른쪽 데이터를 모두 읽어 결과 생성, LEFT와 RIGHT의 합집합 결과랑 동일

단, UNION ALL이 아닌 UNION 기능과 같으므로 중복된 데이터는 삭제

FULL JOIN으로 생략해서 사용 가능

9. INNER VS. OUTER VS. CROSS JOIN 비교

1. JOIN의 개요

두 개 이상의 테이블 들을 연결 또는 결합하여 데이터를 출력하는 것

PK, FK와 연관되어 있거나 혹은 없어도 논리적 값 연결로도 가능

여러 테이블과 나열이 되어도 처리할 때는 단 두개의 집합 간에만 조인이 일어남

원하는 조인 테이블 개수 - 1 은 이후 집합간의 조인 갯수

2. EQUI JOIN

등가조인, 서로 정확하게 일치하는 값을 가진 경우, 관계형 데이터베이스의 큰 장점

대부분 PK, FK 관계로 이루어짐 (모든것은 아님)

SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...

FROM 테이블1, 테이블2

WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2; → WHERE 절에 JOIN 조건을 넣는다

SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...

FROM 테이블1 INNER JOIN 테이블2

ON 테이블1.칼럼명1 = 테이블2.칼럼명2; → ON 절에 JOIN 조건을 넣는다.

SELECT 구문에서 유일한 이름을 가진 컬럼이라면 테이블명을 붙이지않아도 되지만 두 테이블에 동일한 칼럼명이 있다면 지정해주어야 함

또한 개발자나 사용자의 가독성이나 유지보수성을 높이는 효과, 오류를 방지하고 일관성을 위해 테이블명을 지정해주는 습관 필요

N개의 테이블로부터 필요한 데이터를 조회하기 위한 JOIN조건은 N-1개 필요함

테이블 명이 길어 SELECT 구문이 길어짐으로 FROM절의 테이블에도 별명을 사용할 수 있음

별명 사용시에는 SELECT, WHERE절에서는 별명을 사용해야 함

3. NON EQUI JOIN

비등가조인, 서로 값이 일치하지 않는 경우 >, < , >=, <= , BETWEEN 연산자 사용(모델에 따라 불가능할 수 있음)

SELECT 테이블1.칼럼명, 테이블2.칼럼명, ...

FROM 테이블1, 테이블2

WHERE 테이블1.칼럼명1 BETWEEN 테이블2.칼럼명1 AND 테이블2.칼럼명2;

4. 3개 이상 TABEL JOIN

예제)SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지, T.TEAM_NAME 팀명, S.STADIUM_NAME 구장명

FROM PLAYER P, TEAM T, STADIUM S

WHERE P.TEAM_ID = T.TEAM_ID AND T.STADIUM_ID = S.STADIUM_ID

ORDER BY 선수명;

또는 INNER JOIN을 명시하여 사용할 수도 있다.

SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, T.REGION_NAME 연고지, T.TEAM_NAME 팀명, S.STADIUM_NAME 구장명

FROM PLAYER P INNER JOIN TEAM T

ON P.TEAM_ID = T.TEAM_ID

INNER JOIN STADIUM S

ON T.STADIUM_ID = S.STADIUM_ID

ORDER BY 선수명;