[SQL] JOIN

👀 유용한 정보

💡피드백 Tip

SDLC (Software Development Life Cycle) - 소프트웨어개발생명주기

📘 Join

Join 종류

Equi-join

Non Equi-Join (EQUAL = 이외의 연산자를 사용하여 JOIN)

Outer-Join

Ansi Outer-Join

Self-Join

카테시안 곱 (Cartesian Product)

📝과제

1. 아래의 3 SQL을 결과를 비교한 후 설명하시오

2. 아래 SQL의 실행결과 조회되는 Row 개수를 설명

3. JOIN을 사용하여 부서별 급여 지급 순위 계산

3-2. RANK, DENSE_RANK 함수를 사용하여 2)와 동일한 결과를 나타내는 SQL 작성

4. MAKE_ENV.SQL을 사용하여 실습 환경을 구성한 후 아래의 결과가 출력되는 JOIN 구문 작성

5. 부서번호, 이름, 급여, 급여비율(소수점이하 2자리) 출력하는 SQL을 카티션 곱을 응용하여 작성

6. 다음의 집합 연산자를 실행한 후 설명하시오

---

👀 유용한 정보

💡

불필요한 Outer Join을 하지 말자. Join을 처리하는 내부 알고리즘이 좌측 Outer Join과 Equi 조인이 다르게 처리되었기 때문에 정렬된 순서가 다르게 나타났다. 실행 계획 조회를 통해 어떤 알고리즘이 적용되었는지 확인할 수 있다.

💡

추후 프로젝트에서 쿼리 튜닝을 시도하고 그 튜닝 이후 실행 계획에서 어떠한 알고리즘으로 바뀌어 적용되었는지 등에 대한 내용을 적용하는 것이 좋다.

💡피드백 Tip

SDLC (Software Development Life Cycle) - 소프트웨어개발생명주기

[데이터베이스 측면에서의 SDLC]

0. 타당성 검토

1. 기획, 계획

2. (사용자 요구 사항) 분석 (What) → ERD 도출

3. 설계 (How) [40%]

4. 개발 [20%]

5. 테스트 [40%]

6. 이행 (Realease, Deploy)

7. 운영 및 유지보수

→ 추후 모델링 한 ERD를 A3용지에 그리는 것이 시험

---

📘 Join

여러 테이블에 흩어져 있는 데이터를 붙여서(Join 해서) 보는 것

💡

Display data from multiple tables (한 개 이상의 테이블로 부터 데이터를 읽어야 할때 ➔ 수평적 결합)

• 관계형 데이터베이스는 데이터 간 물리적 연결 없이 독립적으로 존재 하다가 데이터 간 연결이 필요할 때 내용에 의한 참조(Content Reference)를 한다. 실시간으로 테이블 간 관계 통해 수평적 결합 것이 JOIN.

• JOIN은 관계형 데이터베이스에서 필수적으로 필요한 가장 중요한 연산 중 하나.

필요성

• 관계형 데이터베이스는 테이블 독립성, 데이터의 최소 중복성, 데이터간의 함수적 종속성을 통해 각각의 데이터를 각각의 테이블로 나누어(분할) 저장하도록 설계.
  • * 최소 중복성 : 모델링에 중요!

• 조회를 원하는 데이터가 하나의 테이블에 저장되어 있는 경우 SELECT를 통해 조회 하지만, 조회를 원하는 데이터가 여러 테이블에 나누어 저장되어 있는 경우는 JOIN 연산으로 데이터 조회 한다.

• 사번,이름,급여,부서번호가 필요한 경우 ➔ SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP;

• 부서번호,부서명,부서위치가 필요한 경우 ➔ SELECT DEPTNO,DNAME,LOC FROM DEPT;

• 부서명,이름,급여 정보가 필요한 경우 ➔ SELECT DNAME, ENAME, SAL FROM DEPT , EMP WHERE DEPT.DEPTNO = EMP.DEPTNO ;

Join 종류

Equi-join

💡

Equi-Join (= INNER JOIN)

• JOIN에 사용되는 테이블의 컬럼간에 정확이 일치(EQUAL)하는 데이터를 RETURN

• EQUAL( = ) 연산자를 사용하여 JOIN

