SQL 공부 일지 8일차 230823

## 공부정리

과목2 2장 SQL 활용 1편

1) 표준 조인

 

 

 

#1 표준 조인

1 STANDAR SQL (표준 SQL) 개요

 

① 표준 SQL의 기능

• STANDARD JOIN 기능 추가 (CROSS, OUTER JOIN 등 새로운 FROM 절 JOIN 기능들)
• SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 서브쿼리 기능들
• ROLLUP, CUBE, GROUPING SETS 등의 새로운 리포팅 기능
• WINDOW FUCTION 같은 새로운 개념의 분석 기능들

② 일반 집합 연산자 → 현재 SQL

• UNION 연산 -> UNION 기능 : 합집합
• INTERSECTION 연산 -> INTERSECT 기능 : 교집합
• DIFFERENCE 연산 -> EXCEPT 기능으로 (Oracle은 MINUS) : 차집합
• PRODUCT 연산 -> CROSS JOIN 기능 : 곱집합(생길 수 있는 모든 데이터 조합)

③ 순수 관계 연산자 -> 현재 SQL

SELECT 연산 -> WHERE 절 : 조건에 맞는 행 조회

PROJECT 연산 -> SELECT 절 : 조건에 맞는 칼럼 조회

(NATURAL) JOIN 연산 -> 다양한 JOIN 기능 : 여러 조인 존재

DIVIDE 연산은 현재 사용되지 않는다.

 

 

2 FROM 절의 JOIN 형태

- ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태

: INNER JOIN / NATURAL JOIN / USING 조건절 / ON 조건절 / CROSS JOIN / OUTER JOIN

 

- 기존 WHERE 절 그대로 사용 가능

- FROM 절에서 JOIN 조건을 명시적으로 정의 가능

 

 

3 INNER JOIN - 내부 JOIN

• JOIN 조건에서 동일한 값이 있는 행만 반환
• DEFAULT 옵션이므로 생략이 가능하지만, CROSS JOIN / OUTER JOIN 과는 같이 사용 X
• USING 조건절이나 ON 조건절을 필수적으로 사용
• 중복 테이블의 경우 별개의 칼럼으로 표시

SQL>>
"사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 출력하시오."

SELECT EMP.DEPTNO,EMPNO,ENAME,DNAME FROM EMP,DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

-> (같은 코드)

SELECT EMP.DEPTNO,EMPNO,ENAME,DNAME FROM EMP
INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPT.DEPTNO;

-> (다음도 같은 코드, INNER JOIN을 JOIN으로 써도 상관 없다. 디폴트 값이 INNER JOIN)

SELECT EMP.DEPTNO,EMPNO,ENAME,DNAME FROM EMP
JOIN DEPT IN EMP.DEPTNO = DEPT.DEPTNO;

 

 

4 NATURAL JOIN

두 테이블 간 동일한 이름을 갖는 모든 칼럼에 대해 EQUI JOIN을 수행

USING, ON, WHERE에서 JOIN을 정의 할 수 없다.

JOIN에 사용된 컬럼은 같은 데이터 타입이여야 함

ALIAS나 접두사를 붙일 수 없다.

 

SQL>>
"사원 번호와 사원이름, 소속부서 코드와 소속부서 이름을 출력하시오."

SELECT DEPNO,EMPNO,ENAME,DNAMEFROM EMP NATURAL JOIN DEPT;

 

 

5 USING 조건절

• FROM 절에 USING 조건절을 이용해서 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있다.
• SQL 에서는 지원하지 않는다.
• JOIN 칼럼에 대해서는 ALIAS나 테이블 이름과 같은 접두사를 붙일 수 없다.
• JOIN에 사용되는 칼럼은 1개만 표시한다.

Oracle>>
SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);

 

 

6 ON 조건절

• 칼럼명이 달라도 JOIN 사용가능
• WHERE 검색 조건은 충돌 없이 사용할 수 있다
• ON 조건절에서 사용된 괄호는 옵션사항이다
• ALIAS 및 테이블명과 같은 접두사를 반드시 사용

가. WHERE 결과의 혼용

