혼자 공부하는 공간

[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #1 본문

자격증/SQLD

[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #1

god_z 2020. 8. 27. 16:24

<목차>

<< 표준 조인 (STANDARD JOIN) >>

* 대표적인 ANSI/ISO 표준 SQL의 기능

  • STANDARD JOIN 기능 추가 (CROSS JOIN, OUTER JOIN 등의 새로운 FROM 절 JOIN 기능들)

  • SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 SUBQUERY 기능들

  • ROLLUP, CUBE, GROUPING SETS 등의 새로운 리포팅 기능

  • WINDOW FUNCTION 같은 새로운 개념의 분석 기능들

 

 

1. 표준 조인의 개요

1-1. 일반 집합 연산자

E.F.CODD 일반 집합 연산자

  1. UNION 연산

    * UNION (합집합) 기능

    * 공통 교집합의 중복을 없이기 위한 정렬 작업이 사전에 발생하여 시스템에 부하 발생

    * UNION ALL 기능 : 공통 교집합 그대로 출력. UNION 기능처럼 정렬 작업이 불필요하므로 UNION 기능보다 시스템 부하가 훨씬 적다.

    * UNION, UNION ALL의 결과가 동일하다면 UNION ALL 사용을 권고함.

  2. INTERSECTION 연산

    * INTERSECTION (교집합) 기능

  3. DIFFERENCE 연산

    * EXCEPT(MINUS ; Oracle, 차집합) 기능

  4. PRODUCT 연산

    * CROSS JOIN 기능

    * CROSS PRODUCT(곱집합)으로 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합.

    * A집합의 데이터 M개, B집합의 데이터 N개라면 곱집합은 M*N건의 데이터가 발생.

    * CARTESIAN PRODUCT 라고도 표현.

 

1-2. 순수 관계 연산자

E.F.CODD 순수 관계 연산자

* 관계형 데이터베이스를 구현하기 위해 새롭게 만들어진 연산자.

  1. SELECT 연산

    * WHERE 절로 구현.

  2. PROJECT 연산

    * SELECT 절의 칼럼 선택 기능으로 구현.

  3. (NATURAL) JOIN 연산

    * 다양한 JOIN 기능으로 구현.

    * FROM 절의 NATURAL JOIN, INNER JOIN, OUTER JOIN, USING 조건절, ON 조건절 등으로 다양하게 발전.

  4. DIVIDE 연산

    * 현재 사용하지 않음.

* 정규화 과정의 경우 데이터의 정합성과 저장 공간의 절약을 위해 Entity를 최대한 분리하는 작업이다.

* 일반적으로 3차 정규형(3NF)이나 보이스코드 정규형(BCNF)까지 진행.

* 해당 정규화 후 하나의 주제에 관련 있는 Entity가 여러 개로 나누어지게 되고, 이 Entity들이 주로 테이블이 됨.

* 흩어진 테이블을 연결해서 원하는 데이터를 가져오는 작업이 바로 JOIN 이다.

 

2. FROM 절 JOIN 형태

* WHERE 절에서 사용하던 기존 JOIN 방식과 차이가 있다.

* ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태는 아래와 같다.

  1. INNER JOIN

    * WHERE 절에서 사용하던 JOIN의 DEFAULT 옵션으로 JOIN 조건에서 동일한 값이 있는 행만 반환.

    * CROSS JOIN/OUTER JOIN과 함께 사용 불가.

  2. NATURAL JOIN

    * INNER JOIN의 하위 개념으로 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행.

    * NATURAL INNER JOIN 이라고도 표현.

  3. USING 조건절

  4. ON 조건절

    * WHERE 절의 JOIN 조건을 FROM 절의 ON 조건절로 분리하여 표시함으로써 사용자가 이해하기 쉽도록 함.

    * NATURAL JOIN 처럼 JOIN 조건이 숨어 있지 않고, 명시적으로 JOIN 조건을 구분할 수 있음.

    * FROM 절에 테이블이 많이 사용될 경우 가독성이 떨어지는 단점이 있다.

  5. CROSS JOIN

  6. OUTER JOIN

 

3. INNER JOIN

* OUTER JOIN과 대비하여 내부 JOIN 이라고 하며, JOIN 조건에서 동일한 값이 있는 행만 반환한다.

* WHERE 절에서 사용하던 JOIN 기능을 FROM 절에서 정의하겠다는 표기법으로 USING 조건절/ON 조건절을 필수적으로 사용해야 한다.

 

* 예시 - 문제

   : 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
/* 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은 DEFAULT 옵션이므로 INNER 키워드는 생략 가능 */
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME 
FROM EMP JOIN DEPT 
ON EMP.DEPTNO = DEPT.DEPTNO;
 
/*
DEPTNO EMPNO ENAME   DNAME 
------ ----- ------- --------- 
20     7369  SMITH   RESEARCH 
30     7499  ALLEN   SALES     
30     7521  WARD    SALES 
20     7566  JONES   RESEARCH 
30     7654  MARTIN  SALES 
30     7698  BLAKE   SALES 
10     7782  CLARK   ACCOUNTING 
20     7788  SCOTT   RESEARCH 
10     7839  KING    ACCOUNTING 
30     7844  TURNER  SALES 
......
14개의 행이 선택되었다.
*/
 cs

 

 

4. NATURAL JOIN

* 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN 을 수행한다.

* NATURAL JOIN이 명시되면 USING 조건절, ON 조건절을 명시할 수 없다.

* 또한 WHERE 절에 JOIN 조건을 정의할 수 없다.

* SQL Server에서는 지원하지 않는다.

 

* 예시 - 문제

   : 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT DEPTNO, EMPNO, ENAME, DNAME 
FROM EMP NATURAL JOIN DEPT;
 
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME 
FROM EMP NATURAL JOIN DEPT; 
/* 
ERROR 발생.
1. EMP와 DEPT 테이블에서 동일한 이름의 칼럼들에 대해 EQUI JOIN을 
   수행하기 때문에 칼럼명 앞에 테이블 이름은 생략한다.
*/
 
/*
DEPTNO EMPNO  ENAME  DNAME 
------ ------ ------ ------ 
20     7369   SMITH  RESEARCH 
30     7499   ALLEN  SALES 
30     7521   WARD   SALES 
20     7566   JONES  RESEARCH 
30     7654   MARTIN SALES 
30     7698   BLAKE  SALES 
10     7782   CLARK  ACCOUNTING 
.....
14개의 행이 선택되었다.
*/
 cs

---> JOIN이 되는 칼럼 이름과 데이터 타입이 반드시 동일해야 한다.

---> 만약, EMP와 DEPT 테이블 사이에서 동일한 칼럼이 2개 이상이라면 위의 INNER JOIN 예시의 결과와 다른 결과를 출력한다.

 

* 예시 (와일드 카드 '*')

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
/* NATURAL JOIN */
SELECT * 
FROM EMP NATURAL JOIN DEPT;
 
/*
DEPTNO EMPNO ENAME  JOB       MGR  HIREDATE    SAL  COMM DNAME      LOC 
----- ----- ------- --------- ---- ----------- ---- ---- ---------- ------ 
20    7369   SMITH  CLERK     7902 1980-12-17  800       RESEARCH   DALLAS 
30    7499   ALLEN  SALESMAN  7698 1981-02-20  1600 300  SALES      CHICAGO 
30    7521   WARD   SALESMAN  7698 1981-02-22  1250 500  SALES      CHICAGO 
20    7566   JONES  MANAGER   7839 1981-04-02  2975      RESEARCH   DALLAS 
30    7654   MARTIN SALESMAN  7698 1981-09-28  1250 1400 SALES      CHICAGO 
30    7698   BLAKE  MANAGER   7839 1981-05-01  2850      SALES      CHICAGO 
10    7782   CLARK  MANAGER   7839 1981-06-09  2450      ACCOUNTING NEW YORK 
20    7788   SCOTT  ANALYST   7566 1987-07-13  3000      RESEARCH   DALLAS 
..... 
14개의 행이 선택되었다.
*/
 
 
/* INNER JOIN */
SELECT * 
FROM EMP INNER JOIN DEPT 
ON EMP.DEPTNO = DEPT.DEPTNO;
/*
EMPNO ENAME  JOB      MGR   HIREDATE   SAL  COMM DEPTNO DEPTNO DNAME      LOC 
----- ------ -------- ----- ---------- ---- ---- ------ ------ ---------- ----- 
7369  SMITH  CLERK    7902  1980-12-17 800       20     20     RESEARCH   DALLAS 
7499  ALLEN  SALESMAN 7698  1981-02-20 1600 300  30     30     SALES      CHICAGO 
7521  WARD   SALESMAN 7698  1981-02-22 1250 500  30     30     SALES      CHICAGO 
7566  JONES  MANAGER  7839  1981-04-02 2975      20     20     RESEARCH   DALLAS 
7654  MARTIN SALESMAN 7698  1981-09-28 1250 1400 30     30     SALES      CHICAGO 
7698  BLAKE  MANAGER  7839  1981-05-01 2850      30     30     SALES      CHICAGO 
7782  CLARK  MANAGER  7839  1981-06-09 2450      10     10     ACCOUNTING NEW YORK 
.....
14개의 행이 선택되었다. 
*/
 cs

* NATURAL JOIN

   : JOIN의 기준이 되는 칼럼들이 다른 칼럼보다 먼저 출력된다. (DEPTNO 칼럼)

   : JOIN의 기준이 되는 칼럼들을 하나로 처리한다. (DEPTNO를 두번이 아니라 하나로 출력)

* INNER JOIN

   : 첫 번째 테이블, 두 번째 테이블의 칼럼 순서대로 데이터가 출력된다.

   : JOIN의 기준이 되는 칼럼들을 별개로 처리한다. (DEPTNO가 두 번 출력)

 

5. USING 조건절

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

* SQL Server에서는 지원하지 않는다.

 

* 예시 - 문제

   : 세 개의 칼럼명이 모두 같은 DEPT와 DEPT_TEMP 테이블을 DEPTNO 칼럼을 이용한 INNER JOIN의 USING 조건절로 수행.

---> DEPTNO 테이블은 DEPT 테이블을 복사한 후, DEPTNO가 20인 행의 DNAME을 'R&D'로, 30인 행의 DNAME을 'MARKETING'으로 UPDATE한 테이블이다.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT * 
FROM DEPT JOIN DEPT_TEMP 
USING (DEPTNO);
 
/*
DEPTNO DNAME      LOC       DNAME      LOC 
------ ---------- --------- ---------- --------- 
10     ACCOUNTING NEW YORK  ACCOUNTING NEW YORK 
20     RESEARCH   DALLAS    R&D        DALLAS 
30     SALES      CHICAGO   MARKETING  CHICAGO 
40     OPERATIONS BOSTON    OPERATIONS BOSTON 
4개의 행이 선택되었다.
*/
 
 cs

---> '*' 와일드 카드처럼 별도의 칼럼 순서를 지정하지 않으면 USING 조건절의 기준이 되는 칼럼이 다른 칼럼보다 먼저 출력된다. (결과를 보면 DEPTNO 칼럼이 제일 먼저 출력됨.)

---> USING JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리한다. (결과를 보면 DEPTNO 칼럼은 하나만 출력됨.)

 

* 예시

   : USING 조건절을 이용한 EQUI JOIN에서도 NATURAL JOIN과 마찬가지로 JOIN 조건에 해당하는 칼럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙힐 수 없다. (DEPT.DEPTNO 사용 불가능)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/* 잘못된 사례 */ 
SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, DEPT_TEMP.DNAME, DEPT_TEMP.LOC 
FROM DEPT JOIN DEPT_TEMP USING (DEPTNO); 
 
/* 바른 사례 */ 
SELECT DEPTNO, DEPT.DNAME, DEPT.LOC, DEPT_TEMP.DNAME, DEPT_TEMP.LOC 
FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);
 
/*
DEPTNO DNAME      LOC       DNAME      LOC 
------ ---------- --------- ---------- --------- 
10     ACCOUNTING NEW YORK  ACCOUNTING NEW YORK 
20     RESEARCH   DALLAS    R&D        DALLAS 
30     SALES      CHICAGO   MARKETING  CHICAGO 
40     OPERATIONS BOSTON    OPERATIONS BOSTON 
4개의 행이 선택되었다.
*/
 
 cs

 

 

* 예시 - 문제

   : 세 개의 칼럼명이 모두 같은 DEPT와 DEPT_TEMP 테이블을 DNAME과 DEPTNO 2개 칼럼을 이용한 INNER JOIN의 USING 조건절로 수행.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
SELECT * 
FROM DEPT JOIN DEPT_TEMP USING (DEPTNO, DNAME);
 
/*
DEPTNO DNAME      LOC       LOC
------ ---------- --------- ---------
10     ACCOUNTING NEW YORK  NEW YORK
40     OPERATIONS BOSTON    BOSTON
2개의 행이 선택되었다.
*/
 
 cs

---> DEPTNO가 20, 30인 행의 DNAME이 변경되었기 때문에 결과에서 제외되었다.

---> USING 조건절에 사용된 JOIN 조건에 해당하는 DEPTNO, DNAME 먼저 출력되고 그 후 JOIN 조건에 해당되지 않는 LOC가 2개의 칼럼으로 출력.

 

6. ON 조건절

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

 

* 예시 - 문제

   : 사원 테이블과 부서 테이블에서 사원 번호와 사원 이름, 소속부서 코드, 소속부서 이름을 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);
 
