[SQLD] SQL 활용

ewillwin·2023년 8월 17일

SQLD Study

목록 보기

5/8

[1. 표준 조인 (STANDARD JOIN)]

STANDARD SQL 개요

일반 집합 연산자

UNION 연산은 UNION 기능으로,
INTERSECTION 연산은 INTERSECT 기능으로,
DIFFERENCE 연산은 EXCEPT(Oracle은 MINUS) 기능으로,
PRODUCT 연산은 CROSS JOIN 기능으로 구현되었다.

SELECT 연산은 WHERE 절로 구현되었다.
PROJECT 연산은 SELECT 절로 구현되었다.
(NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현되었다.
DIVIDE 연산은 현재 사용되지 않는다.

FROM 절 JOIN 형태

SQL에서 표시하는 FROM 절의 JOIN 형태는 다음과 같다.
- INNER JOIN
- NATURAL JOIN
- USING 조건절
- ON 조건절
- CROSS JOIN
- OUTER JOIN

INNER JOIN

INNER JOIN은 OUTER JOIN과 대비하여 내부 JOIN이라고 하며 JOIN 조건에서 동일한 값이 있는 행만 반환한다.
INNER JOIN 표시는 그 동안 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다.

# WHERE절 JOIN 조건
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

# FROM 절 JOIN 조건
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;

# INNER는 JOIN의 디폴트 옵션으로 아래 SQL문과 같이 생략 가능하다.
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;

NATURAL JOIN

NATURAL JOIN은 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행한다.
NATURAL JOIN이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없다.

SELECT DEPTNO, EMPNO, ENAME, DNAME
FROM EMP NATURAL JOIN DEPT;

USING 조건절

NATURAL JOIN에서는 모든 일치되는 칼럼들에 대해 JOIN이 이루어지지만, FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수가 있다.

SELECT *
FROM DEPT JOIN DEPT_TEMP
USING (DEPTNO);

ON 조건절

JOIN 서술부(ON 조건절)와 비 JOIN 서술부(WHERE 조건절)를 분리하여 이해가 쉬우며, 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점이 있다.

SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO);

ON 조건절은 WEHRE 절의 JOIN 조건과 같은 기능을 하면서도, 명시적으로 JOIN의 조건을 구분할 수 있으므로 가장 많이 사용될 것으로 예상된다.

CROSS JOIN

CROSS JOIN은 일반 집합 연산자의 PRODUCT의 개념으로 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다.
두 개의 테이블에 대한 CARTESIAN PRODUCT 또는 CROSS PRODUCT와 같은 표현으로, 결과는 양쪽 집합의 M*N 건의 데이터 조합이 발생한다.

SELECT ENAME, DNAME
FROM EMP CROSS JOIN DEPT
ORDER BY ENAME;

OUTER JOIN

INNER JOIN과 대비하여 OUTER JOIN이라고 불리며, JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용할 수 있다.

LEFT OUTER JOIN

Table A와 B가 있을 때(Table 'A'가 기준이 됨), A와 B를 비교해서 B의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, B의 JOIN 칼럼에서 같은 값이 없는 경우에는 B 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다.

RIGHT OUTER JOIN

Table A와 B가 있을 때(Table 'B'가 기준이 됨), A와 B를 비교해서 A의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, A의 JOIN 칼럼에서 같은 값이 없는 경우에는 A 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다.

FULL OUTER JOIN

조인 수행 시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다.

INNER vs OUTER vs CROSS JOIN 비교

INNER JOIN 결과
- B-B, C-C
LEFT OUTER JOIN 결과
- B-B, C-C, D-NULL, E-NULL
RIGHT OUTER JOIN 결과
- NULL-A, B-B, C-C
FULL OUTER JOIN 결과
- NULL-A, B-B, C-C, D-NULL, E-NULL
CROSS JOIN 결과
- B-A, B-B, B-C, C-A, C-B, C-C, D-A, D-B, D-C, E-A, E-B, E-C

[2. 집합 연산자 (SET OPERATION)]

집합 연산자는 여러 개의 질의의 결과를 연결하여 하나로 결합하는 방식을 사용한다. 즉, 집합 연산자는 2개 이상의 질의 결과를 하나의 결과로 만들어 준다.
- SELECT 절의 칼럼 수가 동일하고 SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환 가능(반드시 동일한 데이터 타입일 필요는 없음)해야 한다.

