[SQL] SQL 활용(1)

표준 조인

개요

• ANSI/ISO SQL2(1992년) 표준 SQL명세 부족, 상호 호환성과 학습효율 많이 부족했다.
• ANSI/ISO SQL3(1999년): 기능정리, 호환가능한 여러기능 추가하였다.
• ANSI/ISO SQL(2003년): 기준 소폭추가, 현재 대부분 DB가 사용 현재의 SQL에는 STANDARD JOIN기능이 추가되어있다.

 

일반 집합 연산자

1. UNION: 합집합, 중복 제거해준다
2. INTERSECT: 교집합이다
3. EXCEPT/MINUS(ORCALE): 차집합(DIFFERENCE)이다
4. CROSS JOIN: 곱집합(PRODUCT), JOIN조건이없는 경우 생길 수 있는 모든 조합이다

 

순수 관계 연산자

1. SELECT: WHERE절로 구현되어 있다
2. PROJECT: SELECT절로 구현되어 있다
3. NATURAL JOIN: EQUI JOIN을 수행한다
4. DEVIDE: 현재는 사용되지 않는다

 

JOIN

1. INNER JOIN: 내부 조인, 동일한 값이 있는 행만 반환한다
   • JOIN 조건을 USING절이나 ON절에서 사용한다
2. NATURAL JOIN
   • 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=)JOIN을 수행한다
   • NATURAL JOIN이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE절에서 JOIN 정의할 수 없다
   • JOIN 칼럼에 대하여 ALIAS(별명), 접두사 불가능하다
   • 기준이 되는 칼럼: 맨앞에 정렬(이름같으면 하나로 처리)
   ※INNER JOIN의 경우: 첫 테이블 컬럼 먼저, 다음으로 두번째 테이블 칼럼들(이름같아도 따로 처리)순이다
   • SQL Server에서 지원안한다
3. USING 조건절
   • 원하는 칼럼들만 선택적으로 EQUIJOIN가능하다
   • SQL Server에서 지원안한다
   • 이름이 다른 칼럼은 ON을 통해 JOIN → where절 권고!(특히나 조회만 하는거면 무조건)
   • 데이터 추가같은 경우는 from절 사용함
4. CROSS JOIN: 생길 수 있는 모든 데이터의 조합이다
   • 양쪽 테이블 M*N건 만큼 조합 발생
     ex. 두개 테이블 데이터가 각각 5, 3개면 조인으로 15개발생
5. OUTER JOIN: 동일한 값이 없는 행도 반환 가능하다(없는값: NULL로 표현)
   • LEFT OUTER JOIN: 좌측 기준이다
   • RIGHT OUTER JOIN: 우측 기준이다
   • FULL OUTER JOIN: 좌/우측 모든 데이터를 읽어 조인 한다(중복 삭제)

 

 

 

 

 

---

집합 연산자

집합 연산자	연산자의 의미
UNION	여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만든다.
UNION ALL	여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시된다. 즉, 단순히 결과만 합쳐놓은 것이다.
INTERSECT	여러 개의 SQL문의 결과에 대한 교집합이다. 중복된 행은 하나의 행으로 만든다.
EXCEPT	앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합이다. 중복된 행은 하나의 행으로 만든다.

요약

• 조인을 사용하지 않고 연관된 데이터 조회한다
• 여러 질의 결과를 하나로 결합하는 방식이다
• UNION: 합집합, 중복 행 하나로 합
• UNION ALL: 합집합, 중복 표현(이런 특징덕분에 내부조인때 사용)
• INTERSECT: 교집합 → EXISTS, IN으로 변경 가능
• EXCEPT: 차집합 → NOT EXISTS, NOT IN으로 변경 가능
  • EXCEPT - <>
  • MINUS - NOT EXISTS, NOT IN

 

 

 

---

계층형 질의

계층적으로 상위, 하위 데이터가 포함된 데이터

계층형 구조

 

ORACLE

• ORACLE
  • START WITH 절: 시작 위치 지정한다.
  • CONNECT BY 절: 자식 데이터를 지정하는 구문이다.
  • PRIOR: CONNECT BY 절에서 PRIOR 자식 = 부모 형태경우 순방향(부모→자식): PRIOR 부모 = 자식 형태경우 역방향(자식->부모)으로  순서가 지정된다.