/*
EMPNO ENAME   DEPTNO DNAME 
----- ------- ------ ----------- 
7369  SMITH   20     RESEARCH 
7499  ALLEN   30     SALES 
7521  WARD    30     SALES 
7566  JONES   20     RESEARCH 
7654  MARTIN  30     SALES 
7698  BLAKE   30     SALES 
7782  CLARK   10     ACCOUNTING 
....
14개의 행이 선택되었다.
*/
 
 cs

---> NATURAL JOIN의 JOIN 조건은 기본적으로 같은 칼럼명을 가진 칼럼들에 대한 동등(EQUI) 조건이지만, 임의의 JOIN 조건을 지정하거나 이름이 다른 칼럼을 JOIN 조건으로 사용하기 위해서는 ON 조건절을 사용한다.

---> USING 조건절의 JOIN 조건에 해당하는 칼럼들은 ALIAS나 테이블명과 같은 접두사를 사용하면 에러가 발생하지만, ON 조건절의 JOIN 조건에 해당하는 칼럼들은 ALIAS나 테이블명과 같은 접두사를 사용해 SELECT 절에 사용되는 칼럼을 논리적으로 명확하게 지정해주어야 한다. (DEPTNO가 아닌 E.DEPTNO or D.DEPTNO)

---> ON 조건절은 WHERE 절의 JOIN 조건과 같은 기능을 하면서도 명시적으로 JOIN의 조건을 구분할 수 있다. 

      (ON 키워드를 통해 일반 조건문인지 JOIN의 조건문인지 한눈에 알 수 있다.)

 

