1. UNION 연산은 UNION 기능으로
- UNION 연산은 공통 교집합의 중복을 없애기 위한 작업으로 시스템에 부하를 주는 정렬 작업이 발생.
- UNION ALL은 특별한 요구 사항이 없다면 공통집합을 중복해서 그대로 보여주기 때문에 정렬작업이 일어나지 않는다.
- UNION과 UNION ALL의 출력 결과가 같다면, 데이터 정렬 작업이 발생하지 않는 UNION ALL사용 권고
2. INTERSECTION 연산은 INTERSECT 기능으로
- 수학의 교집합으로써 두 집합의 공통집합을 추출
3. DIFFERENCE 연산은 EXCEPT(ORACLE은 MINUS) 기능으로
- 수학의 차집합으로써 첫 번째 집합에서 두 번째 집합과의 공통집합을 제외한 부분
4. PRODUCT 연산은 CROSS JOIN 기능으로 구현
- CROSS PRODUCT라고 불리는 곱집합으로, JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합
- M * N 건의 데이터 조합이 발생
- CARTESIAN PRODUCT 라고도 표현
5. SELECT 연산은 WHERE 절로 구현
- WHERE 절의 조건절 기능으로 구현 (SELECT 연산과 SELECT 절의 의미가 다름을 유의!)
6. PROJECT 연산은 SELECT 절로 구현
- PROJECT 연산은 SQL 문장에서는 SELECT 절의 컬럼 선택 기능으로 구현
7. (NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현
- JOIN 연산은 WHERE절의 INNER JOIN 조건과 함께 FROM절의 NATURAL JOIN, INNER JOIN, OUTER JOIN, USING 조건절, ON 조건절 등
8. DIVIDE 연산은 현재 사용되지 않음
** 관계형 데이터베이스의 경우 요구사항 분석, 개념적 데이터 모델링, 논리적 데이터 모델링, 물리적 데이터 모델링 단계를 거치게 되는데, 이 단계에서 엔터티 확정 및 정규화 과정, 그리고 M:M (다대다) 관계를 분해하는 절차를 거침
정규화를 거치면 하나의 주제에 관련 있는 엔터티가 여러 개로 나누어지게 되고, 이 엔터티들이 주로 테이블이 되는데 이렇게 흩어진 데이터를 연결해서 원하는 데이터를 가져오는 작업이 바로 JOIN
* FROM 절 JOIN 형태
- INNER JOIN
: WHERE 절에서부터 사용하던 JOIN의 DEFAULT 옵션으로 JOIN 조건에서 동일한 값이 있는 행만 반환
: DEFAULT 옵션이므로 생략이 가능하지만, CROSS JOIN, OUTER JOIN과는 같이 사용할 수 없음
- NATURAL JOIN
: INNER JOIN의 하위 개념으로 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행
- USING 조건절
- ON 조건절
: 과거 WHERE 절에서 JOIN 조건과 데이터 검증 조건이 같이 사용되어 용도가 불분명한 경우가 발생할 수 있었는데, WHERE절의 JOIN 조건을 FROM 절의 ON 조건절로 분리하여 표시함으로써 이해를 도움
: NATURAL JOIN처럼 JOIN 조건이 숨어 있지 않고, 명시적으로 JOIN 조건을 구분할 수 있고 NATURAL JOIN이나 USING 조건절처럼 칼럼명이 똑같아야 된다는 제약 없이 칼럼명이 상호 다르더라고 JOIN 조건으로 사용 가능
=> SQL server의 경우 ON 조건절만 지원하고 NATURAL JOIN과 USING 조건절을 지원하지 않음
- CROSS JOIN
- OUTER JOIN
▷ INNER JOIN
: JOIN 조건에서 동일한 값이 있는 행만 반환
: WHERE 절에서 사용하던 JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 함
예제] 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾아본다.
SELECT EMPNO, ENAME, EMP.DEPTNO, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO, ENAME, EMP.DEPTNO, DNAME
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT EMPNO, ENAME, EMP.DEPTNO, DNAME
FROM EMP I JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO;
▷ NATURAL JOIN
: 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행
: 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없음
: SQL server에서 지원하지 않는 기능
: JOIN이 되는 테이블의 데이터 성격(도메인)과 칼럼명 등이 동일해야 하는 제약 조건이 있음
예제] 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾아본다.
SELECT EMPNO, ENAME, DEPTNO, DNAME
FROM EMP NATURAL JOIN DEPT;
=> 별도의 JOIN 칼럼을 지정하지 않았지만, 두 개의 테이블에서 DEPTNO라는 공통된 칼럼을 자동으로 인식하여 JOIN을 처리한 것
JOIN에 사용된 칼럼들은 같은 데이터 유형이어야 하며, ALIAS나 테이블 명과 같은 접두사를 붙일 수 없다.
SELECT EMPNO, ENAME, EMP.DEPTNO, DNAME FROM EMP NATURAL JOIN DEPT
*
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
예제] SELECT * FROM EMP NATURAL JOIN DEPT;
=> '*' 와일드카드처럼 별도의 칼럼 순서를 지정하지 않으면 NATURAL JOIN의 기준이 되는 칼럼들이 다른 칼럼보다 먼저 출력된다. (DEPTNO가 첫번째 칼럼이 된다) 이 때 NATURAL JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리한다.
예제] 반면, INNER JOIN의 경우 첫 번째 테이블, 두 번째 테이블의 칼럼 순서대로 데이터가 출력된다. 이 때 NATURAL JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리하지만, INNER JOIN은 별개의 칼럼으로 표시한다.
SELECT *
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO=DEPT.DEPTNO;
SELECT *
FROM DEPT NATURAL JOIN DEPT_TEMP;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
SELECT *
FROM DEPT JOIN DEPT_TEMP
ON DEPT.DEPTNO=DEPT_TEMP.DEPTNO
AND DEPT.DNAME=DEPT_TEMP.DNAME
AND DEPT.LOC=DEPT_TEMP.LOC
DEPTNO DNAME LOC DEPTNO DNAME LOC
---------- -------------- ------------- ---------- -------------- -------------
10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON 40 OPERATIONS BOSTON
▷ USING 조건절
: FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수가 있다.
: SQL server에서 지원하지 않음
예제] 세 개의 칼럼명이 모두 같은 DEPT와 DEPT_TEMP 테이블을 DEPTNO 칼럼을 이용한 [INNER] JOIN의 USING 조건절로 수행한다.
SELECT *
FROM DEPT JOIN DEPT_TEMP
USING (DEPTNO);
DEPTNO DNAME LOC DNAME LOC
---------- -------------- ------------- -------------- -------------
10 ACCOUNTING NEW YORK ACCOUNTING NEW YORK
20 RESEARCH DALLAS RD DALLAS
30 SALES CHICAGO MARKETING CHICAGO
40 OPERATIONS BOSTON OPERATIONS BOSTON
=> 별도의 칼럼 순서를 지정하지 않으면 USING 조건절의 기준이 되는 칼럼이 다른 칼럼보다 먼저 출력된다.
이 때 USING JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리한다.
예제] USING 조건절을 이용한 EQUI JOIN에서도 NATURAL JOIN과 마찬가지로 JOIN 칼럼에 대해서는 ALILAS나 테이블 이름과 같은 접두사를 붙일 수 없다.(DEPT.DEPTNO->DEPTNO)
SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, DEPT_TEMP.DNAME, DEPT_TEMP.LOC
FROM DEPT JOIN DEPT_TEMP
USING (DEPTNO)
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier
수정 => SELECT DEPTNO, DEPT.DNAME, DEPT.LOC, DEPT_TEMP.DNAME, DEPT_TEMP.LOC
FROM DEPT JOIN DEPT_TEMP
USING (DEPTNO)
예제] DEPT와 DEPT_TEMP 테이블의 일부 내용이 변경되었던 DNAME 칼럼을 조인 조건으로 [INNER] JOIN의 USING 조건절을 수행
SELECT *
FROM DEPT JOIN DEPT_tEMP
USING (DNAME);
DNAME DEPTNO LOC DEPTNO LOC
-------------- ---------- ------------- ---------- -------------
ACCOUNTING 10 NEW YORK 10 NEW YORK
OPERATIONS 40 BOSTON 40 BOSTON
예제] 세 개의 칼럼명이 같은 DEPT와 DEPT_TEMP 테이블을 LOC와 DEPTNO 2개 칼럼을 이용한 [INNER] JOIN의 USING 조건절로 수행
SELECT *
FROM DEPT JOIN DEPT_TEMP
USING (LOC, DEPTNO);
LOC DEPTNO DNAME DNAME
------------- ---------- -------------- --------------
NEW YORK 10 ACCOUNTING ACCOUNTING
DALLAS 20 RESEARCH RD
CHICAGO 30 SALES MARKETING
BOSTON 40 OPERATIONS OPERATIONS
예제] DEPTNO, DNAME 2개의 칼럼을 이용한 [INNER] JOIN의 USING 조건절로 수행
SELECT *
FROM DEPT JOIN DEPT_TEMP
USING (DEPTNO, DNAME);
DEPTNO DNAME LOC LOC
---------- -------------- ------------- -------------
10 ACCOUNTING NEW YORK NEW YORK
40 OPERATIONS BOSTON BOSTON
▷ 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)
- 임의의 JOIN 조건을 지정하거나, 이름이 다른 칼럼명을 JOIN 조건으로 사용하거나, JOIN 칼럼을 명시하기 위해서는 ON 조건절을 사용.
- ON 조건절에 사용된 괄호는 옵션 사항
- USING 조건절을 이용한 JOIN 에서는 JOIN 칼럼에 대해서 ALIAS나 테이블 명과 같은 접두사를 사용하면 SYNTAX 에러가 발생하지만, 반대로 ON 조건절을 사용한 JOIN의 경우는 ALIAS나 테이블 명과 같은 접두사를 사용하여 SELECT에 사용되는 칼럼을 논리적으로 명확하게 지정해 주어야 한다.
- ON 조건절은 WHERE 절의 JOIN 조건과 같은 기능을 하면서도, 명시적으로 JOIN의 조건을 구분할 수 있으므로 가장 많이 사용될 것으로 예상된다. 다만 FROM 절에 테이블이 많이 사용될 경우 다소 복잡하게 보여 가독성이 떨어지는 단점도 있다.
1. WHERE 절과의 혼용
예제] ON 조건절과 WHERE 검색 조건은 충돌 없이 사용할 수 있다. 부서코드 30인 부서의 소속 사원 이름 및 소속 부서 코드, 부서 코드, 부서 이름을 찾아본다.
SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT P
ON (E.DEPTNO=D.DEPTNO)
WHERE E.DEPTNO=30
2. ON 조건절 + 데이터 검증 조건 추가
: ON 조건절에 JOIN 조건 외에도 데이터 검색 조건을 추가할 수는 있으나, 검색 조건 목적인 경우는 WHERE 절을 사용할 것을 권고한다. (다만, 아우터 조인의 대상을 제한하기 위한 목적으로 사용되는 추가 조건의 경우는 ON 절에 표기되어야 한다)
예제] 매니저 사원 번호가 7698번인 사원들의 이름 및 소속부서코드, 부서 이름을 찾아본다.
SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D
ON (E.DEPTNO=D.DEPTNO
AND E.MGR=7698)
SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D
ON (E.DEPTNO=D.DEPTNO)
WHERE E.MGR=7698
3. ON 조건절 예제
예제] 팀과 스타디움 테이블을 스타디움ID로 JOIN하여 팀이름, 스타디움ID, 스타디움 이름을 찾아본다
SELECT TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME
FROM TEAM JOIN STADIUM
ON TEAM.STADIUM_ID=STADIUM.STADIUM_ID
SELECT TEAM_NAME, STADIUM_ID, STADIUM_NAME
FROM TEAM JOIN STADIUM
USING (STADIUM_ID)
SELECT TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME
FROM TEAM, STADIUM
WHERE TEAM.STADIUM_ID=STADIUM.STADIUM_ID
예제] 팀과 스타디움 테이블을 팀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
SELECT TEAM_NAME, TEAM_ID, STADIUM_NAME
FROM TEAM,STADIUM
WHERE TEAM.TEAM_ID=STADIUM.HOMETEAM_ID
=> TEAM_ID와 HOMETEAM_ID라는 다른 이름의 칼럼을 사용하기 때문에 USING 조건절을 사용할 수 없다.
4. 다중 테이블 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)
SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME NEW_DNAME
FROM EMP E, DEPT D, DEPT_TEMP P
WHERE E.DEPTNO=D.DEPTNO
AND E.DEPTNO=T.DEPTNO
예제] GK 포지션의 선수별 연고지명, 팀명, 구장명을 출력한다.
SELECT P.PLAYER_NAME, P.POSITION, T.REGION_NAME, T.TEAM_NAME, S.STADIUM_NAME
FROM PLAYER P JOIN TEAM T
ON P.TEAM_ID=T.TEAM_ID
JOIN STADIUM S
ON T.STADIUM_ID=S.STADIUM_ID
WHERE P.POSITION='GK'
SELECT P.PLAYER_NAME, P.POSITION, T.REGION_NAME, T.TEAM_NAME, S.STADIUM_NAME
FROM PLAYER P, TEAM T, STADIUM S
WHERE P.TEAM_ID=T.TEAM_ID
AND T.STADIUM_ID=S.STADIUM_ID
AND P.POSITION='GK'
예제] 홈팀이 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
SELECT ST.STADIUM_NAME, SC.STADIUM_ID, SCHE_DATE, HT.TEAM_NAME, AT.TEAM_NAME, HOME_SCORE, AWAY_SCORE
FROM SCHEDULE SC, STADIUM ST, TEAM HT, TEAM AT
WHERE HOME_SCORE>=AWAY_SCORE +3
AND SC.STADIUM_ID=ST.STADIUM_ID
AND SC.HOMETEAM_ID=HT.TEAM_ID
AND SC.AWAYTEAM_ID=AT.TEAM_ID
▷ CROSS JOIN
: 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합
예제] 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾아본다.
SELECT ENAME, DNAME
FROM EMP CROSS JOIN DEPT
예제] CROSS JOIN에서 WHERE 절을 사용하면 INNER JOIN과 같은 결과를 얻는다
SELECT ENAME, DNAME
FROM EMP CROSS JOIN DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
SELECT ENAME, DNAME
FROM EMP INNER JOIN DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
▷ OUTER JOIN
: JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용
: 필수적으로 USING 조건절이나 ON 조건절 사용
: LEFT/RIGHT OUTER JOIN의 경우에는 기준이 되는 테이블이 조인 수행시 무조건 드라이빙 테이블이 된다
1. LEFT OUTER JOIN
: 조인 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다. 즉 TABLE A와 B가 있을 때 (TABLE 'A'가 기준), A와 B를 비교해서 B의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, B의 JOIN 칼럼에서 같은 값이 없는 경우에는 B 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다.
예제] STADIUM에 등록된 운동장 중에는 홈팀이 없는 경기장도 있다. STADIUM과 TEAM을 JOIN하되 홈팀이 없는 경기장의 정보도 같이 출력되도록 한다
SELECT STADIUM_NAME, STADIUM.STADIUM_ID, HOMETEAM_ID, TEAM_NAME
FROM STADIUM LEFT OUTER JOIN TEAM
ON STADIUM.HOMETEAM_ID=TEAM.TEAM_ID
2. RIGHT OUTER JOIN
: 조인 수행시 LEFT JOIN과 반대로 우측 테이블이 기준이 되어 결과를 생성한다. 즉, TABLE A와 B가 있을 때(TABLE B가 기준), A와 B를 비교해서 A의 JOIN 칼럼에서 같은 값이 있을 때 그 해당 데이터를 가져오고, A의 JOIN 칼럼에서 같은 값이 없는 경우에는 A테이블에서 가져오는 칼럼들은 NULL값으로 채운다.
예제] DEPT에 등록된 부서 중에는 사원이 없는 부서도 있다. DEPT와 EMP를 조인하되 사원이 없는 부서 정보도 같이 출력하도록 함
SELECT E.ENAME, D.DEPTNO, D.DNAME, D.LOC
FROM EMP E RIGHT JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
3. FULL OUTER JOIN
: 조인 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다. 즉 TABLE A와 B가 있을 때 (TABLE A,B 모두 기준이 됨) RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일
: UNION ALL이 아닌 UNION 기능과 같으므로 중복되는 데이터는 삭제한다.
예제] DEPT 테이블과 DEPT_TEMP 테이블의 FULL OUTER JOIN 사례를 만들기 위해 DEPT_TEMP의 DEPTNO를 수정한다. 결과적으로 DEPT_TEMP 테이블의 새로운 DEPTNO 데이터는 DEPT 테이블의 DEPTNO와 2건은 동일하고 2건은 새로운 DEPTNO가 생성됨
-> DEPTNO 기준으로 DEPT와 DEPT_TEMP 데이터를 FULL OUTER JOIN으로 출력
SELECT *
FROM DEPT FULL JOIN DEPT_TEMP
ON DEPT.DEPTNO=DEPT_TEMP.DEPTNO
DEPTNO DNAME LOC DEPTNO DNAME LOC
---------- -------------- ------------- ---------- -------------- -------------
30 SALES CHICAGO 30 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON 40 RD DALLAS
50 MARKETING CHICAGO
60 OPERATIONS BOSTON
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
SELECT D.DEPTNO, D.DNAME, D.LOC, DT.DEPTNO, DT.DNAME, DT.LOC
FROM DEPT D LEFT JOIN DEPT_TEMP DT
ON D.DEPTNO=DT.DEPTNO
UNION
SELECT D.DEPTNO, D.DNAME, D.LOC, DT.DEPTNO, DT.DNAME, DT.LOC
FROM DEPT D RIGHT JOIN DEPT_TEMP DT
ON D.DEPTNO=DT.DEPTNO
▷ 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의 결과
=> 양쪽 테이블의 데이터를 곱한 개수인 4*3=12건 추출
키 값 조합이 B-A, B-B, B-C, C-A, C-B, C-C, D-A, D-B, D-C, E-A, E-B, E-C 12건 출력
'Study > SQL전문가가이드' 카테고리의 다른 글
| 2.3 계층형 질의와 셀프 조인 (0) | 2015.11.18 |
|---|---|
| 2.2 집합 연산자(SET OPERATOR) (0) | 2015.11.17 |
| 1.4 관계 (0) | 2015.08.06 |
| 1.3 속성 (0) | 2015.08.06 |
| 1.2 엔터티 (0) | 2015.08.05 |