<--앞부분 생략-->
START WITH MANAGERID == NULL
CONNECT BY PRIOR EMPLOYEE = MANAGERID

위와 같이 설정하면 자식 = 부모로 (부모->자식) 순방향 형태가 된다. 그리고 시작을 MANAGERID=NULL로 하면 상사가 비어있는칸부터 시작하니 루트(최종부모)부터 시작이라고 생각하면 될것 같다

 

• 가상 컬럼
  • LEVEL: 루트 데이터는 1, 하위 데이터는 2 (1씩 증가)이다
  • CONNET_BY_ISLEAF: 리프 데이터면 1, 아니면 0 이다
  • CONNECT_BY_ISCYCLE: 조상으로 존재하면 1, 아니면 0 이다
• 함수
  • SYS_CONNECT_BY_PATH: 경로 표시 한다
  • CONNECT_BY_ROOT: 루트 데이터 표시 한다

※ORDER SIBLINGS BY: 형제 노드(동일 LEVEL)사이에서 정렬을 수행한다.

 

SQL

• 2000버전까지는 계층 문법지원x → 2005버전부터는 하나의 질의로 원하는 결과 얻는다
• CTE(Common Table Expression) 재귀호출, 실제와 다르게 표현 → order by로 정렬해야한다(구문: ORDER BY SORT)
• UNION ALL 연산자를 사용한다

 

 

---

셀프 조인

• 동일 테이블 사이의 조인 한다
• 반드시 ALIAS(별칭) 사용한다
• INNER JOIN 사용시 최상위 데이터 표현X → OUTER JOIN 사용한다 
  OUTER JOIN ex. A B A C A D C A E C A   
  INNER JOIN ex. B A C A D C A E C A

 

 

 

---

서브쿼리

SQL문 안에 또 다른 SQL문

 

분류

• 동작 방식 분류
  1. 비연관 서브쿼리: 메인쿼리 컬럼을 가지고 있지 않은 형태이다 
  2. 연관 서브쿼리: 메인쿼리 컬럼을 가지고 있는 형태이다
• 반환 형태 분류
  1. 단일행 서브쿼리: 결과가 항상 1건 이하, 2건 이상이면 RunTime오류 발생 ex. =, <, ≤, >, ≥, <> 단일 비교연산자 사용한다
  2. 다중행 서브쿼리: 결과가 여러 건 ex. IN, ALL, ANY, SOME, EXISTS 다중항 비교연산자 사용한다
  3. 다중컬럼 서브쿼리(SQL Server 지원안됨): 여러 컬럼 반환, 비교하고자 하는 칼럼 개수와 위치가 동일해야한다
  보너스. 연관 서브큐리: 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리이다
• 그밖에 위치에서 사용하는 서브쿼리
  • SELECT 절: 스칼라 서브쿼리다(2개이상의 SELECT절 생각하면됨)
  • FROM 절: 생성된 테이블처럼 보이지만 하나의 뷰일뿐이다.(정적뷰)
    • TOP-N쿼리: 인라인 뷰 정렬후 원하는 데이터 개수찾는다
    • ORACLE에서는 ROWNUM이라는 연산자 통해서 데이터 건수 제약가능하다
  • HAVING 절: 그룹핑된 결과에 부가적인 조건 추가한다
  • UPDATE문의 SET절에서 사용, 주의점은 서브쿼리의 결과가 NULL이면 해당 칼럼의 결과가 NULL이다.
  • INSERT문의 VALUES절: 값 알맞게 변경하고 데이터 넣는다

 

 

 

---

뷰

가상 테이블(실제 데이터가아님)

• 장점
  1. 독립성: 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다
  2. 편리성: 복잡한 질의 → 뷰를 통해 단순 작성가능하다
  3. 보안성: 해당 컬럼을 제외하고 생성해 사용자에게 정보를 감출 수 있다
• 생성, 삭제
  • CREATE VIEW, DROP VIEW

•