SQL>>
"부서코드 30인 부서의 소속 사원 이름 및 소속 부서 코드, 부서 코드, 부서 이름을 추력하시오."

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 조건절 + 데이터 검증 조건 추가

SQL>>
"매니저 사원번호가 7698번인 사원들의 이름 및 소속 부서 코드, 부서 이름을 출력하시오."

SELECT E.ENAME,E.MGR,D.DEPTNO,D.DNAME FROM EMP E
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO AND E.MGR = 7698);

-> (같은 코드)

SELECT E.ENAME,E.MGR,D.DEPTNO,D.DNAME FROM EMP E
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) WHERE E.MGR = 7698;

 

다. ON 조건절 예제

SQL>>
"팀과 스타디움 테이블을 팀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;

 

라. 다중 테이블 JOIN

SQL>>
"사원과 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);

 

 

7 CROSS JOIN(= CARTESIAN PRODUCT / CROSS PRODUCT)

• JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합
• JOIN 할 때 적절한 JOIN 조건 칼럼이 없는 경우 사용
• 생길 수 있는 모든 데이터 조합을 출력
• 결과는 양쪽 집합의 M * N 건의 데이터 조합 발생

SQL>>
"사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾아본다."

SELECT ENAME,DNAME FROM EMP CROSS JOIN DEPT ORDER BY ENAME;

 

 

8 OUTER JOIN

• JOIN 조건에서 동일한 값이 없는 행도(NULL도) 출력
• USING 조건절이나 ON 조건절을 필수로 사용
• IN / ON 연산자 사용시 에러
• 표시가 누락된 칼럼이 있을 경우 OUTER JOIN 오류 발생, FULL OUTER JOIN 미지원
• FULL OUTER JOIN 미지원으로 인해 STANDART JOIN 주로 사용

이미지 출처 : https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=346

 

 

이미지 출처 : https://hongong.hanbit.co.kr/sql-%EA%B8%B0%EB%B3%B8-%EB%AC%B8%EB%B2%95-joininner-outer-cross-self-join/

 

 

① LEFT OUTER JOIN (↔RIGHT)

- 좌측 테이블에서 먼저 데이터를 읽은 후, 우측 테이블에서 JOIN 대상을 읽음

- 좌측 테이블 기준이며, OUTER 키워드는 생략 가능

 

SQL>>
"STADIUM에 등록된 운동장 중에는 홈팀이 없는 경기장도 있다.
STADIUM 과 TEAM을 JOIN 하되 홈팀이 없는 경기장의 정보도 같이 출력하도록 한다."

SELECT STADIUM_NAME,STADIUM.STADIUM_ID,SEAT_COUNT,HOMETEAM_ID,TEAM_NAME
FROM STADIUM LEFT OUTER JOIN TEAM ON STADIUM.HOMETEAM_ID = TEAM.TEAM_ID
ORDER BY HOMETEAM_ID;
-> (OUTER는 생략 가능)

 

* SELECT X.KEY1, Y.KEY2

FROM TAB1 X LEFT JOIN TAB2 Y

ON (X.KEY=Y.KEY)

 

② RIGHT OUTER JOIN

- LEFT OUTER JOIN과 반대로 우측 테이블이 기준이 되어 결과 생성

 

③ FULL OUTER JOIN

- 합집합 개념으로 LEFT와 RIGHT를 모두 읽어 온다

- 조인이 되는 모든 테이블의 데이터를 읽어 JOIN 함

 

SQL>>
UPDATE DEPT_TEMP SET DEPTNO=DEPTNO + 20;
SELECT * FROM DEPT_TEMP;

 

* SELECT X.KEY1, Y.KEY2

FROM TAB1 X FULL JOIN TAB2 Y

ON (X.KEY=Y.KEY)

 

 

9 INNER JOIN vs OUTER JOIN vs CROSS JOIN 비교

 

 

① INNER JOIN의 결과는 다음과 같다.

양쪽 테이블에 모두 존재하는 키 값이 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의 결과는 다음과 같다.

양쪽 테이블을 기준으로 키 값 조합이 NULL-A, B-B, C-C, D-NULL, E-NULL 인 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, D-B, D-C, E-A, E-B, E-C인 12건이 출력된다.