JOIN
정규화 (Normalization)
- 이상현상 (Anomaly) 발생을 피하기 위해 테이블을 분할하는 과정
- 데이터의 중복을 최소화하고, 데이터 무결성을 유지함
- 3NF (3차 정규형)이 학계와 실무에서 주로 사용됨
JOIN
- 여러 테이블을 연결하여 데이터의 통합 조회를 수행
- 실제 JOIN 연산은 두 개의 테이블에 대해서만 적용됨
- 일반적으로 PK (Primary Key)와 FK (Foreign Key)의 연관 관계에 의해 JOIN이 성립함
- 그 외에도 논리적인 값들의 연관성만으로도 JOIN이 성립 가능함
| JOIN 유형 | 설명 및 특징 |
|---|---|
| Equi Join | - 두 테이블 간의 동일한 값을 가진 컬럼을 기준으로 연결하는 JOIN - 일반적으로 = 연산자를 사용하여 JOIN |
| Non-Equi Join | - 두 테이블 간의 컬럼 값이 동일하지 않은 경우를 기준으로 연결하는 JOIN - =, >, <, >=, <= 등의 연산자를 사용할 수 있음 |
| 암시적 조인 | - WHERE 절에 조건을 사용하여 조인을 수행하는 방법- 조인 문법을 명확히 드러내지 않음 (구버전 SQL에서 주로 사용) |
| 명시적 조인 | - JOIN 키워드를 사용하여 조인을 수행하는 방법- INNER JOIN, LEFT JOIN, RIGHT JOIN 등 조인의 종류를 명확하게 표시 |
| Inner Join | - 두 테이블 간의 공통된 값이 있는 행만 반환 - ON 절을 통해 조건을 지정하여 JOIN 수행 |
| Outer Join | - 한 테이블의 모든 행과 다른 테이블의 일치하는 행을 반환 - 세 가지 유형이 있음: - LEFT OUTER JOIN: 왼쪽 테이블의 모든 행 반환 - RIGHT OUTER JOIN: 오른쪽 테이블의 모든 행 반환 - FULL OUTER JOIN: 양쪽 테이블의 모든 행 반환 |
| Cross Join | - 두 테이블 간의 모든 행의 조합을 반환하는 JOIN - 두 테이블의 각 행이 조합되어 결과로 나타남 (카테시안 곱) |
| Self Join | - 자기 자신과 조인 |
EQUI JOIN (동등 조인)
- 조인 조건으로 Equal (
=) 연산을 사용하여 두 테이블을 연결
기본 사용 예시
SELECT ENAME, DEPTNO, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO; -- ERROR!!!- 위 예시에서
DEPTNO가 중복된 컬럼이므로, 테이블 이름을 붙여 명확히 지정해야 합니다.
수정된 사용 예시
SELECT EMP.ENAME, EMP.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;- 중복된 컬럼에 테이블명을 붙여서 해결 가능
- 테이블명이 긴 경우에는 ALIAS를 사용하여 간결하게 표현할 수 있음
ALIAS 사용 예시
-
ALIAS 미사용
SELECT EMP.ENAME, EMP.DEPTNO, DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; -
ALIAS 사용
SELECT E.ENAME, E.DEPTNO, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO; -
접두어 일부 생략 시
SELECT ENAME, E.DEPTNO, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO; -
ALIAS와 테이블명 혼용 시 (오류 발생)
SELECT EMP.ENAME, EMP.DEPTNO, DEPT.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO; -- ERROR!!!
- FROM 절에서 ALIAS를 정의한 후에는 WHERE/SELECT 절에서 테이블명 사용 불가
Non EQUI JOIN (비동등 조인)
- 조인 조건으로 Equal (
=) 연산 이외의 연산자를 사용하는 조인 BETWEEN,>,>=,<,<=등의 연산자를 사용하여 조인 조건을 정의- 일반적으로 두 테이블 간의 직접적인 동일값 매칭이 아닌 범위 조건을 통해 조인할 때 사용
예시: 사원별 급여 등급 조회
SELECT EMP.ENAME, EMP.SAL, GRADE.GRADE
FROM EMP, SALGRADE GRADE
WHERE EMP.SAL BETWEEN GRADE.LOSAL AND GRADE.HISAL;EMP테이블의SAL(사원의 급여)과SALGRADE테이블의LOSAL(최소 급여) 및HISAL(최대 급여) 사이의 범위 조건을 사용하여 조인SAL이 특정 범위에 해당할 때 그 범위의GRADE를 조회
INNER JOIN (내부 조인)
- 서로 대응되는 내용만 검색하는 조인 방식
- 조건절을 필수로 사용하여 두 테이블의 일치하는 데이터만 반환
- 기본 조인 방식으로
INNER키워드를 생략 가능INNER JOIN은JOIN과 동일하게 작동
예시
-
PLAYER 테이블
PLAYER_NAME TEAM_ID 홍길동 K01 강감찬 K01 김유신 K02 유관순 K03 최무선 K04 -
TEAM 테이블
TEAM_ID TEAM_NAME K01 KIA K02 두산 K03 LG K04 넥센 -
INNER JOIN 결과
SELECT PLAYER_NAME, PLAYER.TEAM_ID, TEAM_NAME FROM PLAYER INNER JOIN TEAM ON PLAYER.TEAM_ID = TEAM.TEAM_ID;PLAYER_NAME TEAM_ID TEAM_NAME 홍길동 K01 KIA 강감찬 K01 KIA 김유신 K02 두산 TEAM_ID가 일치하는 행만 출력됨
- 암시적 조인과 명시적 조인의 두 가지 방식이 존재
- 대부분의 DBMS에서 명시적 조인을 표준으로 채택하고 있지만, 기존의 암시적 조인도 허용
1. 암시적 조인
SELECT E.ENAME, E.DEPTNO, E.SAL, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO AND E.SAL > 2000;- 특징:
WHERE절에 조인 조건과 일반 조건을 함께 사용하여 가독성이 떨어질 수 있음- 조건이 많아질 경우 명확히 구분하기 어려워질 수 있음
2. 명시적 조인 (표준 조인)
SELECT E.ENAME, E.DEPTNO, E.SAL, D.DNAME
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE E.SAL > 2000;- 특징:
ON절을 사용해 조인 조건을 명확하게 분리할 수 있음- 조인 조건과 일반 조건을 구분하여 가독성이 향상됨
INNER키워드는 생략 가능 (JOIN만 사용해도 동일한 결과)
NATURAL JOIN
-
INNER JOIN의 특수한 경우
NATURAL INNER JOIN은NATURAL JOIN과 동일하게 작동
-
두 테이블 간 동일한 이름을 갖는 모든 컬럼에 대해 EQUI JOIN 수행
- 컬럼 간 데이터 타입도 동일해야 함
- 별도의 조인 컬럼 및 조건을 지정할 수 없음
-
조인의 대상이 되는 컬럼에는 접두사(테이블 명 또는 ALIAS)를 사용할 수 없음
예시: NATURAL JOIN 쿼리
SELECT EMPNO, ENAME, DEPTNO, DNAME
FROM EMP NATURAL JOIN DEPT;동일한 결과를 얻는 INNER JOIN 쿼리
SELECT EMP.EMPNO, EMP.ENAME, EMP.DEPTNO, DEPT.DNAME
FROM EMP
INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;- 위 두 쿼리는 동일한 출력 결과를 제공합니다.
NATURAL JOIN vs INNER JOIN
| 구분 | NATURAL JOIN | INNER JOIN |
|---|---|---|
| 조인 조건 | 동일한 이름을 가진 컬럼들만 자동으로 매칭 | 사용자가 ON 절에 조인 조건을 명시적으로 지정 |
| 컬럼명 지정 | 동일한 이름을 가진 모든 컬럼이 자동으로 조인되며, 별도의 조건 지정 불가 | 조인 조건을 원하는 컬럼으로 자유롭게 지정 가능 |
| 접두사 사용 | 조인에 사용된 컬럼에 테이블명 또는 ALIAS를 붙일 수 없음 | 테이블명 또는 ALIAS를 붙여 명확하게 컬럼을 지정할 수 있음 |
| 가독성 | 단순하지만 조인 조건을 세부적으로 제어하기 어려움 | 조인 조건을 명확하게 지정할 수 있어 복잡한 조인에 적합 |
| 사용 예시 | SELECT * FROM EMP NATURAL JOIN DEPT; | SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO; |
JOIN 조건절
WHERE, ON, USING 절의 조건 기술 비교
| 구분 | 설명 | 예시 |
|---|---|---|
| WHERE 절 | - 암시적 조인 방식 - 모든 조건을 WHERE 절에 기술- 조인과 일반 조건이 혼용되어 가독성이 떨어질 수 있음 | SELECT ENAME, EMP.DEPTNO, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; |
| ON 절 | - 명시적 조인 방식 - 조인 조건은 ON 절에, 일반 조건은 WHERE 절에 분리- 조인 대상 컬럼에 ALIAS 사용 가능 | SELECT E.ENAME, E.DEPTNO, D.DNAME FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO; |
| USING 절 | - 공통 컬럼을 기준으로 조인할 때 사용 - ON 절 대신 USING 절 사용 가능- 접두사 (테이블 명/ALIAS) 사용 불가 | SELECT ENAME, DEPTNO, DNAME FROM EMP JOIN DEPT USING (DEPTNO); |
예시 코드 비교
-
WHERE 절 (암시적 조인)
SELECT ENAME, EMP.DEPTNO, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO; -
ON 절 (명시적 조인)
SELECT ENAME, EMP.DEPTNO, DNAME FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO; -
USING 절 (공통 컬럼)
SELECT ENAME, DEPTNO, DNAME FROM EMP JOIN DEPT USING (DEPTNO); -
NATURAL JOIN (자동 조인)
SELECT ENAME, DEPTNO, DNAME FROM EMP NATURAL JOIN DEPT;
OUTER JOIN (외부 조인)
정의
- 서로 매칭되지 않는 행도 포함하여 출력하는 조인 방식
- 조건절을 반드시 사용해야 함
- 성능 저하의 원인이 될 수 있어 필요한 경우에만 사용 권장
OUTER JOIN (외부 조인)
LEFT OUTER JOIN
- 왼쪽 테이블의 모든 데이터를 포함하며, 오른쪽 테이블에 매칭되는 데이터가 없는 경우
NULL로 채움 - 예시 쿼리:
SELECT PLAYER_NAME, TEAM_ID, TEAM_NAME FROM PLAYER LEFT JOIN TEAM ON PLAYER.TEAM_ID = TEAM.TEAM_ID;
RIGHT OUTER JOIN
- 오른쪽 테이블의 모든 데이터를 포함하며, 왼쪽 테이블에 매칭되는 데이터가 없는 경우
NULL로 채움 - 예시 쿼리:
SELECT PLAYER_NAME, TEAM_ID, TEAM_NAME FROM PLAYER RIGHT JOIN TEAM ON PLAYER.TEAM_ID = TEAM.TEAM_ID;
FULL OUTER JOIN
- 양쪽 테이블의 모든 데이터를 포함하며, 어느 한쪽 테이블에 매칭되는 데이터가 없을 경우
NULL로 채움 FULL OUTER JOIN은LEFT JOIN과RIGHT JOIN의 합집합과 같으며 중복이 제거됨- 예시 쿼리:
SELECT PLAYER_NAME, TEAM_ID, TEAM_NAME FROM PLAYER FULL OUTER JOIN TEAM ON PLAYER.TEAM_ID = TEAM.TEAM_ID;
CROSS JOIN에 대한 간단한 정리입니다.
CROSS JOIN (교차 조인)
정의
- 두 테이블의 모든 조합(곱집합, Cartesian Product)을 출력하는 조인 방식
- 별도의 조인 조건이 필요하지 않음
- 각 행이 서로 모든 경우의 수로 결합되어 출력됨
예시
| 번호 | 이름 | 생산품코드 | 상품명 |
|---|---|---|---|
| 1 | 홍길동 | A | 면도기 |
| 2 | 임꺽정 | A | 면도기 |
| 1 | 홍길동 | B | 칫솔 |
| 2 | 임꺽정 | B | 칫솔 |
| 1 | 홍길동 | C | 치약 |
| 2 | 임꺽정 | C | 치약 |
- 예시 설명:
번호와이름이 있는 테이블과생산품코드,상품명이 있는 테이블을 CROSS JOIN하면, 두 테이블의 모든 가능한 조합이 생성됩니다.
기본 테이블
STUDENT 테이블
| S_NAME | S_ID | DEPT |
|---|---|---|
| KIM | 111 | B |
| LEE | 222 | C |
| CHOI | 333 | D |
| PARK | 444 | B |
DEPT 테이블
| D_ID | D_NAME |
|---|---|
| A | MIS |
| B | CS |
| C | BIO |
JOIN 유형별 결과와 설명
| JOIN 유형 | SQL 문장 | 설명 | 레코드 수 |
|---|---|---|---|
| INNER JOIN | SELECT S.S_NAME, D.D_NAME FROM STUDENT S INNER JOIN DEPT D ON (S.DEPT = D.D_ID); | STUDENT 테이블과 DEPT 테이블에서 DEPT 값이 일치하는 행만 조회. 즉, DEPT가 B와 C인 데이터만 가져옴 | 3 |
| CROSS JOIN | SELECT S.S_NAME, D.D_NAME FROM STUDENT S CROSS JOIN DEPT D; | 모든 행의 조합을 출력하는 곱집합 (Cartesian Product). 두 테이블의 모든 행이 조합되므로 총 4 * 3 = 12개의 결과가 나옴 | 12 |
| LEFT OUTER JOIN | SELECT S.S_NAME, D.D_NAME FROM STUDENT S LEFT OUTER JOIN DEPT D ON (S.DEPT = D.D_ID); | STUDENT 테이블의 모든 행과 일치하는 DEPT의 값을 함께 조회. 일치하는 값이 없으면 NULL로 표시되므로, DEPT가 D인 학생은 D_NAME이 NULL로 표시됨 | 4 |
| RIGHT OUTER JOIN | SELECT S.S_NAME, D.D_NAME FROM STUDENT S RIGHT OUTER JOIN DEPT D ON (S.DEPT = D.D_ID); | DEPT 테이블의 모든 행과 일치하는 STUDENT의 값을 함께 조회. 일치하는 값이 없으면 NULL로 표시되며, D_ID가 A인 부서의 학생은 S_NAME이 NULL로 표시됨 | 4 |
| FULL OUTER JOIN | SELECT S.S_NAME, D.D_NAME FROM STUDENT S FULL OUTER JOIN DEPT D ON (S.DEPT = D.D_ID); | STUDENT와 DEPT의 모든 행을 출력하며, 일치하지 않는 경우 NULL로 표시. DEPT가 A인 부서와 DEPT가 D인 학생의 경우 NULL로 출력됨 | 5 |
SELF JOIN (셀프 조인)
- 정의: 동일 테이블 내에서 데이터를 조인하는 방식.
- 특징:
FROM절에 동일 테이블이 두 번 이상 나타남.- 테이블 식별을 위해 반드시 별칭(Alias) 사용이 필요.
- 동일한 테이블을 개념적으로 서로 다른 두 개의 테이블로 사용.
예제
목표: EMP 테이블에서 사원의 사번과 이름, 매니저의 사번과 이름을 출력. 단, 매니저가 없는 사원의 정보도 포함하여 출력.
SQL 예시
SELECT
E.EMP_NO AS 사원_사번,
E.EMP_NAME AS 사원_이름,
M.EMP_NO AS 매니저_사번,
M.EMP_NAME AS 매니저_이름
FROM
EMP E
LEFT JOIN
EMP M ON E.MANAGER_ID = M.EMP_NO;설명
- E와 M이라는 별칭을 사용하여 동일한
EMP테이블을 두 번 참조함. LEFT JOIN을 통해 매니저가 없는 사원도 포함하여 조회.E.MANAGER_ID = M.EMP_NO조건으로 사원의 매니저 정보를 가져옴.
SELF JOIN은 동일 테이블에서 상호 관계를 표현할 때 유용하며, 예를 들어 사원-매니저 관계와 같은 계층적 관계를 나타낼 수 있습니다.
계층형 질의
계층형 데이터
- 동일 테이블에서 계층적으로 상위와 하위 데이터가 포함된 데이터 구조
- 엔터티가 순환 관계로 설계된 경우 발생
- 계층형 질의(Hierarchical Query)를 통해 접근 가능
계층형 질의의 방향
- 순방향 전개: 루트에서 리프까지 하향 전개
- 역방향 전개: 리프에서 루트까지 상향 전개
계층형 질의의 구조
- START WITH: 질의 시작 조건을 지정
- 예:
START WITH MGR IS NULL또는START WITH EMPNO = 'D'
- 예:
- CONNECT BY: 다음 전개 방향을 지정
- 순방향:
CONNECT BY PRIOR EMPNO = MGR(자식 = 부모) - 역방향:
CONNECT BY PRIOR MGR = EMPNO(부모 = 자식)
- 순방향:
순방향 계층형 질의 예시
- SELECT 문 예시
SELECT LEVEL, EMPNO 사원, MGR 관리자, CONNECT_BY_ISLEAF ISLEAF FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR; - Pseudo Column
- [LEVEL]: 시작 노드를 1로 하여 각 계층에 대해 1씩 증가
- [CONNECT_BY_ISLEAF]: 해당 노드의 후속 노드가 없으면 1, 그렇지 않으면 0
역방향 계층형 질의 예시
-
SELECT 문 예시
SELECT LEVEL, EMPNO 사원, MGR 관리자, CONNECT_BY_ISLEAF ISLEAF FROM EMP START WITH EMPNO = '7876' CONNECT BY PRIOR MGR = EMPNO; -
Pseudo Column
- [LEVEL]: 시작 노드를 1로 하여 Root까지 1씩 증가
- [CONNECT_BY_ISLEAF]: 해당 노드의 후속 노드가 없으면 1, 그렇지 않으면 0
추가 Pseudo Column
- [CONNECT_BY_ROOT]: 시작 노드의 해당 칼럼을 표시
- [SYS_CONNECT_BY_PATH]: 시작 노드부터 현재 노드까지 경로를 표시
집합 연산자
집합 연산자 개요
- 여러 질의(Select 문) 결과를 하나로 결합하기 위해 사용
- 집합 연산의 대상이 되는 두 질의는 다음의 조건을 만족해야 함:
- SELECT 절의 칼럼 수가 동일해야 함
- SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환 가능해야 함 (동일한 데이터 타입일 필요는 없음)
집합 연산자의 종류 및 의미
| 집합 연산자 | 연산자 의미 |
|---|---|
| UNION | 여러 SQL문 결과에 대한 합집합 (중복된 행은 제거한 후 하나의 행만 출력) |
| UNION ALL | 여러 SQL문 결과에 대한 합집합 (중복된 행도 삭제하지 않고 모두 출력 → 속도가 빠르므로 우선 고려) |
| INTERSECT | 여러 SQL문 결과에 대한 교집합 (중복된 행은 제거한 후 하나의 행만 출력) |
| MINUS | 앞의 SQL문 결과에서 뒤의 SQL문 결과를 뺀 차집합 (중복된 행은 제거한 후 하나의 행만 출력) Oracle에서 사용 |
| EXCEPT | 앞의 SQL문 결과에서 뒤의 SQL문 결과를 뺀 차집합 (중복된 행은 제거한 후 하나의 행만 출력) MS-SQL에서 사용 |
UNION과INTERSECT연산은 기본적으로 중복된 행을 제거하여 출력하므로 결과의 중복을 원하지 않을 때 사용.UNION ALL은 중복된 행도 모두 포함하여 출력하므로, 데이터의 중복이 필요한 경우 유용하며, 속도 측면에서 유리.
집합 연산과 ALIAS (별칭)
1. ALIAS를 활용한 ORDER BY
SELECT PLAYER_NAME, TEAM_ID AS TP
FROM PLAYER
WHERE TEAM_ID = 'K06'
ORDER BY TP;- 설명:
TEAM_ID칼럼에TP라는 별칭을 부여하여ORDER BY절에서 사용. - 결과: 별칭
TP를 사용하여 정렬이 정상적으로 이루어짐.
SELECT PLAYER_NAME, TEAM_ID AS TP
FROM PLAYER
WHERE TEAM_ID = 'K06'
ORDER BY TEAM_ID;- 설명:
TEAM_ID의 원래 이름으로ORDER BY절에서 사용. - 결과: 원본 칼럼명
TEAM_ID로도 정렬이 가능함.
2. UNION ALL과 ALIAS를 활용한 ORDER BY
SELECT PLAYER_NAME, TEAM_ID AS ETC
FROM PLAYER
WHERE TEAM_ID = 'K06'
UNION ALL
SELECT PLAYER_NAME, POSITION
FROM PLAYER
WHERE POSITION = 'GK'
ORDER BY ETC;- 설명: 두 개의 SELECT 문을
UNION ALL로 결합하고, 첫 번째 SELECT 문의TEAM_ID칼럼에ETC라는 별칭을 부여하여ORDER BY에서 사용. - 결과:
ETC별칭을 사용한 정렬이 정상적으로 이루어짐.
SELECT PLAYER_NAME, TEAM_ID AS ETC
FROM PLAYER
WHERE TEAM_ID = 'K06'
UNION ALL
SELECT PLAYER_NAME, POSITION
FROM PLAYER
WHERE POSITION = 'GK'
ORDER BY TEAM_ID;- 설명:
UNION ALL로 결합된 두 SELECT 문에서ORDER BY절에TEAM_ID를 사용하려고 함. - 결과:
ERROR발생.UNION ALL사용 시ORDER BY절에서 별칭이 아닌 원본 칼럼명을 사용하는 경우 오류가 발생할 수 있음.
.