• 1~4 Oracle Join 구문, 5~6 ANSI/ISO-SQL

①

SELECT DNAME,ENAME,JOB,SAL FROM EMP, DEPT WHERE DEPTNO = DEPTNO;

➔ ORA-00918: 열의 정의가 애매합니다.. 이유는 ?

②

SELECT DNAME,ENAME,JOB,SAL FROM SCOTT.EMP, SCOTT.DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;

• OBJECT Full name 표기법 : [SCHEMA.]OBJECT_NAME EX) EMP ➔ SCOTT.EMP

• COLUMN Full name 표기법 : [TABLE_NAME.]COLUMN_NAME EX) EMPNO ➔ EMP.EMPNO

③

SELECT DNAME,ENAME,JOB,SAL FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO AND EMP.JOB IN ('MANAGER','CLERK')
ORDER BY DNAME;

• 조건절 처리 순서??
  • (a) JOIN 처리 (EMP.DEPTNO = DEPT.DEPTNO)
    • → 필터링 (EMP.JOB IN ('MANAGER','CLERK'))
  • (b) 필터링 → JOIN 처리

💡

필터링 이후 Join 처리를 한다. → 전체 일량이 줄어들기 때문에 옵티마이저가 필터링을 먼저 처리하고 Join을 가장 후 순위로 처리 한다.

④

SELECT D.DNAME,E.ENAME,E.JOB,E.SAL FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

• TABLE ALIAS의 사용
  • (a) 편의성
  • (b) 가독성(의미있는 이름사용)
    • EX) EMP E , EMP A
  • (c) Self Join시 필수 사용
  • (d) 동일 컬럼 명이 존재하는 경우

⑤ // ANSI-SQL , ON 조인조건

SELECT D.DNAME,E.ENAME,E.JOB,E.SAL FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

⑥

SELECT D.DNAME,E.ENAME,E.JOB,E.SAL FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO -- 조인 조건
WHERE E.DEPTNO IN (10,20) AND D.DNAME = 'RESEARCH' -- 필터링 조건

Non Equi-Join (EQUAL = 이외의 연산자를 사용하여 JOIN)

💡

Non Equi-Join

• EQUAL( = ) 이외의 연산자를 사용하여 JOIN

①

SELECT E.ENAME, E.JOB,E.SAL,S.GRADE FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL; -- 범위 연산자

②

SELECT DNAME,ENAME,JOB,SAL,GRADE
FROM EMP E, DEPT D, SALGRADE S // Join 순서는 누가 결정? , 기술 순서? KMA
WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;

• 3개 테이블 JOIN , 최소 JOIN조건: N(테이블개수) – 1

③

SELECT E.ENAME, E.JOB,E.SAL,E.GRADE FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND E.DEPTNO IN (10,30);
ORDER BY E.ENAME;

• 조건절 처리 순서???
  • (b) 필터 조건 → JOIN 조건

④

SELECT E.ENAME, E.JOB,E.SAL,S.GRADE FROM EMP E, SALGRADE S
WHERE E.SAL < S.LOSAL AND E.DEPTNO IN (10,30)
ORDER BY E.ENAME;

• 원하는 결과가 아닌 무의미(불필요)한 다량의 곱집합(Cartesian product) 결과가 발생한 이유는?

  → 잘못된 Join 조건을 사용

  • 리턴된 값을 봤을 때 결과 집합이 Cartesian Product의 형태로 나왔을 때 조인 조건을 다시한번 확인해보기!

Outer-Join

💡

Outer-Join

• JOIN 조건에 직접 만족되지 않는 정보도 조회 ( != Inner Join )

①

SELECT D.DNAME,E.ENAME,E.JOB,E.SAL
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY D.DNAME;

• 40번 부서(OPERATIONS)에 근무하는 직원이 없기 때문에 Equi Join에서는 40번 부서관련 정보 조회가 안된다. 14건

②

SELECT D.DNAME,E.ENAME,E.JOB,E.SAL 
FROM EMP E,DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
ORDER BY D.DNAME;

• 기준이되는 테이블(DEPT)과 조인되는 대상 테이블(EMP)의 조인 조건에 (+) 표시 ,직접 매핑되지 않는 컬럼에는 NULL , 15건 (OPERATIONS ?)