* 주의사항 : INNER JOIN에서는 ON과 WHERE이 같지만, LEFT/RIGHT/FULL OUTER JOIN에서는 ON과 WHERE 절을 사용했을 때 각각의 결과가 다르다.

* 예시

<T1>

ID SERIAL
1000 A990
1001 A520
1002 A999
1003 A808

 

<T2>

SERIAL PRICE
A990 50000
A999 75000
A478 49000
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT T1.ID, T1.SERIAL, T2.PRICE
FROM T1 LEFT JOIN T2
ON T1.SERIAL = T2.SERIAL
WHERE T2.PRICE BETWEEN (45000, 55000);
 
/*
ID    SERIAL PRICE
----- ------ -----
1000  A990   50000
*/
 
 
 
SELECT T1.ID, T1.SERIAL, T2.PRICE
FROM T1 LEFT JOIN T2
ON T1.SERIAL = T2.SERIAL
AND T2.PRICE BETWEEN (45000, 55000);
 
/*
ID    SERIAL PRICE
----- ------ -----
1000  A990   50000
1001  A520   NULL
1002  A999   NULL
1003  A808   NULL
*/
 
 cs

---> [ON 절 사용시]

JOIN 전에 필터링 되기 때문에 PRICE가 45000 이상 55000 이하에 대한 T2의 ROW1과 ROW3에 대해서만 LEFT OUTER JOIN을 수행한다.

 

