1. 계층형 질의

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

save image

 

. 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 : 모든 전개를 수행한 지정된 조건을 만족하는 데이터만 추출
  • 가상칼럼 제공

가상칼럼

설명

LEVEL

루트 데이터면 1, 하위 데이터면 2, 리프 데이터까지 1 증가

CONNECT_BY_ISLEAF

리프 데이터면 1, 아니면 0

CONNECT_BY_ISCYCLE

자식을 갖는데 해당 데이터가 조상(자신으로부터 루트까지 경로가 존재하는)으로서 존재하면 1, 아니면 0


예제) 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, 역순으로 할때 가장 끝에 있기 때문에

 

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

함수

설명

SYS_CONNECT_BY_PATH

 루트데이터부터 현재까지 전개한 경로 표시

CONNECT_BY_ROOT

 현재 전개할 데이터의 루트 데이터 표시, 단항 연산자


예제) 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 사용 가능


'자격증 공방 > SQLD' 카테고리의 다른 글

제 4-5절 그룹함수  (0) 2018.07.11
제 4-4절 서브쿼리  (0) 2018.07.11
제 4-2절 집합 연산자 (SET OPERATOR)  (0) 2018.07.11
제 4-1절 표준 조인  (0) 2018.07.11
제 3-9절 조인(JOIN)  (0) 2018.07.07

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

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

사용 목적

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

제약 조건

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

 

집합 연산자의 종류

집합 연산자

연산자의 의미

UNION

여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만듬

UNION ALL

여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그래도 결과에 출력, 상호 배타적일때 사용

INTERSECT

여러 개의 SQL문의 결과에 대한 교집합으로 중복된 행은 하나의 행으로 만듬

EXCEPT

SQL문의 결과에서 SQL 문의 결과에 대한 차집합

중복된 행은 하나의 행으로 만듬 ORACLE MINUS 사용

 

save image

 

 

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코드를 추가하여 사용 가능


'자격증 공방 > SQLD' 카테고리의 다른 글

제 4-4절 서브쿼리  (0) 2018.07.11
제 4-3절 계층형 질의와 셀프 조인  (0) 2018.07.11
제 4-1절 표준 조인  (0) 2018.07.11
제 3-9절 조인(JOIN)  (0) 2018.07.07
제 3-8절 ORDER BY절  (0) 2018.07.07

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박사가 처음으로 개발

save image

 

  1. UNION 연산 UNION 기능

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

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

  1. INTERSECTION 연산 INTERSECT 기능

수학적 교집합

  1. DIFFRENCE 연산 EXCEPT(ORACLE MINUS)기능

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

  1. PRODUCT 연산 CROSS JOIN 기능

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

 

. 순수 관계 연산자

save image

  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 비교

save image

'자격증 공방 > SQLD' 카테고리의 다른 글

제 4-3절 계층형 질의와 셀프 조인  (0) 2018.07.11
제 4-2절 집합 연산자 (SET OPERATOR)  (0) 2018.07.11
제 3-9절 조인(JOIN)  (0) 2018.07.07
제 3-8절 ORDER BY절  (0) 2018.07.07
제 3-7절 GROUP BY, HAVING 절  (0) 2018.07.07

 

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 선수명;


'자격증 공방 > SQLD' 카테고리의 다른 글

제 4-2절 집합 연산자 (SET OPERATOR)  (0) 2018.07.11
제 4-1절 표준 조인  (0) 2018.07.11
제 3-8절 ORDER BY절  (0) 2018.07.07
제 3-7절 GROUP BY, HAVING 절  (0) 2018.07.07
제 3-6절 함수  (0) 2018.07.07

 

1. ORDER BY 정렬

칼럼명, SELECT 별명, 또는 순서로 정리 (혼용사용도 가능)

 

SELECT 칼럼명 [ALIAS명]

FROM 테이블명

[WHERE 조건식]

[GROUP BY 칼럼(Column)이나 표현식]

[HAVING 그룹조건식]

[ORDER BY 칼럼(Column)이나 표현식 [ASC 또는 DESC]] ;

 

ASC(Ascending) : 조회한 데이터를 오름차순으로 정렬한다.(기본 값이므로 생략 가능)

DESC(Descending) : 조회한 데이터를 내림차순으로 정렬한다.

 

ORACLE       : 내림차순에서는 NULL값이 앞에 출력, 오름차순에서는 NULL값이 뒤에 출력

SQL SERVER : 내림차순에는 NULL값이 뒤에 출력, 오름차순에는 NULL값이 앞에 출력

 

기본적인 정렬 순서는 오름차순 (숫자형일 경우는 가장 작은 값부터, 날짜형은 가장 빠른 날짜부터)

 

2. SELECT 문장의 실행 순서

 

5. SELECT 칼럼명 [ALIAS명]

1. FROM 테이블명

2. WHERE 조건식

3. GROUP BY 칼럼(Column)이나 표현식

4. HAVING 그룹조건식

6. ORDER BY 칼럼(Column)이나 표현식;

 

1. 발췌 대상 테이블을 참조한다. (FROM)

2. 발췌 대상 데이터가 아닌 것은 제거한다. (WHERE)

3. 행들을 소그룹화 한다. (GROUP BY)

4. 그룹핑된 값의 조건에 맞는 것만을 출력한다. (HAVING)

5. 데이터 값을 출력/계산한다. (SELECT)

6. 데이터를 정렬한다. (ORDER BY)

 

ORDER BY 절에서는 SELECT 목록에 없는 문자형 항목 포함 가능

, SELECT DISTINCT 지정하더거나 GROUP BY , UNION 연산자 있다면 SELECT 목록에 표시 되어야함

예제) SELECT EMPNO, ENAME FROM EMP ORDER BY MGR;

인라인 뷰의 SELECT 절에서 정의한 칼럼은 메인쿼리에서도 사용 가능 그러나 서브쿼리 범위를 벗어나면 이상 사용 불가

GROUP BY 이후 SELECT ORDER BY 절에서는 개별 데이터 사용 불가

SELECT 절에서 정의하지 않은 MAX, SUM, COUNT 집계 함수도 ORDER BY에서는 사용 가능

예제) SELECT JOB

FROM EMP

GROUP BY JOB

HAVING COUNT(*) > 0

ORDER BY MAX(EMPNO), MAX(MGR), SUM(SAL), COUNT(DEPTNO), MAX(HIREDATE);

 

3. TOP N 쿼리

 

. ROWNUM

ORACLE 순위가 높은 N개의 로우를 추출하기 위해 WHERE절에서 ROWNUM 함수 사용

, 원하는 데이터를 원할 없음 데이터 일부가 먼저 추출된 정렬작업이 일어남, ORDER BY 효과가 없음

인라인 뷰로 먼저 데이터 정렬 사용 가능, 인덱스로 추출가능

 

. TOP ( )

SQL SERVER TOP 조건을 사용하여 사용 가능

TOP (Expression) [PERCENT] [WITH TIES]

WITH TIES : 마지막 행으로 표시되는 추가 행의 데이터가 같을 경우 N+ 동일 정렬 순서를 추가 반환 하도록 지정된 옵션


'자격증 공방 > SQLD' 카테고리의 다른 글

제 4-1절 표준 조인  (0) 2018.07.11
제 3-9절 조인(JOIN)  (0) 2018.07.07
제 3-7절 GROUP BY, HAVING 절  (0) 2018.07.07
제 3-6절 함수  (0) 2018.07.07
제 3-5절 WHERE 절  (0) 2018.07.07

+ Recent posts