③

SELECT D.DNAME,E.ENAME,E.JOB,E.SAL
FROM EMP E,DEPT D
WHERE E.DEPTNO = D.DEPTNO(+)
ORDER BY D.DNAME;

• Equi-Join과 동일한 결과 출력, 불필요한 아웃터 조인은 비효율적인 자원 사용 유발 가능

④

SELECT D.DNAME, NVL(E.ENAME,'비상근 부서'), E.JOB, E.SAL
FROM EMP E,DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
ORDER BY D.DNAME;

• 직접 매핑되지 않는 컬럼에는 NULL

⑤

SELECT D.DNAME,E.ENAME,E.JOB,E.SAL
FROM EMP E,DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO(+)
ORDER BY D.DNAME;

• ORACLE SQL은 양방향 OUTER JOIN을 허용하지 않는다.

• ANSI-SQL 1999 에서는 양방향 OUTER JOIN을 허용

Ansi Outer-Join

💡

Ansi Outer-Join ① LEFT OUTER JOIN : 좌측 테이블 기준

SELECT E.DEPTNO,D.DNAME,E.ENAME FROM EMP E LEFT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
ORDER BY E.DEPTNO;

② RIGHT OUTER JOIN : 우측 테이블 기준

SELECT D.DNAME,E.ENAME FROM EMP E RIGHT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
ORDER BY E.DEPTNO;

• DEPT을 기준 테이블(Driving Table)하여 Join 연산 수행 , 40번 부서의 정보 표기 !!!!

③ FULL OUTER JOIN

SELECT D.DNAME,E.ENAME FROM EMP E FULL OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
ORDER BY E.DEPTNO;

• 양방향 아웃터 조인(Full Outer Join) 실행

[실습 이해] - SELECT LIST 를 *로 놓고 비교해보기.

Self-Join

💡

Self-Join (순환 참조 관계 : Recursive Relationship ) ①

SELECT E.ENAME||' ''S MANAGER IS '||M.ENAME
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO
ORDER BY M.ENAME;

• 같은 테이블 끼리 조인

• 테이블 개체내 참조 무결성 관계시 ex) empno vs mgr

• 테이블 Alias 필수 사용

==============================================