집합 연산자는 사용상의 제약조건을 만족한다면 어떤 형태의 SELECT문이라도 이용할 수 있다.
- 집합 연산자는 여러 개의 SELECT문을 연결하는 것에 지나지 않는다.
- ORDER BY는 집합 연산을 적용한 최종 결과에 대한 정렬 처리이므로 가장 마지막 줄에 한 번만 기술한다.

[3. 계층형 질의와 셀프 조인]

계층형 질의

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 게층형 질의(Hierarchical Query)를 사용한다.
계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.

Oracle 계층형 질의

START WITH: 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트 데이터를 지정한다.(엑세스)
CONNECT BY: 다음에 전개될 자식 데이터를 지정하는 구문이다. 자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야 한다.(조인)
PRIOR: CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정한다.
- "PRIOR 자식 = 부모" 형태를 사용하면 계층구조에서 자식 데이터에서 부모 데이터(자식 -> 부모) 방향으로 전개하는 순방향 전개를 한다.
- "PRIOR 부모 = 자식" 형태를 사용하면 반대로 부모 데이터에서 자식 데이터(부모 -> 자식) 방향으로 전개하는 역방향 전개를 한다.
NOCYCLE: 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클이 형성되었다라고 말한다. 사이클이 발생한 데이터는 런타임 오류가 발생한다. 그렇지만 NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
ORDER SIBLINGS BY: 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.
WHERE: 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)

Oracle은 계층형 질의를 사용할 대 다음과 같은 가상 칼럼(Pseudo Column)을 제공한다.
Oracle은 계층형 질의를 사용할 때 사용자 편의성을 제공하기 위해서 아래와 같은 함수를 제공한다

셀프 조인

셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 말한다. 따라서 FROM 절에 동일 테이블이 두 번 이상 나타난다.
동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias)를 사용해야 한다.

SELECT ALIAS명1.칼럼명, ALIAS명2.칼럼명, ...
FROM 테이블1 ALIAS명1, 테이블2 ALIAS명2
WHERE ALIAS명1.칼럼명2 = ALIAS명2.칼럼명1;

"자신과 상위, 차상위 관리자를 같은 줄에 표시하라"
- 셀프 조인으로 해결 가능 -> FROM 절에 사원 테이블을 두 번 사용

SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자
FROM 사원 E1, 사원 E2
WHERE E1.관리자 = E2.사원
ORDER BY E1.사원;

# 실행 결과
사원 관리자 차상위_관리자
---- ------ ----------
B    A
C    A
D    C      A
E    C      A

자신과 자신의 직속 관리자는 동일한 행에서 데이터를 구할 수 있으나, 차상위 관리자는 바로 구할 수 없다. 차상위 관리자를 구하기 위해서는 자신의 직속 관리자를 기준으로 사원 테이블과 한 번 더 조인(셀프 조인)을 수행해야 한다.
위의 실행 결과를 보면 A에 대한 정보는 누락되었다. 내부 조인(Inner Join)을 사용할 경우 자신의 관리자가 존재하지 않는 경우에는 관리자(E2) 테이블에서 조인할 대상이 존재하지 않기 대문에 해당 데이터는 결과에서 누락된다. 이를 방지하기 위해서는 아우터 조인을 사용해야 한다.

SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자
FROM 사원 E1 LEFT OUTER JOIN 사원 E2
ON (E1.관리자 = E2.사원)
ORDER BY E1.사원;

# 실행 결과
사원 관리자 차상위_관리자
---- ----- ----------
A
B    A
C    A
D    C     A
E    C     A

[4. 서브 쿼리]

서브쿼리(Subquery)란 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 말한다.
조인은 조인에 참여하는 모든 테이블이 대등한 관계에 있기 때문에 조인에 참여하는 모든 테이블의 칼럼을 어느 위치에서라도 자유롭게 사용할 수 있다. 그러나 서브쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다.
서브쿼리를 사용할 때 다음 사항에 주의해야 한다.
- 서브쿼리를 괄호로 감싸서 사용한다.
- 서브쿼리는 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 하고 복수행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.
- 서브쿼리에서는 ORDER BY를 사용하지 못한다. ORDER BY절은 SELECT절에서 오직 한 개만 올 수 있기 때문에 ORDER BY절은 메인쿼리의 마지막 문장에 위치해야 한다.
- SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT-VALUES, UPDATE-SET절에 사용이 가능하다
동작하는 방식에 따른 서브쿼리 분류
반환되는 데이터의 형태에 따른 서브쿼리 분류

