본 문서의 내용은 한국데이터산업진흥원에서 펴낸 SQL 전문가 가이드를 기반으로 자격증 취득에 도움이 될 개념을 정리한 것입니다.
 |
|
1. 표준 조인
1. STANDARD SQL 개요
현재 사용되는 데이터베이스는 대부분 SQL-2003 표준을 기준으로 하고 있다. 다른 벤더의 DBMS 도 2006년 이후 발표된 버전에서 ANSI/ISO SQL-99 와 SQL-2003 의 핵심적인 기능은 만족스러운 수준으로 구현된 것으로 평가 받고 있다.
가. 일반 집합 연산자
현재 사용하는 SQL 의 많은 기능이 관계형 데이터베이스의 이론을 수립한 E.F.Codd 박사의 논문에 언급이 되어 있다. 논문에 언급된 8 가지 관계형 대수는 다시 각각 4 개의 일반 집합 연산자와 순수 관계 연산자로 나눌 수 있으며, 관계형 데이터베이스 엔진 및 SQL 의 기반 이론이 되었다.
1. UNION
- 수학적 합집합을 제공하기 위해, 공통 교집합의 중복을 없애기 위한 사전 작업으로 시스템에 부하를 주는 정렬 작업이 발생한다. 이후 UNION ALL 기능이 추가되었는데, 특별한 요구 사항이 없다면 공통집합을 중복해서 그대로 보여 주기 때문에 정렬 작업이 일어나지 않는 장점을 가진다.
- 응답 속도 향상이나 자원 효율화 측면에서 데이터 정렬 작업이 발생하지 않는 UNION ALL 을 사용하는 것을 권고한다.
2. INTERSECTION
- 수학의 교집합으로써 두 집합의 공통집합을 추출한다.
3. DIFFERENCE
- 수학의 차집합으로써 첫 번째 집합에서 두 번째 집합과의 공통집합을 제외한 부분이다.
- 대다수 벤더는 EXCEPT를, Oracle 은 MINUS 용어를 사용한다. (SQL 표준에는 EXCEPT 로 표시되어 있다.
4. PRODUCT
- CROSS(ANIS/ISO 표준) PRODUCT 라고 불리는 곱집합으로, JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다. 양쪽 집합의 M*N 건의 데이터 조합이 발생하며, CARTESIAN(수학자 이름) PRODUCT 라고도 표현한다.
나. 순수 관계 연산자
순수 관계 연산자는 관계형 데이터베이스를 구현하기 위해 새롭게 만들어진 연산자이다. 순수 관계 연산자를 현재의 SQL 문장과 비교하면 다음과 같다.
5. SELECT
- SQL 문장에서는 WHERE 절의 조건절 기능으로 구현이 되었다. (SELECT 연산과 SELECT 절의 의미가 다름을 유의하자.)
6. PROJECT
- SQL 문장에서는 SELECT 절의 칼럼 선택 기능으로 구현이 되었다.
7. JOIN
- WHERE 절의 INNER JOIN 조건과 함께 FROM 절의 NATURAL JOIN, INNER JOIN, OUTER JOIN, USING 조건절, ON 조건절 등으로 가장 다양하게 발전하였다.
8. DIVIDE
- 나눗셈과 비슷한 개념으로 왼쪽의 집합을 ‘XZ’로 나누었을 때, 즉 ‘XZ’를 모두 가지고 있는 ‘A’가 답이 되는 기능으로 현재 사용되지 않는다.
2. FROM 절 JOIN 형태
ANSI/ISO SQL 에서 규정한 JOIN 문법은 WHERE 절을 사용하던 기존 JOIN 방식과 차이가 있다. 사용자는 기존 WHERE 절의 검색 조건과 테이블 간의 JOIN 조건을 구분 없이 사용하던 방식을 그대로 사용할 수 있으면서, 추가된 선택 기능으로 테이블 간의 JOIN 조건을 FROM 절에서 명시적으로 정의할 수 있게 되었다.
INNER JOIN 은 WHERE 절에서부터 사용하던 JOIN 의 DEFAULT 옵션으로 JOIN 조건에서 동일한 값이 있는 행만 반환한다. DEFAULT 옵션이므로 생략이 가능하지만, CROSS JOIN, OUTER JOIN 과는 같이 사용할 수 없다.
NATURAL JOIN 은 INNER JOIN 의 하위 개념으로 NATURAL JOIN 은 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN 을 수행한다.
ON 조건절의 경우 NATURAL JOIN 처럼 JOIN 조건이 숨어 있지 않고, 명시적으로 JOIN 조건을 구분할 수 있고, NATURAL JOIN 이나 USING 조건절처럼 칼럼명이 똑같아야 된다는 제약 없이 칼럼명이 상호 다르더라도 JOIN 조건으로 사용할 수 있으므로 앞으로 가장 많이 사용될 것으로 예상된다.
3. INNER JOIN
내부 JOIN 이라고 하며 JOIN 조건에서 동일한 값이 있는 행만 반환한다. INNER JOIN 표시는 그 동안 WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다.
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP
(INNER) JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
4. NATURAL JOIN
NATURAL JOIN 이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없다. 그리고, SQL Server 에서는 지원하지 않는 기능이다.
SELECT DEPTNO, EMPNO, ENAME, DNAME
FROM EMP
NATURAL JOIN DEPT;
위 SQL 은 별도의 JOIN 칼럼을 지정하지 않았지만, 두 개의 테이블에서 DEPTNO 라는 공통된 칼럼을 자동으로 인식하여 JOIN 을 처리한 것이다. JOIN 에 사용된 칼럼들은 같은 데이터 유형이어야 하며, ALIAS 나 테이블 명과 같은 접두사를 붙일 수 없다.
NATURAL JOIN 은 JOIN 이 되는 테이블의 데이터 성격(도메인)과 칼럼명 등이 동일해야 하는 제약 조건이 있다. 간혹 모델링 상의 부주의로 인해 동일한 칼럼명이더라도 다른 용도의 데이터를 저장하는 경우도 있으므로 주의해서 사용해야 한다.
'*' 와일드카드처럼 별도의 칼럼 순서를 지정하지 않으면 NATURAL JOIN 의 기준이 되는 칼럼 들이 다른 칼럼보다 먼저 출력된다. 반면, INNER JOIN 의 경우 첫 번째 테이블, 두 번째 테이블의 칼럼 순서대로 데이터가 출력된다. 이때 NATURAL JOIN 은 JOIN 에 사용된 같은 이름의 칼럼을 하나로 처리하지만, INNER JOIN은 별개의 칼럼으로 표시한다.
5. USING 조건절
NATURAL JOIN 에서는 모든 일치되는 칼럼들에 대해 JOIN 이 이루어지지만, FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN 을 할 수가 있다. 다만, 이 기능은 SQL Server 에서는 지원하지 않는다.
별도의 칼럼 순서를 지정하지 않으면 USING 조건절의 기준이 되는 칼럼이 다른 칼럼보다 먼저 출력된다. 이때 USING JOIN 은 JOIN 에 사용된 같은 이름의 칼럼을 하나로 처리한다.
USING 조건절을 이용한 EQUI JOIN 에서도 NATURAL JOIN 과 마찬가지로 JOIN 칼럼에 대해서는 ALIAS 나 테이블 이름과 같은 접두사를 붙일 수 없다. 다만, JOIN 컬럼 이외의 컬럼은 ALIAS 사용이 가능하다.
SELECT DEPTNO, -- JOIN 컬럼에 대해 Alias 사용 불가
DEPT.DNAME,
DEPT.LOC,
DEPT_TEMP.DNAME,
DEPT_TEMP.LOC
FROM DEPT
JOIN DEPT_TEMP USING (DEPTNO);
SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO, DNAME);
위 SQL 의 경우 DNAME 의 내용이 바뀐 부서번호 20, 30 의 경우는 결과에서 제외된 것을 알 수 있다. 그리고 USING 에 사용된 DEPTNO, DNAME 이 첫 번째, 두 번째 칼럼으로 출력된 것과 함께, JOIN 조건에 참여하지 않은 LOC 가 2 개의 칼럼으로 표시된 것을 알 수 있다.
6. ON 조건절
JOIN 서술부(ON 조건절)와 비 JOIN 서술부(WHERE 조건절)를 분리하여 이해가 쉬우며, 컬럼명이 다르더라도 JOIN 조건을 사용할 수 있는 장점이 있다.
NATURAL JOIN 의 JOIN 조건은 기본적으로 같은 이름을 가진 모든 칼럼들에 대한 동등 조건이지만, 임의의 JOIN 조건을 지정하거나, 이름이 다른 칼럼명을 JOIN 조건으로 사용하거나, JOIN 칼럼을 명시하기 위해서는 ON 조건절을 사용한다. ON 조건절에 사용된 괄호는 옵션 사항이다.
USING 조건절을 이용한 JOIN 에서는 JOIN 칼럼에 대해서 ALIAS 나 테이블 명과 같은 접두사를 사용하면 SYNTAX 에러가 발생하지만, 반대로 ON 조건절을 사용한 JOIN 의 경우는 ALIAS 나 테이블 명과 같은 접두사를 사용하여 SELECT 에 사용되는 칼럼을 논리적으로 명확하게 지정해주어야 한다.
가. WHERE 절과의 혼용
ON 조건절과 WHERE 검색 조건은 충돌 없이 사용할 수 있다.
SELECT E.ENAME, E.DEPTNO, D.DEPTNO, D.DNAME
FROM EMP E
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE E.DEPTNO = 30;
나. ON 조건절 + 데이터 검증 조건 추가
ON 조건절에 JOIN 조건 외에도 데이터 검색 조건을 추가할 수는 있으나, 검색 조건 목적인 경우는 WHERE 절을 사용할 것을 권고한다. (다만, 아우터 조인에서 조인의 대상을 제한하기 위한 목적으로 사용되는 추가 조건의 경우는 ON 절에 표기되어야 한다.)
SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME
FROM EMP E
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO AND E.MGR = 7698);
다. ON 조건절 예제
팀과 스타디움 테이블을 팀 ID 로 JOIN 하여 팀이름, 팀 ID, 스타디움 이름을 찾아본다. STADIUM 에는 팀 ID 가 HOMETEAM_ID 라는 칼럼으로 표시되어 있다.
SELECT TEAM_NAME, TEAM_ID, STADIUM_NAME
FROM TEAM
JOIN STADIUM ON TEAM.TEAM_ID = STADIUM.HOMETEAM_ID
ORDER BY TEAM_ID;
위 SQL은 TEAM_ID와 HOMETEAM_ID라는 다른 이름의 칼럼을 사용하기 때문에 USING 조건절을 사용할 수는 없다.
라. 다중 테이블 JOIN
사원과 DEPT 테이블의 소속 부서명, DEPT_TEMP 테이블의 바뀐 부서명 정보를 출력한다.
SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME New_DNAME
FROM EMP E
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
JOIN DEPT_TEMP T ON (E.DEPTNO = T.DEPTNO);
홈팀이 3 점 이상 차이로 승리한 경기의 경기장 이름, 경기 일정, 홈팀 이름과 원정팀 이름 정보를 출력한다.
SELECT ST.STADIUM_NAME,
SC.STADIUM_ID,
SCHE_DATE,
HT.TEAM_NAME,
AT.TEAM_NAME,
HOME_SCORE,
AWAY_SCORE
FROM SCHEDULE SC
JOIN STADIUM ST ON SC.STADIUM_ID = ST.STADIUM_ID
JOIN TEAM HT ON SC.HOMETEAM_ID = HT.TEAM_ID
JOIN TEAM AT ON SC.AWAYTEAM_ID = AT.TEAM_ID
WHERE HOME_SCORE >= AWAY_SCORE + 3;
7. CROSS JOIN
CROSS JOIN 은 E.F.CODD 박사가 언급한 일반 집합 연산자의 PRODUCT 의 개념으로 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다. 두 개의 테이블에 대한 CARTESIAN PRODUCT 또는 CROSS PRODUCT 와 같은 표현으로, 결과는 양쪽 집합의 M*N 건의 데이터 조합이 발생한다.
SELECT ENAME, DNAME
FROM EMP
CROSS JOIN DEPT
ORDER BY ENAME;
NATURAL JOIN 의 경우 WHERE 절에서 JOIN 조건을 추가할 수 없지만, CROSS JOIN 의 경우 WHERE 절에 JOIN 조건을 추가할 수 있다. 그러나, 이 경우는 CROSS JOIN 이 아니라 INNER JOIN 과 같은 결과를 얻기 때문에 CROSS JOIN 을 사용하는 의미가 없어지므로 권고하지 않는다.
SELECT ENAME, DNAME
FROM EMP
CROSS JOIN DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
-- 아래의 INNER JOIN과 동일한 결과 반환
SELECT ENAME, DNAME
FROM EMP
INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
정상적인 데이터 모델이라면 CROSS PRODUCT 가 필요한 경우는 많지 않지만, 간혹 튜닝이나 리포트를 작성하기 위해 고의적으로 사용하는 경우가 있을 수 있다. 그리고 데이터웨어하우스의 개별 DIMENSION(차원)을 FACT(사실) 칼럼과 JOIN 하기 전에 모든 DIMENSION 의 CROSS PRODUCT 를 먼저 구할 때 유용하게 사용할 수 있다.
8. OUTER JOIN
INNER(내부) JOIN 과 대비하여 OUTER(외부) JOIN 이라고 불리며, JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용할 수 있다.
추가로 OUTER JOIN 역시 JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다. 그리고, LEFT/RIGHT OUTER JOIN 의 경우에는 기준이 되는 테이블이 조인 수행시 무조건 드라이빙 테이블이 된다.
가. LEFT OUTER JOIN
Table A 와 B 가 있을 때(Table 'A'가 기준이 됨), A 와 B를 비교해서 B 의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, B 의 JOIN 칼럼에서 같은 값이 없는 경우에는 B 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다. 그리고 LEFT JOIN 으로 OUTER 키워드를 생략해서 사용할 수 있다.
SELECT STADIUM_NAME, STADIUM.STADIUM_ID, SEAT_COUNT, HOMETEAM_ID, TEAM_NAME
FROM STADIUM
LEFT JOIN TEAM ON STADIUM.HOMETEAM_ID = TEAM.TEAM_ID
ORDER BY HOMETEAM_ID;
나. RIGHT OUTER JOIN
LEFT JOIN 과 반대로 우측 테이블이 기준이 되어 결과를 생성한다. 즉, TABLE A 와 B가 있을 때(TABLE 'B'가 기준이 됨), A 와 B 를 비교해서 A 의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, A 의 JOIN 칼럼에서 같은 값이 없는 경우에는 A 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다. 그리고 LEFT JOIN과 동일하게 OUTER 키워드를 생략할 수 있다.
SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E
RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
다. FULL OUTER JOIN
TABLE A 와 B 가 있을 때(TABLE 'A', 'B' 모두 기준이 됨), RIGHT OUTER JOIN 과 LEFT OUTER JOIN 의 결과를 합집합으로 처리한 결과와 동일하다. 단, UNION ALL 이 아닌 UNION 기능과 같으므로 중복되 는 데이터는 삭제한다.
SELECT *
FROM DEPT
FULL JOIN DEPT_TEMP ON DEPT.DEPTNO = DEPT_TEMP.DEPTNO;
-- LEFT OUTER JOIN과 RIGHT OUTER JOIN 합집합도 같은 결과를 반환
SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC
FROM DEPT L
LEFT OUTER JOIN DEPT_TEMP R ON L.DEPTNO = R.DEPTNO
UNION
SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC
FROM DEPT L
RIGHT OUTER JOIN DEPT_TEMP R ON L.DEPTNO = R.DEPTNO;
9. INNER vs OUTER vs CROSS JOIN 비교
1. INNER JOIN: 양쪽 테이블에 모두 존재하는 키 값이 B-B, C-C 인 2 건이 출력된다.
2. LEFT OUTER JOIN: TAB1 을 기준으로 키 값 조합이 B-B, C-C, D-NULL, E-NULL 인 4 건이 출력된다.
3. RIGHT OUTER JOIN: TAB2 를 기준으로 키 값 조합이 NULL-A, B-B, C-C 인 3 건이 출력된다.
4. FULL OUTER JOIN: 양쪽 테이블을 기준으로 키 값 조합이 NULL-A, B-B, C-C, D-NULL, E-NULL 인 5 건이 출력된다.
5. CROSS JOIN: JOIN 가능한 모든 경우의 수를 표시하지만 단, OUTER JOIN 은 제외한다. 양쪽 테이블 TAB1 과 TAB2 의 데이터를 곱한 개수인 4 * 3 = 12 건이 추출된다. 값 조합은 B-A, B-B, B-C, C-A, C-B, C-C, D-A, DB, D-C, E-A, E-B, E-C 인 12 건이 출력된다.
↓SQL 전문가 가이드 요약 목록
1장. 데이터 모델링의 이해
Part 1. 데이터 모델링의 이해
Part 2. 데이터 모델과 성능
2장. SQL 기본 및 활용
Part 1. SQL 기본
Part 2. SQL 활용
Part 3. SQL 최적화 기본원리
따로 PDF 파일이 필요하신 분은 댓글을 통해 메일 주소 적어주시기 바랍니다.
'데이터 사이언스 > SQL' 카테고리의 다른 글
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.3. 계층형 질의와 셀프 조인 (0) | 2020.11.25 |
|---|---|
| [SQLD 학습 자료 요약] SQL 기본 및 활용 2.2. 집합 연산자 (0) | 2020.11.25 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 1.9. 조인 (JOIN) (0) | 2020.11.24 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 1.8. ORDER BY 절 (0) | 2020.11.23 |
| [SQLD 학습 자료 요약] SQL 기본 및 활용 1.7. GROUP BY, HAVING 절 (2) | 2020.11.23 |