1. 위의 ① SQL에서는 회사 대표( job=‘PRESIDENT') 정보가 누락 되었다.

   → 대표는 manager가 없기 때문.

• SQL을 수정하여 누락된 정보가 조회 되도록 작성 : OUTER JOIN 사용!

• 매니저가 없는 경우 매니저의 이름은 NOBODY로 표기

SELECT E.ENAME||' ''S MANAGER IS '||NVL(M.ENAME, 'NOBODY')
FROM EMP E LEFT OUTER JOIN EMP M
ON E.MGR = M.EMPNO
ORDER BY M.ENAME;

• 기존 KING과 OCPOK은 아래와 같이 MGR 번호가 null이다

따라서, Self-join을 Equi Join했을 때에는 JOIN 조건에 의해 MGR이 없는 KING과 OCPOK은 누락되었었다.

이를 Employ 관점에서의 테이블을 LEFT JOIN 함으로써 누락됐었던 레코드를 포함시켜 JOIN 했고, NVL()함수를 통해 null 처리를 했다.

카테시안 곱 (Cartesian Product)

Join의 내부 주요 처리 알고리즘

• Nested Loop

• Sort-Merge

• Hash

💡

카티션곱 (CARTESIAN PRODUCT)

• Join시 두 테이블(집합)간에 곱집합 연산으로 유용하지 않은 대량의 데이터를 생성하는 현상
  • 데카르트의 곱집합
  • 발생원인 (a) Join 조건 생략시 (b) 잘못된 JOIN 조건
  • 용도 (a) 테스트용 샘플데이타 생성 (b) 곱집합 기능을 이용한 빠른 연산 응용

① CROSS JOIN (ANSI/ISO-SQL)

SELECT ENAME,JOB,DNAME FROM EMP, DEPT;
SELECT ENAME,JOB,DNAME FROM EMP CROSS JOIN DEPT; -- Ansi-SQL

• Join 조건 생략시 발생 , 데이터 건수 체크

→ Cartesian Product 발생!

② Join 조건 없이 Join

SELECT ENAME,JOB,DNAME FROM EMP, DEPT
WHERE EMP.SAL > 2000 and DEPT.DEPTNO IN (10,20);

• 필터링 조건(O) , Join 조건(X) , and 와 데이터 건수 체크

③ Join 조건 없이 Join

SELECT ENAME,JOB,DNAME FROM EMP , DEPT
WHERE EMP.SAL > 2000 or DEPT.DEPTNO IN (10,20);

• 필터링 조건(O) , Join 조건(X) , or 와 데이터 건수 체크

④

SELECT E.ENAME, E.JOB,E.SAL,S.GRADE FROM EMP E, SALGRADE S
WHERE E.SAL < S.LOSAL AND E.DEPTNO IN (10,30)
ORDER BY E.ENAME;

• 잘못된 Join 조건

---

📝과제

1. 위의 ① SQL에서는 회사 대표( job=‘PRESIDENT') 정보가 누락 되었다.

   → 대표는 manager가 없기 때문.

• SQL을 수정하여 누락된 정보가 조회 되도록 작성 : OUTER JOIN 사용!

• 매니저가 없는 경우 매니저의 이름은 NOBODY로 표기

SELECT E.ENAME||' ''S MANAGER IS '||NVL(M.ENAME, 'NOBODY')
FROM EMP E LEFT OUTER JOIN EMP M
ON E.MGR = M.EMPNO
ORDER BY M.ENAME;

• 기존 KING과 OCPOK은 아래와 같이 MGR 번호가 null이다

따라서, Self-join을 Equi Join했을 때에는 JOIN 조건에 의해 MGR이 없는 KING과 OCPOK은 누락되었었다.

이를 Employ 관점에서의 테이블을 LEFT JOIN 함으로써 누락됐었던 레코드를 포함시켜 JOIN 했고, NVL()함수를 통해 null 처리를 했다.

1. 아래의 3 SQL을 결과를 비교한 후 설명하시오

SELECT D.DNAME,E.ENAME,E.JOB,E.SAL FROM EMP E,DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO ORDER BY D.DNAME;
-- DEPT (오른쪽)을 기준으로 Outer 조인


SELECT D.DNAME,E.ENAME,E.JOB,E.SAL FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO AND E.SAL > 2000 ORDER BY D.DNAME; -- Outer Join 후 필터링
-- DEPT (오른쪽)을 기준으로 Oute 조인 이후, SAL이 2000이 넘는 것을 필터링


SELECT D.DNAME,E.ENAME,E.JOB,E.SAL FROM EMP E,DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO AND E.SAL(+) > 2000 ORDER BY D.DNAME; -- Outer Join
-- Outer Join은 기준이 되는 테이블에는 Join조건이 매칭되지 않더라도 null을 포함해 조인한다.
-- E.SAL(+)에도 Outer Join이 적용돼서, DEPT를 기준으로 SAL 값이 2000이 되지 않더라도 포함되었다.

2. 아래 SQL의 실행결과 조회되는 Row 개수를 설명

-- 2-1
SELECT E.ENAME||' ''S MANAGER IS '||M.ENAME FROM EMP E, EMP M
WHERE E.MGR(+) = M.EMPNO
ORDER BY M.ENAME;


-- 2-2
SELECT E.ENAME||' ''S MANAGER IS '||M.ENAME FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO(+)
ORDER BY M.ENAME;

💡

2-1 : M.EMPNO를 기준으로 하는 right outer join이다. 2-2 : E.MGR을 기준으로 하는 Left outer join이다.

3. JOIN을 사용하여 부서별 급여 지급 순위 계산

-- rank
select E.deptno, e.ename, e.job, e.sal,

(select count(*) + 1
from emp e2
where e2.deptno = e.deptno and e2.sal > e.sal) as 급여순위

from emp e
order by E.deptno, e.sal desc;

--------------------------------------------------------------------

-- dense rank
select E.deptno, e.ename, e.job, e.sal,

(select count(distinct e2.sal) + 1
from emp e2
where e2.deptno = e.deptno and e2.sal > e.sal) as 급여순위

from emp e
order by E.deptno, e.sal desc;

3-2. RANK, DENSE_RANK 함수를 사용하여 2)와 동일한 결과를 나타내는 SQL 작성

• 각 함수에 대한 설명 추가

💡

[RANK]

동일한 값을 가진 데이터들 중에서 순위를 계산하되, 중간에 빠진 순위가 있을 수 있습니다. 예를 들어, 1, 2, 2, 4, 5가 있을 때 RANK 함수를 적용하면 1, 2, 2, 4, 5가 됩니다.

[DENSE_RANK]

RANK 함수와 비슷하지만, 중간에 빠진 순위를 채우고 계속 순차적으로 순위를 매깁니다. 예를 들어, 1, 2, 2, 4, 5가 있을 때 DENSE_RANK 함수를 적용하면 1, 2, 2, 3, 4가 됩니다.

SELECT DEPTNO, ENAME, JOB, SAL,
       RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS dense_rank
FROM EMP
ORDER BY DEPTNO;

4. MAKE_ENV.SQL을 사용하여 실습 환경을 구성한 후 아래의 결과가 출력되는 JOIN 구문 작성

SYSTE	SYSTEM_NAME	FTP	TELNET	EMAIL
XXX	혜화DB	사용	미사용	미사용
YYY	강남DB	사용	사용	사용
ZZZ	영등포DB	미사용	미사용	미사용

sql 스크립트 파일을 만들어서 별도로 해당 파일을 실행. 과제 풀기

1) 터미널에서 @ or start 명령어 사용해서 sql script 파일 실행