단일 행 서브 쿼리

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

예제 1

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버
FROM PLAYER
WHERE TEAM_ID = (SELECT TEAM_ID
				FROM PLAYER
                WHERE PLAYER_NAME = '정남일')
ORDER BY PLAYER_NAME;

예제 2

SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버
FROM PLAYER
WHERE HEIGHT <= (SELECT AVG(HEIGHT)
				FROM PLAYER)
ORDER BY PLAYER_NAME;

다중 행 서브 쿼리

서브쿼리의 결과가 2건 이상 반환될 수 있다면 반드시 다중 행 비교 연산자(IN, ALL, ANY, SOME)와 함께 사용해야 한다.

예제 1

서브쿼리의 결과로 2개 이상의 행이 반환되어 단일 행 비교연산자인 '='로는 처리가 불가능하여 에러가 반환되는 예시
다중 행 비교 연산자로 바꾸어서 작성한 SQL문

다중 칼럼 서브 쿼리

다중 칼럼 서브쿼리는 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미한다.

예제 1

소속팀별 키가 가장 작은 사람들의 정보를 출력

연관 서브 쿼리

연관 서브쿼리(Correlated Subquery)는 서브쿼리 내에 메인쿼리 칼럼이 사용된 서브쿼리이다.

선수 자신이 속한 팀의 평균 키보다 작은 선수들의 정보를 출력
EIXSTS 서브쿼리는 항상 연관 서브쿼리로 사용된다. 또한 EXISTS 서브쿼리의 특징은 아무리 조건을 만족하는 건이 여러 건이더라도 조건을 만족하는 1건만 찾으면 추가적인 검색을 진행하지 않는다.
아래는 EXISTS 서브쿼리를 사용하여 '20120501'부터 '20120502'사이에 경기가 있는 경기장을 조회하는 SQL문이다.

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

SELECT 절에 서브쿼리 사용하기

SELECT 절에서 사용하는 서브쿼리 == 스칼라 서브쿼리(Scalar Subquery)
- 스칼라 서브쿼리는 한 행, 한 칼럼만을 반환하는 서브쿼리를 말한다.
선수 정보와 해당 선수가 속한 팀의 평균 키를 함께 출력하는 예제

FROM 절에서 서브쿼리 사용하기

FROM 절에서 사용되는 서브쿼리를 인라인 뷰(Inline View)라고 한다.
- 인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다. 그래서 일반적인 뷰를 정적 뷰(Static View)라고 하고 인라인 뷰를 동적 뷰(Dynamic View)라고도 한다.
K-리그 선수들 중에서 포지션이 미드필더인 선수들의 소속팀명 및 선수 정보를 출력
선수들 중에서 포지션이 미드필더인 선수들을 인라인 뷰를 통해서 추출하고 인라인 뷰의 결과와 TEAM 테이블과 조인해서 팀명(TEAM_NAME)을 출력하고 있다.
인라인 뷰에서는 ORDER BY절을 사용할 수 있다.
- 인라인 뷰에 먼저 정렬을 수행하고 정렬된 결과 중에서 일부 데이터를 추출하는 것을 TOP-N 쿼리라고 한다.

HAVING 절에서 서브쿼리 사용하기

HAVING 절은 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기위해서 사용한다.
평균키가 삼성 블루윙즈팀의 평균키보다 작은 팀의 이름과 해당 팀의 평균키를 구하는 SQL문을 작성하면 아래와 같다.

뷰 (VIEW)

테이블은 실제로 데이터를 가지고 있는 반면, 뷰(View)는 실제 데이터를 가지고 있지 않다. 뷰는 단지 뷰 정의(View Definition)만을 가지고 있다.
뷰는 실제 데이터를 가지고 있지 않지만 테이블이 수행하는 역할을 수행하기 대문에 가상 테이블(Virtual Table)이라고도 한다.
뷰 사용의 장점은 아래와 같다.
뷰는 아래와 같이 CREATE VIEW문을 통해서 생성할 수 있다.
뷰를 사용하는 방법은 아래와 같다.
뷰를 사용하는 경우에는 DBMS가 내부적으로 SQL문을 아래와 같이 재작성한다.

이는 인라인 뷰와 유사한 모습임을 알 수 있다.
뷰를 제거하기 위해서는 DROP VIEW문을 사용한다.