---> [WHERE 절 사용시]

JOIN이 끝난 후에 필터링 되기 때문에 조인이 완료된 후 PRICE가 45000 이상 55000 이하에 만족하는 행만 보여준다. (1건)

 

 

6-1. WHERE 절과의 혼용

* ON 조건절과 WHERE 조건절은 충돌 없이 사용할 수 있다.

 

* 예시 - 문제

   : 부서코드가 30인 부서의 소속 사원 이름 및 소속 부서 코드, 부서 코드, 부서 이름을 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT E.ENAME, E.DEPTNO, D.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE E.DEPTNO = 30;
/* WHERE 절에 있는 DEPTNO는 E나 D나 어디에서 가져와도 무관 */
 
/*
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개의 행이 선택되었다.
*/
 
 cs

 

6-2. ON 조건절 + 데이터 검증 조건 추가

* ON 조건절에 JOIN 조건 외에도 데이터 검색 조건을 추가할 수 있다.

* 단순 검색 조건 목적인 경우는 WHERE 절을 사용하는 것을 권고.

* OUTER JOIN에서 JOIN의 대상을 제한하기 위한 목적으로 사용되는 경우는 반드시 ON 조건절에 표기한다.

 

* 예시 - 문제

   : 매니저의 사원번호가 7698번인 사원들의 이름 및 소속 부서 코드, 소속 부서 이름을 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO AND E.MGR = 7698);
 
/* 단순 검색 조건이 목적인 경우 WHERE 절로 사용할 것을 권고 */
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 
5개의 행이 선택되었다.
*/
 
 cs

 

6-3. ON 조건절 예제

