SQL 기본(3)

● 관계형 데이터베이스의 릴레이션 구조 

• 릴레이션(Relation) : 데이터들을 표(Table) 형태로 표현한 것, 릴레이션 스키마(=구조)와 릴레이션 인스턴스(=값)로 구성되어 있다.

• 카디널리티 : 튜플의 개수 

● 순수 관계 연산자

• SELECT(σ) : 릴레이션에서 조건을 만족하는 튜플들을 반환
• PROJECT(π) : 릴레이션 R에서 주어진 속성들의 값만으로만 구성된 튜플들을 반환
• JOIN(▷◁) :  공통 속성을 이용해 릴레이션 R과 다른 릴레이션 S의 튜플들을 연결하여 만들어진 새로운 튜플들을 반환
• DIVISION(÷) : 릴레이션 S의 모든 튜플과 관련있는 릴레이션 R의 튜플들을 반환

● JOIN의 종류

• INNER JOIN(내부 조인) : 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 존재해야 함.
• OUTER JOIN(외부 조인) : 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나옴.
• CROSS JOIN( 상호 조인) : 한쪽 테이블의 모든 행과 다른쪽 테이블의 모든 행을 조인함.
  1. 예를들어 릴레이션 R의 행이 3개 이고  릴레이션 S의 행이 3개이면 총 3x3으로 9개의 행이 나타나게 됨.
• SELF JOIN(자체 조인) : 자신이 자신과 조인한다는 것으로, 1개의 테이블만 사용함.
  • 한 테이블 내에서 두 칼럼이 연관관계가 있을 경우 사용

SELECT <열 목록>
FROM <릴레이션> 별칭A
    INNER JOIN <릴레이션> 별칭B
[WHERE 검색 조건]

 

 

●  집합 연산자의 종류

집합 연산자	연산자 의미
UNION	여러개의 SQL 결과에 대한 합집합으로 결과에모든 중복된 행은 하나의 행으로 만듬.
UNION ALL	여러개의 SQL 결과에 대한 합집합으로 중복된 행도 그대료 표출. (=> 중복 제거가 없다.) 여러 질의 결과가 상호 배타적인일 때 많이 사용.
INTERSECT	여러개의 SQL 결과에 대한 교집합이다. 중복된 행은 하나의 행으로 만듬.
EXPECT	앞의 SQL 결과에서 뒤의 SQL 결과에 대한 차집합이다. 중복된 행은 하나의 행으로 만듬 (일부 데이터베이스는 MINUS를 사용)

 

● UNION ALL 예시 문제

SELECT ENAME AAA, JOB AAB
 FROM EMP
 WHERE EMPNO = 7369
UNION ALL
SELECT ENAME BBA, JON BBB
 FROM EMP
 WHERE EMPNO = 7566
ORDER BY 1, 2

• UNION ALL을 사용하는 경우 맨 처음 ALIAS(=별칭)을 쓴 별칭이 된다. 즉, 컬럼명은 AAA, AAB가 될 것이다.
• ORDER BY는 집합연산(여기선 UNION ALL)을 적용한 최종 결과에 대한 정렬이므로 가장 마지막에 한번 수행한다. 1과 2가 의미하는 건 첫번째 컬럼, 두번째 컬럼을 의미한다.
• 문제 풀이를 하자면, 7369인 ENAME은 SMITH, JOB은 CLERCK이고, 7566인 ENAME은 JONES, JOB은 MANAGER이다. 
• 결과는 아래와 같을 것이다. (알파벳 순서 ASC 정렬)

AAA	AAB
JONES	MANAGER
SMITH	CLERK

 

● ORACLE 계층형 질의

• START WITH : 계층 구조의 시작점을 지정하는 구문(-> 루트 데이터 지정)
• ORDERING SIBLNGS BY : 형제 노드(동일 LEVEL)사이에서의 정렬을 수행
• 순방향전개란 부모 노드로부터 자식 노드 방향으로 전개하는 것을 말함 (<-> 역방향은 그 반대)
  • PRIOR 자식 = 부모 :  순방향 전개
  • PRIOR 부모 = 자식 :  역방향 전개
