[SQLD] 2-2. SQL 활용

SQL/SQLD

유달잇 2021. 5. 25. 19:19

728x90

1. 표준 조인

1-1. SQL 연산 정리

종 류	연 산	기 능	뜻
일반 집합 연산	UNION	UNION	합집합
	INTERSECTION	INTERSECT	교집합
	DIFFERENCE	MINUS (오라클) EXCEPT (SQL Server)	차집합
	PRODUCT	CROSS JOIN	곱집합 (생길 수 있는 모든 데이터 조합)
순수 관계 연산	SELECT	WHERE 절	조건에 맞는 행 조회
	PROJECT	SELECT 절	조건에 맞는 칼럼 조회
	(NATURAL)JOIN	여러 JOIN
	DIVIDE	없음	공통요소를 추출하고 분모 릴레이션의 속성을 삭제한 후 중복된 행 제거

1-2. JOIN의 종류

INNER JOIN
NATURAL JOIN
CROSS JOIN
OUTER JOIN
SELF JOIN
NON-EQUI JOIN

1-3. INNER JOIN ( =EQUI JOIN)

행에 동일한 값이 있는 칼럼을 내부 JOIN 하는 방법이다.

  
  [Oracle]
  SELECT * 
  FROM t1, t2
  WHERE t1.column = t2.column;
  
  [ANSI]
  SELECT * 
  FROM M INNER JOIN S ON M1=S1;

FROM 절에 USING(칼럼명)을 사용하여 조건을 주어 원하는 칼럼을 조인할 수 있다. (별명, 테이블명 사용 불가능)
데이터 유형이 일치하지 않을 경우 칼럼명을 지정하여 사용한다.

  SELECT 칼럼명,,
  FROM 테이블1 JOIN 테이블2 
  USING(칼럼명);
  
  EX)
  SELECT ENAME, DNAME 
  FROM EMP JOIN DEPT 
  USING(DEPTNO);

-> 1. FROM : EMP 테이블과 DEPT 테이블을 조인한다. DEPTNO 컬럼값이 같은 경우에

2. SELECT : ENAME, DNAME 칼럼을 조회한다.

ON 조건절을 사용하면 칼럼명이 달라도 조인할 수 있다. (별명이나 테이블명 사용 가능)
임의의 조건을 지정하거나 조인한 칼럼을 지정할 경우 사용하며 조인 조건만 ON 절에 기술하고 다른 조건은 WHERE 절에 기술한다.

  
  SELECT 칼럼명,,
  FROM 테이블1 A JOIN 테이블2 B 
  ON (A.칼럼명=B.칼럼명);
  
  EX)
  SELECT E.ENO, E.ENAME, D.DNAME, D.DNO
  FROM EMP E JOIN DEPT D
  ON E.DNO = D.DNO
  WHERE E.ENO = 7788;

-> 1. FROM : EMP테이블(별명 E)과 DEPT 테이블(별명 D)을 조인한다. DNO칼럼이 같은 경우

2. WHERE : EMP테이블의 ENO칼럼값이 7788인 경우

3. SELECT : EMP테이블의 ENO, ENAME 칼럼과 DEPT 테이블의 DNAME, DNO 칼럼을 조회한다.

1-4. NATUAL JOIN

두 테이블간 동일한 칼럼 이름을 가진 경우에 대해 EQUI JOIN을 수행한다.
USING과 ON 조건을 줄 수 없다.
JOIN에 사용된 칼럼은 같은 데이터 타입이어야 한다.
별명이나 테이블명 사용 불가능

1-5. CROSS JOIN

각 테이블의 모든 행에 대해서 가능한 모든 조합을 가지는 쿼리 결과를 만들어 내는 조인이다.

  
  [ANSI]
  SELECT * FROM M CROSS JOIN S  

  [SQL Server]
  SELECT * FROM M,S

M * S 건의 데이터 조합이 발생한다.

1-6. OUTER JOIN

주, 종 관계를 두고 조인 조건을 만족하는 값만 가져오는 외부(OUTER) 조인이다.
양측 칼럼 값 중의 하나가 NULL이지만 조인 결과로 출력하고 싶은 경우에 사용한다.
(+) 기호 사용

LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN ( = LEFT OUTER JOIN + RIGHT OUTER JOIN으로 즉, 둘의 합집합 )

  [RIGHT OUTER JOIN]
  SELECT TABLE.COL, TABLE2.COL
  FROM TABLE1, TABLE2
  TABLE1.COL = TABLE2.COL(+);

  [RIGHT OUTER JOIN]
  SELECT T1.COL, T2.COL
  FROM T1, T2
  WHERE T1.COL(+) = T2.COL;
  (=ON)

  [ON을 사용한 OUTER JOIN]
  SELECT TAB1.COL, TAB2.COL
  FROM TAB1 [ RIGHT | LEFT | FULL ] OUTER JOIN TAB2
  ON TAB1.COL = TAB2.COL;

  
  [FULL OUTER JOIN]
  SELECT * 
  FROM M,S 
  WHERE M1(+)=S1 
  UNION 
  SELECT * 
  FROM M,S 
  WHERE M1=S1(+);

WHERE 절에서 (+)가 왼쪽에 있다면 RIGHT OUTER JOIN이다.
OUTER JOIN의 WHERE 절에서 TABEL1.COL = TABLE2.COL(+); 일 경우에는 LEFT OUTER JOIN이다.
(+)는 WHERE과 ON으로 대체될 수 있다. ON으로 대체될 경우에는 (+) 기호를 쓰지 않는다.
OUTER JOIN에서 OUTER은 생략할 수 있다. ex) RIGHT OUTER JOIN = RIGHT JOIN

1-7. SELF JOIN

하나의 테이블 내에서 다른 칼럼과 참조 관계가 있을 때 사용하는 조인이다.

  
  SELECT 사원.EMPNO, 사원.ENAME, 관리자.EMPNO, 관리자.ENAME 
  FROM EMP 사원, EMP 관리자
  WHERE 사원.MGR=관리자.EMPNO(+);

1-8. NON-EQUI JOIN

특정 범위 내에 있는지 조사하는 JOIN 방법이다.

  
  SELECT E.ENAME, E.SAL, S.GRADE
  FROM EMP E, SAL S
  WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

-> 각 사원의 이름(ENAME)과 월급(SAL), 그리고 그 사원의 급여 등급(GRADE)을 출력하는 명령어다.

월급(SAL)이 범위내에 있는지 조건을 주어 등급(GRADE)을 정하여 출력한다.

2. 집합 연산자

2-1. 집합 연산자 란?

JOIN 없이 연관된 데이터를 조회하는 연산자를 말한다.

2-2. 집합 연산자의 종류

1. UNION : 합집합

중복된 행 하나로 표시
칼럼 수와 데이터 타입이 모두 동일한 경우에만 연산 가능

  
  SELECT 칼럼명 
  FROM 테이블명
  WHERE 조건 
  UNION 
  SELECT 테이블명 
  WHERE 조건;

2. UNION ALL : 합집합

중복된 행 그대로 표시
질의 결과들이 상호 배타적일 때 사용
중복된 값이 없으면 UNION과 결과가 동일하다.

  
  SELECT 칼럼명 
  FROM 테이블명 
  WHERE 조건
  UNION ALL 
  SELECT 테이블명 
  WHERE 조건;

3. INTERSECT : 교집합

중복된 행 하나로 표시

  
  SELECT 칼럼명 
  FROM 테이블명 
  WHERE 조건
  INTERSECT 
  SELECT 테이블명 
  WHERE 조건;

4. MINUS, EXCEPT : 차집합

중복된 행 하나로 표시

  
  SELECT 칼럼명 
  FROM 테이블명
  WHERE 조건절
  MINUS 
  SELECT 테이블명 
  WHERE 조건;

여러 개의 SELECT 문을 집합 연산자로 연결하는 것을 볼 수 있다.
ORDER BY를 사용할 경우 최종 결과에 대한 정렬 처리이므로 가장 마지막 줄에 한 번만 사용한다.

3. 계층형 질의

3-1. 계층형 질의란?

계층형 데이터를 조회하기 위해 사용하는 문장이다.

동일 테이블에 계층적으로 상/하위 데이터가 포함된 데이터를 계층형 데이터라 한다.

ex) 사원 테이블의 상위 사원(관리자) 관계

3-2. Oracle 계층형 질의 구문

  
  SELECT 칼럼명
  FROM 테이블명
  WHERE 조건
  START WITH 조건
  CONNECT BY [NOCYCLE] 조건
  ORDER SIBLINGS BY 칼럼