* 예시 - 문제

   : 팀과 스타디움 테이블을 STADIUM_ID로 JOIN하여 팀 이름, 스타디움 ID, 스타디움 이름을 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
/* ON 조건절 */
SELECT T.TEAM_NAME, S.STADIUM_ID, S.STADIUM_NAME
FROM TEAM T JOIN STADIUM S ON (T.STADIUM_ID = S.STADIUM_ID)
ORDER BY S.STADIUM_ID;
 
/* 두 테이블간의 칼럼명이 같기 때문에 USING 조건절로도 표현 가능 */
/* JOIN 조건에 해당되는 칼럼은 ALIAS, 테이블명의 접두사 사용 불가능 */
SELECT T.TEAM_NAME, STADIUM_ID, S.STADIUM_NAME
FROM TEAM T JOIN STADIUM S USING (STADIUM_ID)
ORDER BY STADIUM_ID;
 
/* WHERE 절에서 JOIN해서 표현 가능 */
SELECT T.TEAM_NAME, S.STADIUM_ID, S.STADIUM_NAME 
FROM TEAM T, STADIUM S
WHERE T.STADIUM_ID = S.STADIUM_ID 
ORDER BY S.STADIUM_ID
 
/*
TEAM_NAME       STADIUM_ID STADIUM_NAME 
--------------- ---------- ------------- 
광주상무         A02        광주월드컵경기장 
강원FC           A03        강릉종합경기장 
제주유나이티드FC  A04        제주월드컵경기장 
대구FC           A05        대구월드컵경기장 
유나이티드       B01        인천월드컵경기장 
일화천마         B02        성남종합운동장 
삼성블루윙즈     B04        수원월드컵경기장 
FC서울          B05        서울월드컵경기장 
아이파크         C02        부산아시아드경기장 
울산현대         C04        울산문수경기장 
경남FC          C05         창원종합운동장 
스틸러스        C06         포항스틸야드 
드래곤즈        D01         광양전용경기장 
시티즌          D02         대전월드컵경기장 
15개의 행이 선택되었다.
*/
 
 cs

 

* 예시 - 문제

   : 팀과 스타디움 테이블을 TEAM_ID로 JOIN하여 팀 이름, 팀 ID, 스타디움 이름을 출력하라. 스타디움에는 TEAM_ID가 HOMETEAM_ID라는 칼럼으로 표기되어 있음.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
/* ON 조건절 1 */
SELECT T.TEAM_NAME, T.TEAM_ID, S.STADIUM_ID
FROM TEAM T JOIN STADIUM S ON (T.TEAM_ID = S.HOMETEAM_ID)
ORDER BY T.TEAM_ID;
/* SELECT 절의 T.TEAM_ID 대신 S.HOMETEAM_ID도 바꿔도 무관 */
 
 
/* ON 조건절 2 */
/* 출력하는 칼럼들의 이름이 모두 다르기 때문에 테이블명이나 ALIAS 접두사 사용안해도 무관 */
SELECT TEAM_NAME, TEAM_ID, STADIUM_NAME 
FROM TEAM JOIN STADIUM ON TEAM.TEAM_ID = STADIUM.HOMETEAM_ID 
ORDER BY TEAM_ID;
 
 
/* USING 조건절 */
/* 두 테이블간의 칼럼명이 달라서 USING 조건절 사용 불가능 */
 
 
/* WHERE 절에서 JOIN해서 표현 가능 */
SELECT T.TEAM_NAME, T.TEAM_ID, S.STADIUM_NAME 
FROM TEAM T, STADIUM S
WHERE T.TEAM_ID = S.HOMETEAM_ID 
ORDER BY T.TEAM_ID;
 
/* 
- TEAM_ID 와 HOMETEAM_ID는 두 테이블이 각각 사용하는 칼럼명이기 때문에 
  테이블명 접두사를 쓰지 않아도 무관하다.
*/
 
/*
TEAM_NAME       TEAM_ID STADIUM_NAME 
--------------- ------- ------------- 
울산현대         K01     울산문수경기장 
삼성블루윙즈     K02     수원월드컵경기장 
스틸러스         K03     포항스틸야드 
유나이티드       K04     인천월드컵경기장 
현대모터스       K05     전주월드컵경기장 
아이파크         K06     부산아시아드경기장 
드래곤즈         K07     광양전용경기장 
일화천마         K08     성남종합운동장 
FC서울          K09     서울월드컵경기장 
시티즌          K10     대전월드컵경기장 
경남FC          K11     창원종합운동장 
광주상무        K12     광주월드컵경기장 
강원FC          K13     강릉종합경기장 
제주유나이티드FC K14     제주월드컵경기장 
15개의 행이 선택되었다.
*/
 
 cs

 