2) sqldev에서 sqlscript 파일 실행 (메뉴에서 찾아서 실행)

Drop Table SYSTEM;

Create Table SYSTEM()

이런식으로 테이블 만들기 전에 미리 삭제를 해서 여러번 실행할 수 있도록 스크립트 수정한다.

SELECT S.SYSTEM_ID, S.SYSTEM_NAME,
       DECODE(SUM(DECODE(R.RESOURCE_NAME, 'FTP', 1, 0)), 1, '사용', '미사용') AS FTP,
       DECODE(SUM(DECODE(R.RESOURCE_NAME, 'TELNET', 1, 0)), 1, '사용', '미사용') AS TELNET,
       DECODE(SUM(DECODE(R.RESOURCE_NAME, 'EMAIL', 1, 0)), 1, '사용', '미사용') AS EMAIL
FROM SYSTEM S LEFT OUTER JOIN RESOURCE_USAGE R
ON S.SYSTEM_ID = R.SYSTEM_ID
GROUP BY S.SYSTEM_ID, SYSTEM_NAME
ORDER BY S.SYSTEM_ID;

5. 부서번호, 이름, 급여, 급여비율(소수점이하 2자리) 출력하는 SQL을 카티션 곱을 응용하여 작성

SELECT DEPTNO, ENAME, SAL, ROUND((SAL/SUM_SAL)*100, 2) || '%'
FROM EMP E1, (
		SELECT SUM(SAL) AS SUM_SAL FROM EMP
) E2;

6. 다음의 집합 연산자를 실행한 후 설명하시오

💡

① 합집합

select deptno from dept -- 10,20,30,40
union all
select deptno from emp;    -- 10,20,30

select deptno from dept
union
select deptno from emp;                    -- 정렬되는 이유? 실행계획 SORT(UNIQUE)

select ename,job,sal from emp where job = 'CLERK’          -- 직무가 CLERK인 집합
union                                              -- 컬럼 개수 & 데이터 타입 일치
select ename,job,sal from emp where sal > 2000      -- 2000 이상 급여 대상자 집합
order by job asc,sal desc;                       -- order by 대상 집합은? 위치는?

• 대상 집합은 UNION 완료 후 결과 집합.

② 교집합

select deptno from dept
intersect
select deptno from emp; -- 정렬되는 이유? 실행계획 SORT(UNIQUE)

③ 차집합

select deptno from dept
minus
select deptno from emp; -- 정렬되는 이유? 실행계획 SORT(UNIQUE)

• Union all을 제외한 Union, intersect, minus는 정렬이 되어 출력된다. 그 이유 확인

• 옵티마이저가 최적의 집합 연산 처리를 위해 정렬을 한다. (SORT, UNIQUE)

---

Uploaded by N2T