START WITH : 계층 구조 전개의 시작 위치를 지정
CONNECT BY : 다음에 전개될 자식 데이터를 지정, 트리 형태의 구조로 쿼리를 수행한다.
PRIOR : 현재 읽은 칼럼 지정
- PRIOR 자식 = 부모 : [ 부모 → 자식 ] 순방향 전개
- PRIOR 부모 = 자식 : [ 자식 → 부모 ] 역방향 전개
NOCYCLE : 데이터 전개 시 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 CYCLE이 형성되었다고 한다. NOCYCLE을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
ORDER SIBLINGS BY : 형제 노드 (동일 LEVEL) 사이에서 정렬을 수행한다.
WHERE : 모든 전개를 수행한 후 지정된 조건을 만족하는 데이터만 추출

3-3. Oracle 계층형 질의 가상 칼럼

LEVEL
- 루트 데이터를 1로 시작하여 하위로 내려갈수록 Leaf까지 1씩 증가한다.
CONNECT_BY_ISLEAF
- 전개 과정에서 리프 데이터면 1, 그렇지 않으면 0
CONNECT_BY_ISCYCLE
- 자식을 가질 때 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0
- CYCLE 옵션 사용 시에만 사용 가능하다.

3-4. Oracle 계층형 질의 함수

SYS_CONEECT_BY_PATH(칼럼, 경로 분리자)
- 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다.
CONNECT_BY_ROOT 칼럼
- 현재 전개할 데이터의 루트 데이터를 표시하며 단항 연산자이다.

3-5. SQL Server 계층형 질의

CTE(Common Table Expression)를 재귀 호출함으로써 상위부터 하위 방향 전개한다.

  
  WITH 테이블명_ANCHOR AS
   ( SELECT 하위칼럼명, 칼럼명, 상위칼럼명, 0 AS LEVEL
     FROM 테이블명
     WHERE 상위칼럼명 IS NULL /* 재귀 호출의 시작점 */
     UNION ALL
     SELECT R.칼럼명, R.칼럼명, R.계층칼럼명, A.LEVEL + 1
     FROM 테이블명_ANCHOR A, 테이블명 R
     WHERE A.하위칼럼 = R.상위칼럼 )

UNION 연산자로 쿼리 두 개를 결합한다.
위의 쿼리를 앵커 멤버, 아래의 쿼리를 재귀 멤버라고 한다.

4. 서브쿼리

4-1. 서브쿼리 란?

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

4-2. 서브쿼리 주의사항

서브쿼리를 괄호로 감싸서 사용한다.
단일 행 또는 복수 행 비교 연산자와 함께 사용이 가능하다.
ORDER BY를 사용하지 못한다.
- ORDER BY 절은 SELECT 절에서 오직 한 개만 올 수 있기 때문에 메인 쿼리의 마지막 문장에만 위치한다.
SELECT, FROM, WHERE, HAVING, OREDER BY 절에서 사용 가능하다.
INSERT의 VALUES, UPDATE의 SET 절에서도 사용 가능하다.

4-3. 동작 방식에 따른 서브쿼리 분류

Un-Correlated(비연관) 서브쿼리
- 서브쿼리가 메인쿼리 칼럼을 갖고 있지 않은 형태
- 메인쿼리에 값을 제공하기 위한 목적으로 주로 사용한다.
Correlated(연관) 서브쿼리
- 서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태
- 메인쿼리가 먼저 수행되어 읽힌 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용한다.

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

Single Row(단일 행) 서브쿼리
- 서브쿼리 결과가 항상 1건 이하인 서브쿼리
- 단일 행 비교 연산자와 함께 사용
Mulit Row(다중 행) 서브쿼리
- 실행 결과가 여러 건인 서브쿼리
- 다중 행 비교 연산자(IN, ALL, ANY, SOME, EXISTS 등)와 함께 사용
Multi Column(다중 칼럼) 서브쿼리
- 실행 결과로 여러 칼럼을 반환하는 서브쿼리
- 조건절에 여러 칼럼을 동시에 비교 가능
- 주로 서브쿼리를 메인쿼리의 조건과 비교하기 위해 사용

4-5. 스칼라 서브쿼리 (Scalar Subquery)

값 하나(한 행 또는 한 칼럼)를 반환하는 서브쿼리로 SELECT 절에서 사용한다.

4-6. 뷰 (VIEW) 란?

가상의 테이블이라고도 하며 FROM절에 사용하는 뷰는 인라인 뷰(Inline View)라고 한다.

뷰는 실제 데이터를 가지고 있지 않지만 테이블이 수행하는 역할을 수행한다.

4-7. VIEW의 장점

독립성
편리성
보안성