6-4. 다중 테이블 JOIN

* JOIN 대상 테이블이 3개 이상인 경우를 알아보자.

 

* 예시 - 문제

   : 사원과 DEPT 테이블의 소속 부서명, DEPT_TEMP 테이블의 바뀐 부서명 정보를 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/* ON 조건절 1 */
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);
/* DEPT_TEMP 테이블과 JOIN 할 때 ON 조건절에서 E.DEPTNO 또는 D.DEPTNO 중 어떤걸 써도 무관 */
 
 
/* WHERE 절에서 JOIN해서 표현 가능 */
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 
------ ------ ---------- ----------- 
7369   20     RESEARCH   R&D 
7499   30     SALES      MARKETING 
7521   30     SALES      MARKETING 
7566   20     RESEARCH   R&D 
7654   30     SALES      MARKETING 
7698   30     SALES      MARKETING 
7782   10     ACCOUNTING ACCOUNTING 
7788   20     RESEARCH   R&D 
7839   10     ACCOUNTING ACCOUNTING 
7844   30     SALES      MARKETING 
7876   20     RESEARCH   R&D 
7900   30     SALES      MARKETING 
7902   20     RESEARCH   R&D 
7934   10     ACCOUNTING ACCOUNTING 
14개의 행이 선택되었다.
*/
 
 cs

 

* 예시 - 문제

   : 포지션이 'GK'인 선수들의 선수별 연고지명, 팀명, 구장명을 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
/* ON 조건절 1 */
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' 
ORDER BY 선수명;
 
 
/* WHERE 절에서 JOIN해서 표현 가능 */
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' 
ORDER BY 선수명;
 
 
/*
선수명  포지션 연고지명 팀명        구장명 
------ ------ ------- ----------- ---------- 
강성일  GK     대전    시티즌       대전월드컵경기장 
권정혁  GK     울산    울산현대     울산문수경기장 
권찬수  GK     성남    일화천마     성남종합운동장 
김대희  GK     포항    스틸러스     포항스틸야드 
김승준  GK     대전    시티즌       대전월드컵경기장 
김용발  GK     전북    현대모터스   전주월드컵경기장 
김운재  GK     수원    삼성블루윙즈 수원월드컵경기장 
김정래  GK     전남    드래곤즈     광양전용경기장 
김준호  GK     포항    스틸러스     포항스틸야드 
김창민  GK     전북    현대모터스   전주월드컵경기장 
.......
43개의 행이 선택되었다.
*/
 
 cs

 

7. CROSS JOIN

* E.F.CODD 박사가 언급한 일반 집합 연산자의 PRODUCT의 개념으로 테이블 간의 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다.

* 두 개의 테이블에 대한 CARTESIAN PRODUCT (=CROSS PRODUCT) 이라고도 표현한다.

* 결과는 A집합의 데이터 건수 M개, B집합의 데이터 건수 N개일 때, M*N 건의 데이터 조합이 발생한다.

 

* 예시

   : EMP 테이블과 DEPT 테이블을 CROSS JOIN 하라. (EMP : 데이터 14건, DEPT : 데이터 4건, 총 56건)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT ENAME, DNAME 
FROM EMP CROSS JOIN DEPT 
ORDER BY ENAME;
 
/*
ENAME DNAME 
----- ---------- 
ADAMS SALES 
ADAMS RESEARCH 
ADAMS OPERATIONS 
ADAMS ACCOUNTING 
ALLEN OPERATIONS 
ALLEN RESEARCH 
ALLEN ACCOUNTING 
ALLEN SALES 
BLAKE SALES 
BLAKE OPERATIONS 
BLAKE RESEARCH 
BLAKE ACCOUNTING 
CLARK SALES 
CLARK RESEARCH 
CLARK OPERATIONS 
CLARK ACCOUNTING 
......
56개의 행이 선택되었다.
*/
 cs

 

* 예시

   : NATURAL JOIN은 WHERE 절에서 JOIN 조건을 추가할 수 없지만, CROSS JOIN은 WHERE 절에 JOIN 조건을 추가할 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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;
 
/*
ENAME   DNAME 
------- --------- 
SMITH   RESEARCH 
ALLEN   SALES 
WARD    SALES 
JONES   RESEARCH 
MARTIN  SALES 
BLAKE   SALES 
CLARK   ACCOUNTING 
SCOTT   RESEARCH 
KING    ACCOUNTING 
TURNER  SALES 
ADAMS   RESEARCH 
JAMES   SALES 
FORD    RESEARCH 
MILLER  ACCOUNTING 
14개의 행이 선택되었다
*/
 cs

