제1절 표준 조인
1. Standard SQL 개요
1970년: Dr. E.F.Codd 관계형 DBMS(Relational DB) 논문 발표
1974년: IBM SQL 개발 1979년: Oracle 상용 DBMS 발표
1980년: Sybase SQL Server 발표 (이후 Sybase ASE로 개명)
1983년: IBM DB2 발표
1986년: ANSI/ISO SQL 표준 최초 제정 (SQL-86, SQL1)
1992년: ANSI/ISO SQL 표준 개정 (SQL-92, SQL2) <=== Altibase
1993년: MS SQL Server 발표 (Windows OS, Sybase Code 활용)
1999년: ANSI/ISO SQL 표준 개정 (SQL-99, SQL3)
2003년: ANSI/ISO SQL 표준 개정 (SQL-2003)
2008년: ANSI/ISO SQL 표준 개정 (SQL-2008)
# 대표적인 ANSI/ISO 표준 SQL의 기능
- STANDARD JOIN 기능 추가 (CROSS, OUTER JOIN 등 새로운 FROM 절 JOIN 기능들)
- SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 SUBQUERY 기능들
- ROLLUP, CUBE, GROUPING SETS 등의 새로운 리포팅 기능
- WINDOW FUNCTION 같은 새로운 개념의 분석 기능들
# E.F.Codd 박사의 논문에 언급된 8가지 관계형 대수(4개의 일반 집합 연산자와 4개의 순수 관계 연산자)
# JOIN의 정의
- RDBMS의 경우 요구 사항 분석, 개념적 데이터 모델링, 논리적 데이터 모델링, 물리적 데이터 모델링 단계를 거치면서 엔티티 확정 및 정규화, 그리고 M:M 관계 분해 절차를 밟게된다. 정규화 과정에서 데이터 정합성과 데이터 저장 공간의 절약을 위해 엔티티를 최대한 분리하는 작업으로, 3차 정규형이나 보이스 코드 정규형까지 진행하게 된다. 이런 정규화를 거치면 하나의 주제에 관련 있는 엔티티가 여러 개로 나눠지게 되고, 이 엔티티들이 주로 테이블이 되는데, 이렇게 흩어진 데이터를 연결해서 원하는 데이터를 가져오는 작업을 말한다.
2. FROM 절 JOIN 형태
- INNER JOIN (기본 조인 방식)
- NATURAL JOIN
- USING 조건절
- ON 조건절
- CROSS JOIN
- OUTER JOIN
# 각 INNER JOIN 형태 비교
3. INNER JOIN (Join의 기본 형태)
- 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 키워드 생략 가능
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
4. NATRUAL JOIN - 두 테이블 간의 동일한 이름을 갖는 모든 컬럼에 대해 EQUI JOIN 수행
- Altibase도 안됨. Oracle만 됨
[ERR-31001 : SQL syntax error
line 1: parse error
SELECT TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME FROM TEAM NATURAL JOIN STADIUM
^
]
- 공통 컬럼이 맨 선두에 위치해 있고, 공통 컬럼은 한번만 출력한다.
SQL> SELECT DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT;
DEPTNO EMPNO ENAME DNAME
---------- ---------- -------------------- ----------------------------
10 7782 CLARK ACCOUNTING
10 7839 KING ACCOUNTING
10 7934 MILLER ACCOUNTING
20 7566 JONES RESEARCH
20 7902 FORD RESEARCH
20 7876 ADAMS RESEARCH
20 7369 SMITH RESEARCH
20 7788 SCOTT RESEARCH
30 7521 WARD SALES
30 7844 TURNER SALES
30 7499 ALLEN SALES
DEPTNO EMPNO ENAME DNAME
---------- ---------- -------------------- ----------------------------
30 7900 JAMES SALES
30 7698 BLAKE SALES
30 7654 MARTIN SALES
14 rows selected.
* INNER JOIN의 경우
SQL> SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ---------- ---------- ---------------------------- --------------------------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ---------- ---------- ---------------------------- --------------------------
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
14 rows selected.
- ALIAS나 테이블 명과 같은 접두사 사용 안됨.
SQL> SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT;
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT
*
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
- 동일한 구조의 테이블을 가지고 NATURAL JOIN과 INNER JOIN 결과를 비교해 보면...
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from dept_temp;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RnD DALLAS
30 MARKETING CHICAGO
40 OPERATIONS BOSTON
SQL> SELECT * FROM DEPT NATURAL INNER JOIN DEPT_TEMP;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
SQL> SELECT * FROM DEPT NATURAL JOIN DEPT_TEMP;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
SQL> 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
SQL> SELECT * FROM DEPT, DEPT_TEMP WHERE 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
- Altibase도 USING 지원 안된다.
[ERR-31001 : SQL syntax error
line 1: parse error
SELECT TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME FROM TEAM ,STADIUM USING(STADIUM_ID)
^ ^
]
- NATURAL JOIN 대상 컬럼은 선두에 위치하고 한번만 출력된다.
SQL> SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);
DEPTNO DNAME LOC DNAME LOC
---------- ---------------------------- -------------------------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK ACCOUNTING NEW YORK
20 RESEARCH DALLAS RnD DALLAS
30 SALES CHICAGO MARKETING CHICAGO
40 OPERATIONS BOSTON OPERATIONS BOSTON
- NATURAL JOIN 처럼 Alias나 테이블 명과 같은 접두사는 사용할 수 없다.
SQL> SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, DEPT_TEMP.DNAME, DEPT_TEMP.LOC FROM DEPT JOIN DEPT_TEMP USING (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
- DNAME이 바뀐 20, 30번 부서 출력 안됨.
SQL> 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
- LOC,DEPTNO는 모두 일치
SQL> SELECT * FROM DEPT JOIN DEPT_TEMP USING (LOC, DEPTNO);
LOC DEPTNO DNAME DNAME
-------------------------- ---------- ---------------------------- ----------------------------
NEW YORK 10 ACCOUNTING ACCOUNTING
DALLAS 20 RESEARCH RnD
CHICAGO 30 SALES MARKETING
BOSTON 40 OPERATIONS OPERATIONS
- Join 대상 조건 중 DNAME이 불일치하여 단 2건 출력
SQL> SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO, DNAME);
DEPTNO DNAME LOC LOC
---------- ---------------------------- -------------------------- --------------------------
10 ACCOUNTING NEW YORK NEW YORK
40 OPERATIONS BOSTON BOSTON
6. ON 조건절 : Join 조건절을 따로 뺄 수 있다.
- NATURAL JOIN과 다르게 다른 이름을 가진 컬럼을 Join 대상으로 지정 가능
- 임의의 조인 조건 지정 가능
SQL> SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);
EMPNO ENAME DEPTNO DNAME
---------- -------------------- ---------- ----------------------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7788 SCOTT 20 RESEARCH
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
EMPNO ENAME DEPTNO DNAME
---------- -------------------- ---------- ----------------------------
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
14 rows selected.
# Where 절과 혼용 가능
SQL> SELECT E.ENAME, E.DEPTNO, D.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE E.DEPTNO = 30;
ENAME DEPTNO DEPTNO DNAME
-------------------- ---------- ---------- ----------------------------
ALLEN 30 30 SALES
WARD 30 30 SALES
MARTIN 30 30 SALES
BLAKE 30 30 SALES
TURNER 30 30 SALES
JAMES 30 30 SALES
6 rows selected.
# ON 조건절 + 데이터 검증 조건 추가
SQL> SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO AND E.MGR = 7698);
ENAME MGR DEPTNO DNAME
-------------------- ---------- ---------- ----------------------------
ALLEN 7698 30 SALES
WARD 7698 30 SALES
MARTIN 7698 30 SALES
TURNER 7698 30 SALES
JAMES 7698 30 SALES
SQL> SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) WHERE E.MGR = 7698;
ENAME MGR DEPTNO DNAME
-------------------- ---------- ---------- ----------------------------
ALLEN 7698 30 SALES
WARD 7698 30 SALES
MARTIN 7698 30 SALES
TURNER 7698 30 SALES
JAMES 7698 30 SALES
# Join 대상 컬럼명이 동일할 경우 ON 조건절을 USING 절로 변환 가능
SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
SQL> SELECT ENAME, MGR, DEPTNO, DNAME FROM EMP JOIN DEPT USING(DEPTNO);
ENAME MGR DEPTNO DNAME
-------------------- ---------- ---------- ----------------------------
CLARK 7839 10 ACCOUNTING
KING 10 ACCOUNTING
MILLER 7782 10 ACCOUNTING
JONES 7839 20 RESEARCH
FORD 7566 20 RESEARCH
ADAMS 7788 20 RESEARCH
SMITH 7902 20 RESEARCH
SCOTT 7566 20 RESEARCH
WARD 7698 30 SALES
TURNER 7698 30 SALES
ALLEN 7698 30 SALES
ENAME MGR DEPTNO DNAME
-------------------- ---------- ---------- ----------------------------
JAMES 7698 30 SALES
BLAKE 7839 30 SALES
MARTIN 7698 30 SALES
14 rows selected.
# Join 대상 컬럼명이 동일하지 않을 경우 ON 조건절을 USING 절로 변환 가능
SQL> select A.ENAME, B.MGR, A.DEPTNO, B.DEPTNO from EMP A JOIN EMP B ON A.EMPNO=B.EMPNO;
ENAME MGR DEPTNO DEPTNO
-------------------- ---------- ---------- ----------
SMITH
SMITH 7902 20 20
ALLEN 7698 30 30
WARD 7698 30 30
JONES 7839 20 20
MARTIN 7698 30 30
BLAKE 7839 30 30
CLARK 7839 10 10
SCOTT 7566 20 20
KING 10 10
TURNER 7698 30 30
ENAME MGR DEPTNO DEPTNO
-------------------- ---------- ---------- ----------
ADAMS 7788 20 20
JAMES 7698 30 30
FORD 7566 20 20
MILLER 7782 10 10
16 rows selected.
SQL> select A.ENAME, B.MGR, A.DEPTNO, B.DEPTNO from EMP A, EMP B WHERE A.EMPNO=B.EMPNO;
ENAME MGR DEPTNO DEPTNO
-------------------- ---------- ---------- ----------
SMITH
SMITH 7902 20 20
ALLEN 7698 30 30
WARD 7698 30 30
JONES 7839 20 20
MARTIN 7698 30 30
BLAKE 7839 30 30
CLARK 7839 10 10
SCOTT 7566 20 20
KING 10 10
TURNER 7698 30 30
ENAME MGR DEPTNO DEPTNO
-------------------- ---------- ---------- ----------
ADAMS 7788 20 20
JAMES 7698 30 30
FORD 7566 20 20
MILLER 7782 10 10
16 rows selected.
# 다중 테이블 JOIN
SQL> 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);
SQL> SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME New_DNAME FROM EMP E, DEPT D, DEPT_TEMP T WHERE E.DEPTNO = D.DEPTNO AND E.DEPTNO = T.DEPTNO;
EMPNO DEPTNO DNAME NEW_DNAME
---------- ---------- ---------------------------- ----------------------------
7934 10 ACCOUNTING ACCOUNTING
7839 10 ACCOUNTING ACCOUNTING
7782 10 ACCOUNTING ACCOUNTING
7788 20 RESEARCH RnD
7369 20 RESEARCH RnD
7876 20 RESEARCH RnD
7902 20 RESEARCH RnD
7566 20 RESEARCH RnD
7654 30 SALES MARKETING
7698 30 SALES MARKETING
7900 30 SALES MARKETING
EMPNO DEPTNO DNAME NEW_DNAME
---------- ---------- ---------------------------- ----------------------------
7499 30 SALES MARKETING
7844 30 SALES MARKETING
7521 30 SALES MARKETING
14 rows selected.
- Altibase는 Join 절을 이용해 3개의 테이블까지 join 가능하지만, 그 이상이면 syntax error
iSQL> select *
2 FROM SCHEDULE SC JOIN STADIUM ST
3 ON SC.STADIUM_ID = ST.STADIUM_ID
4 JOIN TEAM HT
5 ON SC.HOMETEAM_ID = HT.TEAM_ID
6 JOIN TEAM AT
7 ON SC.AWAYTEAM_ID = AT.TEAM_ID
8 WHERE HOME_SCORE > = AWAY_SCORE +3;
[ERR-31001 : SQL syntax error
line 6: parse error
JOIN TEAM AT
^^
]
WHERE 조건절로 바꿔도 에러남
iSQL> select *
2 FROM SCHEDULE SC, STADIUM ST, TEAM HT, TEAM AT
3 WHERE HOME_SCORE >= AWAY_SCORE +3
4 AND SC.STADIUM_ID = ST.STADIUM_ID
5 AND SC.HOMETEAM_ID = HT.TEAM_ID
6 AND SC.AWAYTEAM_ID = AT.TEAM_ID;
[ERR-31001 : SQL syntax error
line 2: parse error
FROM SCHEDULE SC, STADIUM ST, TEAM HT, TEAM AT
^^
]
위 syntax error는 'AT' 라는 Alias 때문에 발생하는 것인데,
altibase 경우 범위 파티션에서 SPILT 할때 AT 키워드가 포함된다.
ALTER TABLE T1 SPILT PARTITION P3 AT ( 350) INTO (PARTITION P_200_350, PARTITION P_OVER_350);
Alias 명을 D 로 바꾸니 실행 잘됨 ㅋ (조인 대상 테이블이 4개로 제한되었다는 가설은 틀린 것이었음. ㅋㅋ)
iSQL> select count(*)
FROM SCHEDULE SC, STADIUM ST, TEAM HT, TEAM D
WHERE HOME_SCORE >= AWAY_SCORE +3
AND SC.STADIUM_ID = ST.STADIUM_ID
AND SC.HOMETEAM_ID = HT.TEAM_ID
AND SC.AWAYTEAM_ID = D.TEAM_ID;
iSQL>
iSQL> /
COUNT
-----------------------
5
1 row selected.
7. CROSS JOIN : 테이블 간 Join 조건이 없는 경우 생길 수 있는 모든 데이터 조합, Cartesian product라고도 함. 결과는 M*N 건
용도 : DW에서 스타 스키마 구조의 개별 DIMEMSION을 FACT 컬럼과 JOIN하기 전에 모든 DEMENSION table의 CROSS PRODUCT를 먼저 구할때
SQL> select count(*) from emp;
COUNT(*)
----------
16
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL>
SQL> select count(*) from emp cross join dept;
COUNT(*)
----------
64
SQL> select count(*) from emp, dept; <== 키워드를 생략해도 됨.
COUNT(*)
----------
64
- 조인 조건이 추가되버리면 INNER JOIN과 같은 결과를 얻기 때문에 무의미함.
SQL> SELECT ENAME, DNAME FROM EMP CROSS JOIN DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
ENAME DNAME
-------------------- ----------------------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
TURNER SALES
ALLEN SALES
ENAME DNAME
-------------------- ----------------------------
JAMES SALES
BLAKE SALES
MARTIN SALES
14 rows selected.
# LEFT OUTER JOIN
iSQL> select * from tab1 left outer join tab2 on tab1.c1=tab2.c1;
C1 C2 C3 C1 C2 C3
--------------------------------------------------------
A AAB 111 A 10 AA
B AAC 123 B 10 AB
C ABA 222 C 10 AC
D ABB 233
E ABC 143
5 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
LEFT-OUTER-JOIN
SCAN ( TABLE: TAB1, FULL SCAN, ACCESS: 5, SELF_ID: 1 )
HASH ( ITEM_SIZE: 24, ITEM_COUNT: 3, BUCKET_COUNT: 1024, ACCESS: 5, SELF_ID: 3, REF_ID: 2 )
SCAN ( TABLE: TAB2, FULL SCAN, ACCESS: 5, SELF_ID: 2 )
------------------------------------------------------------
iSQL> select /*+ USE_NL(A,B) */ * from tab1 a left join tab2 b on a.c1=b.c1;
C1 C2 C3 C1 C2 C3
--------------------------------------------------------
A AAB 111 A 10 AA
B AAC 123 B 10 AB
C ABA 222 C 10 AC
D ABB 233
E ABC 143
5 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
LEFT-OUTER-JOIN
SCAN ( TABLE: TAB1 A, FULL SCAN, ACCESS: 5, SELF_ID: 1 )
SCAN ( TABLE: TAB2 B, FULL SCAN, ACCESS: 17, SELF_ID: 2 )
------------------------------------------------------------
<오라클 결과>
SQL> select * from tab1 left outer join tab2 on tab1.c1=tab2.c1;
C1 C2 C3 C1 C2 C3
-- ------ ---------- -- ---------- --------------------
A AAB 111 A 10 AA
B AAC 123 B 10 AB
C ABA 222 C 10 AC
E ABC 143
D ABB 233
# RIGTH OUTER JOIN
iSQL> select /*+ USE_NL(A,B) */ * from tab1 a right outer join tab2 b on a.c1=b.c1;
C1 C2 C3 C1 C2 C3
--------------------------------------------------------
A AAB 111 A 10 AA
B AAC 123 B 10 AB
C ABA 222 C 10 AC
3 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
LEFT-OUTER-JOIN <------------------ RIGHT OUTER JOIN 연산자가 아니다~!!!!
SCAN ( TABLE: TAB2 B, FULL SCAN, ACCESS: 3, SELF_ID: 2 )
SCAN ( TABLE: TAB1 A, FULL SCAN, ACCESS: 15, SELF_ID: 1 )
------------------------------------------------------------
< Full outer join 테스트 : left outer join 결과와 right outer join 결과를 union하여 비교>
SQL> UPDATE DEPT_TEMP SET DEPTNO = DEPTNO + 20;
4 rows updated.
SQL> SELECT * FROM DEPT_TEMP;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
30 ACCOUNTING NEW YORK
40 RnD DALLAS
50 MARKETING CHICAGO
60 OPERATIONS BOSTON
SQL> SELECT * FROM DEPT FULL OUTER 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 RnD DALLAS
50 MARKETING CHICAGO
60 OPERATIONS BOSTON
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
6 rows selected.
SQL> 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;
DEPTNO DNAME LOC DEPTNO DNAME LOC
---------- ---------------------------- -------------------------- ---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO 30 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON 40 RnD DALLAS
50 MARKETING CHICAGO
60 OPERATIONS BOSTON
6 rows selected.
- Plan상으로 봤을 때 left outer join과 동일하다.
iSQL> select * from tab1 a full outer join tab2 b on a.c1=b.c1;
C1 C2 C3 C1 C2 C3
--------------------------------------------------------
A AAB 111 A 10 AA
B AAC 123 B 10 AB
C ABA 222 C 10 AC
E ABC 143
D ABB 233
F 10 BC
6 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
FULL-OUTER-JOIN
SCAN ( TABLE: TAB1 A, FULL SCAN, ACCESS: 6, SELF_ID: 1 )
HASH ( ITEM_SIZE: 24, ITEM_COUNT: 4, BUCKET_COUNT: 1024, ACCESS: 6, SELF_ID: 3, REF_ID: 2 )
SCAN ( TABLE: TAB2 B, FULL SCAN, ACCESS: 6, SELF_ID: 2 )
------------------------------------------------------------
iSQL> select * from tab1 a left outer join tab2 b on a.c1=b.c1
union
select * from tab1 a right outer join tab2 b on a.c1=b.c1;
iSQL> /
C1 C2 C3 C1 C2 C3
--------------------------------------------------------
A AAB 111 A 10 AA
B AAC 123 B 10 AB
C ABA 222 C 10 AC
E ABC 143
D ABB 233
F 10 BC
6 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
DISTINCT ( ITEM_SIZE: 88, ITEM_COUNT: 6, BUCKET_COUNT: 1024, ACCESS: 6, SELF_ID: 8, REF_ID: 5 )
VIEW ( ACCESS: 9, SELF_ID: 5 )
BAG-UNION
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
LEFT-OUTER-JOIN
SCAN ( TABLE: TAB1 A, FULL SCAN, ACCESS: 5, SELF_ID: 1 )
HASH ( ITEM_SIZE: 24, ITEM_COUNT: 4, BUCKET_COUNT: 1024, ACCESS: 5, SELF_ID: 6, REF_ID: 2 )
SCAN ( TABLE: TAB2 B, FULL SCAN, ACCESS: 6, SELF_ID: 2 )
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
LEFT-OUTER-JOIN <------------------ RIGHT OUTER JOIN 연산자가 아니다~!!!!
SCAN ( TABLE: TAB2 B, FULL SCAN, ACCESS: 4, SELF_ID: 4 )
HASH ( ITEM_SIZE: 24, ITEM_COUNT: 5, BUCKET_COUNT: 1024, ACCESS: 4, SELF_ID: 7, REF_ID: 3 )
SCAN ( TABLE: TAB1 A, FULL SCAN, ACCESS: 6, SELF_ID: 3 )
------------------------------------------------------------
1. Standard SQL 개요
1970년: Dr. E.F.Codd 관계형 DBMS(Relational DB) 논문 발표
1974년: IBM SQL 개발 1979년: Oracle 상용 DBMS 발표
1980년: Sybase SQL Server 발표 (이후 Sybase ASE로 개명)
1983년: IBM DB2 발표
1986년: ANSI/ISO SQL 표준 최초 제정 (SQL-86, SQL1)
1992년: ANSI/ISO SQL 표준 개정 (SQL-92, SQL2) <=== Altibase
1993년: MS SQL Server 발표 (Windows OS, Sybase Code 활용)
1999년: ANSI/ISO SQL 표준 개정 (SQL-99, SQL3)
2003년: ANSI/ISO SQL 표준 개정 (SQL-2003)
2008년: ANSI/ISO SQL 표준 개정 (SQL-2008)
# 대표적인 ANSI/ISO 표준 SQL의 기능
- STANDARD JOIN 기능 추가 (CROSS, OUTER JOIN 등 새로운 FROM 절 JOIN 기능들)
- SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 SUBQUERY 기능들
- ROLLUP, CUBE, GROUPING SETS 등의 새로운 리포팅 기능
- WINDOW FUNCTION 같은 새로운 개념의 분석 기능들
# E.F.Codd 박사의 논문에 언급된 8가지 관계형 대수(4개의 일반 집합 연산자와 4개의 순수 관계 연산자)
| Seqno | 분류 | 관계형 대수 | SQL | 설명 |
| 1 | 일반 집한 연산자 | UNION | UNION | UNIONX은 정렬 작업 수행, UNION ALL 사용 권고 |
| 2 | INTERSECTION | INTERSECT | 교집합 | |
| 3 | DIFFERENCE | EXCEPT or MINUX(Oracle) | 차집합 | |
| 4 | PRODUCT | CROSS JOIN(Cartesian product) | JOIN 조건이 없을 경우 M*N 데이터 조합 발생 | |
| Seqno | 분류 | 관계형 대수 | SQL | 설명 |
| 5 | 순수 관계 연산자 | SELECT | WHERE 조건절 | |
| 6 | PROJECT | SELECT 절 | ||
| 7 | (NATURAL) JOIN | JOIN | WHERE절의 INNER JOIN 조건, FROM절의 NATURAL JON, INNER JOIN, OUTER JOIN, USING 조건절, ON 조건절 | |
| 8 | DIVIDE | 현재 사용되지 않음 |
# JOIN의 정의
- RDBMS의 경우 요구 사항 분석, 개념적 데이터 모델링, 논리적 데이터 모델링, 물리적 데이터 모델링 단계를 거치면서 엔티티 확정 및 정규화, 그리고 M:M 관계 분해 절차를 밟게된다. 정규화 과정에서 데이터 정합성과 데이터 저장 공간의 절약을 위해 엔티티를 최대한 분리하는 작업으로, 3차 정규형이나 보이스 코드 정규형까지 진행하게 된다. 이런 정규화를 거치면 하나의 주제에 관련 있는 엔티티가 여러 개로 나눠지게 되고, 이 엔티티들이 주로 테이블이 되는데, 이렇게 흩어진 데이터를 연결해서 원하는 데이터를 가져오는 작업을 말한다.
2. FROM 절 JOIN 형태
- INNER JOIN (기본 조인 방식)
- NATURAL JOIN
- USING 조건절
- ON 조건절
- CROSS JOIN
- OUTER JOIN
# 각 INNER JOIN 형태 비교
| JOIN 형태 | EQUI JOIN | ALIAS | JOIN 대상 컬럼 위치 변경 및 단일 출력 | JOIN 대상 컬럼명 동일 | 기능 구현 |
|---|---|---|---|---|---|
| INNER JOIN | O | O | X | may | ALL |
| NATURAL JOIN | O | X | O | must | Oracle |
| USING | O | X | O | must | Oracle |
| ON | O | O | X | may | ALL |
| CROSS JOIN | O | O |
3. INNER JOIN (Join의 기본 형태)
- 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 키워드 생략 가능
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
4. NATRUAL JOIN - 두 테이블 간의 동일한 이름을 갖는 모든 컬럼에 대해 EQUI JOIN 수행
- Altibase도 안됨. Oracle만 됨
[ERR-31001 : SQL syntax error
line 1: parse error
SELECT TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME FROM TEAM NATURAL JOIN STADIUM
^
]
- 공통 컬럼이 맨 선두에 위치해 있고, 공통 컬럼은 한번만 출력한다.
SQL> SELECT DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT;
DEPTNO EMPNO ENAME DNAME
---------- ---------- -------------------- ----------------------------
10 7782 CLARK ACCOUNTING
10 7839 KING ACCOUNTING
10 7934 MILLER ACCOUNTING
20 7566 JONES RESEARCH
20 7902 FORD RESEARCH
20 7876 ADAMS RESEARCH
20 7369 SMITH RESEARCH
20 7788 SCOTT RESEARCH
30 7521 WARD SALES
30 7844 TURNER SALES
30 7499 ALLEN SALES
DEPTNO EMPNO ENAME DNAME
---------- ---------- -------------------- ----------------------------
30 7900 JAMES SALES
30 7698 BLAKE SALES
30 7654 MARTIN SALES
14 rows selected.
* INNER JOIN의 경우
SQL> SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ---------- ---------- ---------------------------- --------------------------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK
7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 20 RESEARCH DALLAS
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- ------------ ---------- ---------- ---------- ---------- ---------------------------- --------------------------
7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO
14 rows selected.
- ALIAS나 테이블 명과 같은 접두사 사용 안됨.
SQL> SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT;
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM EMP NATURAL JOIN DEPT
*
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
- 동일한 구조의 테이블을 가지고 NATURAL JOIN과 INNER JOIN 결과를 비교해 보면...
SQL> select * from dept;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from dept_temp;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RnD DALLAS
30 MARKETING CHICAGO
40 OPERATIONS BOSTON
SQL> SELECT * FROM DEPT NATURAL INNER JOIN DEPT_TEMP;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
SQL> SELECT * FROM DEPT NATURAL JOIN DEPT_TEMP;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON
SQL> 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
SQL> SELECT * FROM DEPT, DEPT_TEMP WHERE 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
5. USING 조건절
- 기본적으로 NATURAL JOIN 방식으로 수행하지만, JOIN 대상 컬럼을 지정할 수 있다.- Altibase도 USING 지원 안된다.
[ERR-31001 : SQL syntax error
line 1: parse error
SELECT TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME FROM TEAM ,STADIUM USING(STADIUM_ID)
^ ^
]
- NATURAL JOIN 대상 컬럼은 선두에 위치하고 한번만 출력된다.
SQL> SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);
DEPTNO DNAME LOC DNAME LOC
---------- ---------------------------- -------------------------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK ACCOUNTING NEW YORK
20 RESEARCH DALLAS RnD DALLAS
30 SALES CHICAGO MARKETING CHICAGO
40 OPERATIONS BOSTON OPERATIONS BOSTON
- NATURAL JOIN 처럼 Alias나 테이블 명과 같은 접두사는 사용할 수 없다.
SQL> SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, DEPT_TEMP.DNAME, DEPT_TEMP.LOC FROM DEPT JOIN DEPT_TEMP USING (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
- DNAME이 바뀐 20, 30번 부서 출력 안됨.
SQL> 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
- LOC,DEPTNO는 모두 일치
SQL> SELECT * FROM DEPT JOIN DEPT_TEMP USING (LOC, DEPTNO);
LOC DEPTNO DNAME DNAME
-------------------------- ---------- ---------------------------- ----------------------------
NEW YORK 10 ACCOUNTING ACCOUNTING
DALLAS 20 RESEARCH RnD
CHICAGO 30 SALES MARKETING
BOSTON 40 OPERATIONS OPERATIONS
- Join 대상 조건 중 DNAME이 불일치하여 단 2건 출력
SQL> SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO, DNAME);
DEPTNO DNAME LOC LOC
---------- ---------------------------- -------------------------- --------------------------
10 ACCOUNTING NEW YORK NEW YORK
40 OPERATIONS BOSTON BOSTON
6. ON 조건절 : Join 조건절을 따로 뺄 수 있다.
- NATURAL JOIN과 다르게 다른 이름을 가진 컬럼을 Join 대상으로 지정 가능
- 임의의 조인 조건 지정 가능
SQL> SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);
EMPNO ENAME DEPTNO DNAME
---------- -------------------- ---------- ----------------------------
7782 CLARK 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7566 JONES 20 RESEARCH
7902 FORD 20 RESEARCH
7876 ADAMS 20 RESEARCH
7369 SMITH 20 RESEARCH
7788 SCOTT 20 RESEARCH
7521 WARD 30 SALES
7844 TURNER 30 SALES
7499 ALLEN 30 SALES
EMPNO ENAME DEPTNO DNAME
---------- -------------------- ---------- ----------------------------
7900 JAMES 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
14 rows selected.
# Where 절과 혼용 가능
SQL> SELECT E.ENAME, E.DEPTNO, D.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE E.DEPTNO = 30;
ENAME DEPTNO DEPTNO DNAME
-------------------- ---------- ---------- ----------------------------
ALLEN 30 30 SALES
WARD 30 30 SALES
MARTIN 30 30 SALES
BLAKE 30 30 SALES
TURNER 30 30 SALES
JAMES 30 30 SALES
6 rows selected.
# ON 조건절 + 데이터 검증 조건 추가
SQL> SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO AND E.MGR = 7698);
ENAME MGR DEPTNO DNAME
-------------------- ---------- ---------- ----------------------------
ALLEN 7698 30 SALES
WARD 7698 30 SALES
MARTIN 7698 30 SALES
TURNER 7698 30 SALES
JAMES 7698 30 SALES
SQL> SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) WHERE E.MGR = 7698;
ENAME MGR DEPTNO DNAME
-------------------- ---------- ---------- ----------------------------
ALLEN 7698 30 SALES
WARD 7698 30 SALES
MARTIN 7698 30 SALES
TURNER 7698 30 SALES
JAMES 7698 30 SALES
# Join 대상 컬럼명이 동일할 경우 ON 조건절을 USING 절로 변환 가능
SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
SQL> SELECT ENAME, MGR, DEPTNO, DNAME FROM EMP JOIN DEPT USING(DEPTNO);
ENAME MGR DEPTNO DNAME
-------------------- ---------- ---------- ----------------------------
CLARK 7839 10 ACCOUNTING
KING 10 ACCOUNTING
MILLER 7782 10 ACCOUNTING
JONES 7839 20 RESEARCH
FORD 7566 20 RESEARCH
ADAMS 7788 20 RESEARCH
SMITH 7902 20 RESEARCH
SCOTT 7566 20 RESEARCH
WARD 7698 30 SALES
TURNER 7698 30 SALES
ALLEN 7698 30 SALES
ENAME MGR DEPTNO DNAME
-------------------- ---------- ---------- ----------------------------
JAMES 7698 30 SALES
BLAKE 7839 30 SALES
MARTIN 7698 30 SALES
14 rows selected.
# Join 대상 컬럼명이 동일하지 않을 경우 ON 조건절을 USING 절로 변환 가능
SQL> select A.ENAME, B.MGR, A.DEPTNO, B.DEPTNO from EMP A JOIN EMP B ON A.EMPNO=B.EMPNO;
ENAME MGR DEPTNO DEPTNO
-------------------- ---------- ---------- ----------
SMITH
SMITH 7902 20 20
ALLEN 7698 30 30
WARD 7698 30 30
JONES 7839 20 20
MARTIN 7698 30 30
BLAKE 7839 30 30
CLARK 7839 10 10
SCOTT 7566 20 20
KING 10 10
TURNER 7698 30 30
ENAME MGR DEPTNO DEPTNO
-------------------- ---------- ---------- ----------
ADAMS 7788 20 20
JAMES 7698 30 30
FORD 7566 20 20
MILLER 7782 10 10
16 rows selected.
SQL> select A.ENAME, B.MGR, A.DEPTNO, B.DEPTNO from EMP A, EMP B WHERE A.EMPNO=B.EMPNO;
ENAME MGR DEPTNO DEPTNO
-------------------- ---------- ---------- ----------
SMITH
SMITH 7902 20 20
ALLEN 7698 30 30
WARD 7698 30 30
JONES 7839 20 20
MARTIN 7698 30 30
BLAKE 7839 30 30
CLARK 7839 10 10
SCOTT 7566 20 20
KING 10 10
TURNER 7698 30 30
ENAME MGR DEPTNO DEPTNO
-------------------- ---------- ---------- ----------
ADAMS 7788 20 20
JAMES 7698 30 30
FORD 7566 20 20
MILLER 7782 10 10
16 rows selected.
# 다중 테이블 JOIN
SQL> 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);
SQL> SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME New_DNAME FROM EMP E, DEPT D, DEPT_TEMP T WHERE E.DEPTNO = D.DEPTNO AND E.DEPTNO = T.DEPTNO;
EMPNO DEPTNO DNAME NEW_DNAME
---------- ---------- ---------------------------- ----------------------------
7934 10 ACCOUNTING ACCOUNTING
7839 10 ACCOUNTING ACCOUNTING
7782 10 ACCOUNTING ACCOUNTING
7788 20 RESEARCH RnD
7369 20 RESEARCH RnD
7876 20 RESEARCH RnD
7902 20 RESEARCH RnD
7566 20 RESEARCH RnD
7654 30 SALES MARKETING
7698 30 SALES MARKETING
7900 30 SALES MARKETING
EMPNO DEPTNO DNAME NEW_DNAME
---------- ---------- ---------------------------- ----------------------------
7499 30 SALES MARKETING
7844 30 SALES MARKETING
7521 30 SALES MARKETING
14 rows selected.
- Altibase는 Join 절을 이용해 3개의 테이블까지 join 가능하지만, 그 이상이면 syntax error
iSQL> select *
2 FROM SCHEDULE SC JOIN STADIUM ST
3 ON SC.STADIUM_ID = ST.STADIUM_ID
4 JOIN TEAM HT
5 ON SC.HOMETEAM_ID = HT.TEAM_ID
6 JOIN TEAM AT
7 ON SC.AWAYTEAM_ID = AT.TEAM_ID
8 WHERE HOME_SCORE > = AWAY_SCORE +3;
[ERR-31001 : SQL syntax error
line 6: parse error
JOIN TEAM AT
^^
]
WHERE 조건절로 바꿔도 에러남
iSQL> select *
2 FROM SCHEDULE SC, STADIUM ST, TEAM HT, TEAM AT
3 WHERE HOME_SCORE >= AWAY_SCORE +3
4 AND SC.STADIUM_ID = ST.STADIUM_ID
5 AND SC.HOMETEAM_ID = HT.TEAM_ID
6 AND SC.AWAYTEAM_ID = AT.TEAM_ID;
[ERR-31001 : SQL syntax error
line 2: parse error
FROM SCHEDULE SC, STADIUM ST, TEAM HT, TEAM AT
^^
]
위 syntax error는 'AT' 라는 Alias 때문에 발생하는 것인데,
altibase 경우 범위 파티션에서 SPILT 할때 AT 키워드가 포함된다.
ALTER TABLE T1 SPILT PARTITION P3 AT ( 350) INTO (PARTITION P_200_350, PARTITION P_OVER_350);
Alias 명을 D 로 바꾸니 실행 잘됨 ㅋ (조인 대상 테이블이 4개로 제한되었다는 가설은 틀린 것이었음. ㅋㅋ)
iSQL> select count(*)
FROM SCHEDULE SC, STADIUM ST, TEAM HT, TEAM D
WHERE HOME_SCORE >= AWAY_SCORE +3
AND SC.STADIUM_ID = ST.STADIUM_ID
AND SC.HOMETEAM_ID = HT.TEAM_ID
AND SC.AWAYTEAM_ID = D.TEAM_ID;
iSQL>
iSQL> /
COUNT
-----------------------
5
1 row selected.
7. CROSS JOIN : 테이블 간 Join 조건이 없는 경우 생길 수 있는 모든 데이터 조합, Cartesian product라고도 함. 결과는 M*N 건
용도 : DW에서 스타 스키마 구조의 개별 DIMEMSION을 FACT 컬럼과 JOIN하기 전에 모든 DEMENSION table의 CROSS PRODUCT를 먼저 구할때
SQL> select count(*) from emp;
COUNT(*)
----------
16
SQL> select count(*) from dept;
COUNT(*)
----------
4
SQL>
SQL> select count(*) from emp cross join dept;
COUNT(*)
----------
64
SQL> select count(*) from emp, dept; <== 키워드를 생략해도 됨.
COUNT(*)
----------
64
- 조인 조건이 추가되버리면 INNER JOIN과 같은 결과를 얻기 때문에 무의미함.
SQL> SELECT ENAME, DNAME FROM EMP CROSS JOIN DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
ENAME DNAME
-------------------- ----------------------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
FORD RESEARCH
ADAMS RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
WARD SALES
TURNER SALES
ALLEN SALES
ENAME DNAME
-------------------- ----------------------------
JAMES SALES
BLAKE SALES
MARTIN SALES
14 rows selected.
8. OUTER JOIN
- 명시적인 OUTER JOIN 키워드 사용을 적극 권장
- INNER JOIN 처럼 USING 조건이나, ON 절 필수적으로 사용
- LEFT/RIGHT OUTER JOIN시 기준이되는 테이블이 조인 수행시 무조건 선행 테이블
# LEFT OUTER JOIN
iSQL> select * from tab1 left outer join tab2 on tab1.c1=tab2.c1;
C1 C2 C3 C1 C2 C3
--------------------------------------------------------
A AAB 111 A 10 AA
B AAC 123 B 10 AB
C ABA 222 C 10 AC
D ABB 233
E ABC 143
5 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
LEFT-OUTER-JOIN
SCAN ( TABLE: TAB1, FULL SCAN, ACCESS: 5, SELF_ID: 1 )
HASH ( ITEM_SIZE: 24, ITEM_COUNT: 3, BUCKET_COUNT: 1024, ACCESS: 5, SELF_ID: 3, REF_ID: 2 )
SCAN ( TABLE: TAB2, FULL SCAN, ACCESS: 5, SELF_ID: 2 )
------------------------------------------------------------
iSQL> select /*+ USE_NL(A,B) */ * from tab1 a left join tab2 b on a.c1=b.c1;
C1 C2 C3 C1 C2 C3
--------------------------------------------------------
A AAB 111 A 10 AA
B AAC 123 B 10 AB
C ABA 222 C 10 AC
D ABB 233
E ABC 143
5 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
LEFT-OUTER-JOIN
SCAN ( TABLE: TAB1 A, FULL SCAN, ACCESS: 5, SELF_ID: 1 )
SCAN ( TABLE: TAB2 B, FULL SCAN, ACCESS: 17, SELF_ID: 2 )
------------------------------------------------------------
<오라클 결과>
SQL> select * from tab1 left outer join tab2 on tab1.c1=tab2.c1;
C1 C2 C3 C1 C2 C3
-- ------ ---------- -- ---------- --------------------
A AAB 111 A 10 AA
B AAC 123 B 10 AB
C ABA 222 C 10 AC
E ABC 143
D ABB 233
# RIGTH OUTER JOIN
iSQL> select /*+ USE_NL(A,B) */ * from tab1 a right outer join tab2 b on a.c1=b.c1;
C1 C2 C3 C1 C2 C3
--------------------------------------------------------
A AAB 111 A 10 AA
B AAC 123 B 10 AB
C ABA 222 C 10 AC
3 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
LEFT-OUTER-JOIN <------------------ RIGHT OUTER JOIN 연산자가 아니다~!!!!
SCAN ( TABLE: TAB2 B, FULL SCAN, ACCESS: 3, SELF_ID: 2 )
SCAN ( TABLE: TAB1 A, FULL SCAN, ACCESS: 15, SELF_ID: 1 )
------------------------------------------------------------
# FULL OUTER JOIN
- LEFT OUTER JOIN과 RIGHT OUTER JOIN의 합집합
- UNION ALL이 아닌 UNION과 같은 기능이므로, 중복 데이터 삭제
< Full outer join 테스트 : left outer join 결과와 right outer join 결과를 union하여 비교>
SQL> UPDATE DEPT_TEMP SET DEPTNO = DEPTNO + 20;
4 rows updated.
SQL> SELECT * FROM DEPT_TEMP;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
30 ACCOUNTING NEW YORK
40 RnD DALLAS
50 MARKETING CHICAGO
60 OPERATIONS BOSTON
SQL> SELECT * FROM DEPT FULL OUTER 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 RnD DALLAS
50 MARKETING CHICAGO
60 OPERATIONS BOSTON
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
6 rows selected.
SQL> 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;
DEPTNO DNAME LOC DEPTNO DNAME LOC
---------- ---------------------------- -------------------------- ---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO 30 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON 40 RnD DALLAS
50 MARKETING CHICAGO
60 OPERATIONS BOSTON
6 rows selected.
- Plan상으로 봤을 때 left outer join과 동일하다.
iSQL> select * from tab1 a full outer join tab2 b on a.c1=b.c1;
C1 C2 C3 C1 C2 C3
--------------------------------------------------------
A AAB 111 A 10 AA
B AAC 123 B 10 AB
C ABA 222 C 10 AC
E ABC 143
D ABB 233
F 10 BC
6 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
FULL-OUTER-JOIN
SCAN ( TABLE: TAB1 A, FULL SCAN, ACCESS: 6, SELF_ID: 1 )
HASH ( ITEM_SIZE: 24, ITEM_COUNT: 4, BUCKET_COUNT: 1024, ACCESS: 6, SELF_ID: 3, REF_ID: 2 )
SCAN ( TABLE: TAB2 B, FULL SCAN, ACCESS: 6, SELF_ID: 2 )
------------------------------------------------------------
iSQL> select * from tab1 a left outer join tab2 b on a.c1=b.c1
union
select * from tab1 a right outer join tab2 b on a.c1=b.c1;
iSQL> /
C1 C2 C3 C1 C2 C3
--------------------------------------------------------
A AAB 111 A 10 AA
B AAC 123 B 10 AB
C ABA 222 C 10 AC
E ABC 143
D ABB 233
F 10 BC
6 rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
DISTINCT ( ITEM_SIZE: 88, ITEM_COUNT: 6, BUCKET_COUNT: 1024, ACCESS: 6, SELF_ID: 8, REF_ID: 5 )
VIEW ( ACCESS: 9, SELF_ID: 5 )
BAG-UNION
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
LEFT-OUTER-JOIN
SCAN ( TABLE: TAB1 A, FULL SCAN, ACCESS: 5, SELF_ID: 1 )
HASH ( ITEM_SIZE: 24, ITEM_COUNT: 4, BUCKET_COUNT: 1024, ACCESS: 5, SELF_ID: 6, REF_ID: 2 )
SCAN ( TABLE: TAB2 B, FULL SCAN, ACCESS: 6, SELF_ID: 2 )
PROJECT ( COLUMN_COUNT: 6, TUPLE_SIZE: 70 )
LEFT-OUTER-JOIN <------------------ RIGHT OUTER JOIN 연산자가 아니다~!!!!
SCAN ( TABLE: TAB2 B, FULL SCAN, ACCESS: 4, SELF_ID: 4 )
HASH ( ITEM_SIZE: 24, ITEM_COUNT: 5, BUCKET_COUNT: 1024, ACCESS: 4, SELF_ID: 7, REF_ID: 3 )
SCAN ( TABLE: TAB1 A, FULL SCAN, ACCESS: 6, SELF_ID: 3 )
------------------------------------------------------------
댓글 없음:
댓글 쓰기