• 루트 노드의 LEVEL은 1이다.
• NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클(Cycle)이라 부름. 이런 경우 사이클이 발생한 데이터는 런타임 오류가 발생한다. 하지만 NOCYCLE을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않음. (CONNECY BY [NOCYCLE] 같은 방식으로 사용함)
• CONNECT_BY_ISLEAF : 전개 과정에서 해당 데이터가 리프 데이터(=최하위)면 1, 그렇지 않으면 0
• CONNECT_BY_ISCYCLE : 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0. 조상이란 자신으로부터의 루트까지의 경로에 데이터가 존재하는 것을 의미

● 계층형 질의(Hierachical Query)

•  테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용
• 계층형 데이터 : 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터
• SQL Server에서의 계층형 질의문은 CTE(Common Table Expession)를 재귀 호출함으로써 계층 구조를 전개
  • CTE : SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문 하나의 실행 범위 내에서 정의되는 임시 결과 집합을 의미 -> 즉, 개체로 저장되지 않고 쿼리 지속 시간 동안만 존재한다는 점에서 파생 테이블과 비슷
  • But, CTE는 파생 테이블과 달리 자체 참조가 가능하며 동일 쿼리에서 여러 번 참조 가능

-- CTE 예시
WITH CTE_EXPRESSION_NM [(COLUMN_NM [, ...n])] 
AS (
    CTE_QUERY_SPOT
)

• 앵커 멤버(Anchor member)
  • 재귀 CTE의 첫 번째 호출
  • 집합 연산자로 연결된 하나 이상의 '쿼리 정의'(CTE_query_definition)로 구성
  • 다른 CTE를 참조하지 않음
• 재귀 멤버(Reculsive member)
  • CTE를 참조하는 UNION ALL로 연결된 하나 이상의 '쿼리 정의'로 구성
• ORACLE 계층형 질의문에서는 PRIOR 키워드를 SELECT, WHERE, CONNECT BY 절에 사용 가능
• ORACLE 계층형 질의문에서 WHERE 절은 모든 전개를 진행한 이후 필터 조건으로 조건을 만족하는 데이터만을 추출하는데 사용

● 서브 쿼리

종류	내용
Single Row 서브 쿼리 (단일 행 서브 쿼리)	서브 쿼리의 실행 결과가 항상 1건 이하인 서브쿼리를 의미 단일 행 서브쿼리는 단일 행 비교 연산자(=, <, =<, =>, <>)와 사용
Multi Row 서브 쿼리 (다중 행 서브쿼리)	서브쿼리 실행 결과가 여러 건인 서브 쿼리를 의미 다중 행 서브 쿼리는 다중 행 비교 연산자(IN, ALL, SOME, EXISTS, ANY)와 함께 사용
Multi Column 서브 쿼리 (다중 컬럼 서브쿼리)	서브쿼리 실행결과로 여러 컬럼이 반환되는 쿼리를 의미 다중 컬럼 서브쿼리는 메인 쿼리의 조건식에 여러 칼럼들과 비교 가능 단, 서브쿼리와 메인쿼리에서 비교하고자하는 컬럼 개수와 컬럼의 위치가 동일해야 함.

• 단일 행 또는 복수 향 비교 연산자와 함께 사용 가능
• SELECT, FROM, HAVING ORDER BY 절 등에서 사용 가능
• 연관 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 포함하고 있는 형태의 서브쿼리
• 다중 컬럼 서브쿼리는 서브쿼리이 결과로 여러 개의 컬럼이 반환되어 메인 쿼리의 조건과 비교되는데 SQL SERVER에는 현재 지원하지 않음.
• 단일 행 서브쿼리의 비교연산자로는  =, <, <=, >, >=, <>가 되어야 함.
• 단일 행 서브쿼리의 비교연산자는 다중 행 서브쿼리의 비교연산자를 사용할 수 없지만, 다중 행 비교연산자는 단일 행 서브쿼리의 비교연산자로서 사용될 수 있음.
• 비 연관 서브쿼리는 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용됨.
• 메인 쿼리의 결과가 서브쿼리로 제공될 수도 있고, 서브쿼리 결과가 메인쿼리로 제공될 수도 있으므로 실행 순서에 따라 달라짐.

● COUNT() 함수에 관하여

• COUNT(*) :  ROW 기준이므로 NULL 값도 카운트O
• COUNT(NULL) : NULL이므로 카운트X