---> 결과를 보면 CROSS JOIN에서 WHERE 절에 JOIN 조건을 추가했을 때, INNER JOIN과 같은 결과가 나온다. CROSS JOIN을 사용하는 의미가 없어지므로 CROSS JOIN에서 WHERE 절에 JOIN 조건을 추가하는 것을 권고하지 않는다.

 

8. OUTER JOIN

* INNER JOIN과 대비하여 OUTER JOIN이라고 불린다.

* JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용할 수 있다.

OUTER JOIN 설명

* TAB1 테이블이 TAB2 테이블을 JOIN하는 상황에서 TAB2 테이블의 JOIN 데이터가 있는 경우는 그 데이터를 함께 출력하고, 없는 경우에도 TAB1 테이블의 데이터를 표시하고 싶을 때 사용한다.

* FROM 절에 JOIN 조건을 정의하므로 ON 조건절/USING 조건절을 필수적으로 사용해야 한다.

* LEFR/RIGHT OUTER JOIN의 차이는 기준이 되는 테이블이 JOIN 수행 시 무조건 드라이빙 테이블이 된다.

---> INNER JOIN은 JOIN 되는 순서가 바뀌어도 결과에 영향을 미치지 않지만 OUTER JOIN은 JOIN 순서가 결과에 영향을 미친다.

 

8-1. LEFT OUTER JOIN

* JOIN 수행 시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어 온다.

* 나중 표기된 테이블의 JOIN 칼럼에서 같은 값이 없는 경우에는 NULL 값으로 채운다.

* LEFT OUTER JOIN (OUTER 생략가능) 키워드 사용.

 

* 예시 - 문제

   : STADIUM에 등록된 운동장 중에는 홈팀이 없는 경기장도 있다. STADUIM과 TEAM 테이블을 JOIN 하되 홈팀이 없는 경기장의 정보도 함께 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT S.STADIUM_NAME, S.STADIUM_ID, S.SEAT_COUNT, S.HOMETEAM_ID, T.TEAM_NAME 
FROM STADIUM S LEFT OUTER JOIN TEAM T ON S.HOMETEAM_ID = T.TEAM_ID 
ORDER BY S.HOMETEAM_ID; 
 
/*
STADIUM_NAME     STADIUM_ID SEAT_COUNT HOMETEAM_ID TEAM_NAME 
---------------- --------- ---------- ----------- ---------- 
울산문수경기장     C04       46102      K01         울산현대 
수원월드컵경기장   B04       50000      K02         삼성블루윙즈 
포항스틸야드       C06       25000      K03         스틸러스 
인천월드컵경기장   B01       35000      K04         유나이티드 
전주월드컵경기장   D03       28000      K05         현대모터스 
부산아시아드경기장 C02       30000      K06         아이파크 
광양전용경기장     D01       20009      K07         드래곤즈 
성남종합운동장     B02       27000      K08         일화천마 
....
안양경기장        F05        20000 
마산경기장        F04        20000 
일산경기장        F03        20000 
부산시민경기장     F02       30000 
대구시민경기장     F01       30000
.......
20개의 행이 선택되었다.
*/
 cs

---> NULL이 가장 큰 값으로 처리되므로 HOMETEAM_ID 기준 오름차순 정렬했을 때, NULL 값이 가장 뒤에 출력된다.

---> INNER JOIN 이었다면 안양/마산/일산/부산시민/대구시민 경기장 데이터(5건)가 출력되지 않는다.

 

8-2. RIGHT OUTER JOIN

* LEFT JOIN과 반대로 우측 테이블이 기준이 되어 결과를 출력한다.

* RIGHT OUTER JOIN (OUTER 생략가능) 키워드 사용.

 

* 예시 - 문제

   : DEPT 테이블에 등록된 부서 중에는 사원이 없는 부서도 있다. DEPT와 EMP를 JOIN하되 사원이 없는 부서 정보도 함께 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT E.ENAME, E.DEPTNO, D.DNAME, D.LOC
FROM EMP E RIGHT OUTER JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);
 
/*
ENAME  DEPTNO DNAME      LOC 
------ ------ ---------- -------- 
CLARK  10     ACCOUNTING NEW YORK 
KING   10     ACCOUNTING NEW YORK 
MILLER 10     ACCOUNTING NEW YORK 
JONES  20     RESEARCH   DALLAS 
FORD   20     RESEARCH   DALLAS 
ADAMS  20     RESEARCH   DALLAS 
SMITH  20     RESEARCH   DALLAS 
SCOTT  20     RESEARCH   DALLAS 
WARD   30     SALES      CHICAGO 
TURNER 30     SALES      CHICAGO 
ALLEN  30     SALES      CHICAGO 
JAMES  30     SALES      CHICAGO 
BLAKE  30     SALES      CHICAGO 
MARTIN 30     SALES      CHICAGO 
       40     OPERATIONS BOSTON 
15개의 행이 선택되었다.
*/
 cs

---> INNER JOIN 이었다면 DEPTNO가 40인 OEPRATION 부서(1건)의 데이터는 출력되지 않는다.

 

8-3. FULL OUTER JOIN

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

* LEFT JOIN과 RIGHT JOIN의 결과를 합집합(Union != Union All) 시킨 결과와 동일하다.

---> 중복되는 데이터는 삭제되어 출력되기 때문에 Union All이 아닌 Union

* FULL OUTER JOIN (OUTER 생략가능) 키워드 사용.

 

* 예시

   : DEPT_TEMP 수정, DEPT_TEMP 테이블의 새로운 DEPTNO 데이터는 DEPT 테이블의 DEPTNO와 2건은 동일하고 2건은 새로운 DEPTNO가 생성됨.

1
2
3
4
5
6
7
8
9
10
11
12
UPDATE DEPT_TEMP SET DEPTNO = DEPTNO + 20; 
SELECT * FROM DEPT_TEMP;
 
/*
DEPTNO(전) DEPTNO(후) DNAME      LOC 
---------- ---------- ---------- ---------- 
10         30         ACCOUNTING NEW YORK 
20         40         R&D        DALLAS 
30         50         MARKETING  CHICAGO 
40         60         OPERATIONS BOSTON 
4개의 행이 선택되었다.
*/
 cs

 

* 예시 - 문제

   : DEPTNO 기준으로 DEPT와 DEPT_TEMP 데이터를 FULL OUTER JOIN으로 출력하라.

 

* 예시 - 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT * 
FROM DEPT FULL OUTER JOIN DEPT_TEMP ON DEPT.DEPTNO = DEPT_TEMP.DEPTNO;
 
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     R&D        DALLAS 
50     MARKETING  CHICAGO 
60     OPERATIONS BOSTON 
6개의 행이 선택되었다.
*/
 cs

---> INNER JOIN 이었다면 DEPTNO가 30, 40인 데이터 2건만 출력된다.

---> LEFT JOIN의 결과인 DEPTNO = 10, 20, 30, 40인 데이터와 RIGHT JOIN의 결과인 DEPTNO = 30, 40, 50, 60인 데이터가 합집합처리되어 6건이 출력되었다.

 

9. INNER JOIN VS OUTER JOIN VS CROSS JOIN

각 JOIN 문장 비교

* INNER JOIN 결과

   : TAB1, TAB2 테이블의 키 값이 같은 경우 (B-B, C-C)인 2건이 출력.

 

* LEFT OUTER JOIN 결과

   : 기준은 TAB1 테이블이므로 (B-B, C-C, D-NULL, E-NULL)인 4건이 출력.

 

* RIGHT OUTER JOIN 결과

   : 기준은 TAB2 테이블이므로 (NULL-A, B-B, C-C)인 3건이 출력.

 

* FULL OUTER JOIN 결과

   : LEFT OUTER JOIN 과 RIGHT OUTET JOIN의 합집합이므로 (NULL-A, B-B, C-C, D-NULL, E-NULL)인 5건이 출력.

 

* CROSS JOIN(CARTESIAN PRODUCT) 결과

   : 양 쪽 테이블의 데이터를 모두 조합한 경우의 수가 모두 도출된다. (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건이 출력.

 

 

 

출처

http://www.dbguide.net/db.db?cmd=view&boardUid=148198&boardConfigUid=9&categoryUid=216&boardIdx=135&boardStep=1

 

데이터 전문가 지식포털 DBGuide.net

표준 조인 집합 연산자 계층형 질의와 셀프 조인 서브쿼리 그룹 함수 윈도우 함수 DCL 절차형 SQL 1. STANDARD SQL 개요 1970년: Dr. E.F.Codd 관계형 DBMS(Relational DB) 논문 발표 1974년: IBM SQL 개발 1979년: Oracle

www.dbguide.net

질문은 댓글로 남겨주시면 되겠습니다. 감사합니다.

저작자표시

'자격증 > SQLD' 카테고리의 다른 글

[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #3  (0) 2020.08.28
[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #2  (0) 2020.08.27
[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #9  (0) 2020.08.23
[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #8  (0) 2020.08.22
[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 기본 #7  (0) 2020.08.22
'자격증/SQLD' Related Articles more
Comments