[DML_SELECT] JOIN 연산

2023. 3. 3. 14:40

728x90

JOIN 연산

지금까지는 하나의 테이블에서 데이터를 출력하는 것을 살펴보았다.

하지만 이것은 일상생활에서 발생하는 다양한 조건을 만족하는 SQL 문장을 작성하기에는 부족하다.

예를 들어 두 개 이상의 테이블과 연결 또는 결합하여 데이터를 출력하는 경우가 아주 많이 발생한다.

두 개 이상의 테이블 들을 연결 또는 결합하여 데이터를 출력하는 것을 JOIN이라고 하며,

일반적으로 사용되는 SQL 문장의 상당수가 JOIN이라고 생각하면 JOIN의 중요성을 이해가 쉬울 것이다.

JOIN은 관계형 데이터베이스의 가장 큰 장점이면서 대표적인 핵심 기능이라고 할 수 있다.

일반적인 경우 행들은 PRIMARY KEY(PK)나 FOREIGN KEY(FK) 값의 연관에 의해 JOIN이 성립된다.

하지만 어떤 경우에는 PK, FK의 관계가 없어도 논리적인 값들의 연관만으로 JOIN이 성립 가능하다.

- 두 개 이상의 테이블을 서로 연결하는데 사용되는 기법

- 테이블들은 특정 규칙에 따라 서로 상호 관계를 가짐

- 조인(JOIN) 연산자 키워드를 사용하여 관련 있는 컬럼 기준으로 행을 합쳐주는 연산

- 두 릴레이션으로부터 관련된 튜플들을 결합하여 하나의 튜플로 만드는 가장 대표적인 데이터 연결방법

- Primary Key 혹은 Foreign Key 로 두 테이블을 연결

- 조인은 관계형 데이터베이스의 가장 큰 장점이면서 대표적인 핵심 기능

- 논리적 조인과 물리적 조인으로 구분할 수 있음

논리적 조인 : 사용자 SQL 문에 표현되는 테이블 결할 방식 (내부 조인, 외부 조인)
물리적 조인 : 데이터베이스 옵티마이저에 의해 내부적으로 발생하는 테이블 결합 방식 (중첩 반복, 정렬 합병, 해시)

- 논리적 조인 유형

내부 조인(Inner Join) : 공통 존재 컬럼의 값이 같은 경우를 추출
외부 조인(Outer Join) : 왼쪽 외부조인, 오른쪽 외부 조인, 완전 외부 조인
교차 조인(Cross Join) : 조인 조건이 없는 모든 데이터 조합을 추출
셀프 조인(Self Join) : 자기 자신에게 별칭을 지정한 후 다시 조인

- 물리적 조인 유형

중첩 반복 조인(Nested-Loop Join)
: 중첩 반복 조인은 2개 이상의 테이블에서 하나의 집합을 기준으로 순차적으로 상대방 Row를 결합하여
원하는 결과를 조합하는 조인 방식이다.
: 좁은 범위에 유리한 성능을 보여주며 순차적 처리하며 임의 접근 위주이다.
정렬 합병 조인
: 정렬 합병 조인은 조인의 대상 범위가 넓은 경우 발생하는 임의 접근을 줄이기 위한 경우나
연결고리에 마땅한 인덱스가 존재하지 않을 경우 해결하기 위한 조인 방식이다.
해시 조인(Hash Join)
: 해시 조인은 해싱 함수(Hashing Function)를 활용하여 테이블 간 조인을 수행하는 방식
: 해싱 함수는 직접적인 연결을 담당하는 것이 아니라 연결될 대상을 특정 지역(Partition)에 모아두는 역할 담당
: 비용기반 옵티마이저(CBO)에서만 가능하며, CPU 성능에 의존적임.

- 내부 조인의 세부 유형은 조인의 조건에 따라 세분화 됨

- 내부 조인에서 조인의 대상이 되는 컬럼을 명시적으로 선언하기 위하여 USING 조건절이나 ON 조건절이 사용됨

- JOIN 종류

조인 기법	설명
카티션 곱(Cartesian Product)	모든 행에 대해서 조인 (2행 * 4행) = 8행
동등 조인(Equi Join) 내부 조인(Inner Join)	조인 조건이 정확히 일치할 때 조회
비동등 조인(Non Equi Join)	조인 조건이 정확히 일치하지 않는 경우 조회
외부 조인(Outer Join)	조인 조건이 정확히 일치하지 않아도 모두 조회
자체 조인(Self Join)	자체 테이블에서 조인하여 조회

https://dataschool.com/how-to-teach-people-sql/sql-join-types-explained-visually/

- 카티션 곱 (Cartesian Product) : 공통되는 컬럼 없이 조인 조건이 없어서 모든 데이터가 조회

SELECT *
FROM employees;

SELECT *
FROM departments;

SELECT *
FROM employees, departments;

- 동등 조인 (Equi Join) : 조인하는 테이블에서 조인 조건이 일치하는 것만 조회 / 내부조인

SELECT *
FROM jobs, job_history
WHERE jobs.job_id = job_history.job_id;

SELECT *
FROM countries C, locations L
WHERE C.country_id = L.country_id;

SELECT *
FROM employees E, departments D
WHERE E.department_id = D.department_id;

SELECT <열 목록>
FROM <첫 번째 테이블>
	INNER JOIN <두 번째 테이블>
    ON <조인될 조건>
[WHERE 검색 조건>

- 비 동등 조인 (Non Equi Join) : 테이블의 동일한 컬럼이 없이 다른 조건으로 조인하는 방법

SELECT *
FROM employees E, jobs J
WHERE E.salary BETWEEN J.min_salary AND J.max_salary;

SELECT E.first_name, E.hire_date, H.start_date, H.end_date
FROM employees E, job_history H
WHERE E.hire_date BETWEEN H.start_date AND H.end_date;

- 외부 조인 (Outer Join)

: 조인하는 테이블에서 조인 조건을 만족하지 않는 행도 출력

: 동등 조인 조건을 만족하지 못하고 누락된 행을 출력할 때 사용

SELECT *
FROM jobs J, job_history H
WHERE J.job_id = H.job_id(+);

- 자체 조인 (Self Join) : 자기 자신의 테이블과 조인하는 방법

SELECT
    E.first_name, E.last_name,
    M.first_name, M.last_name
FROM employees E, employees M
WHERE E.manager_id = M.employee_id;

- SQL Joins Visualizer

SQL Joins Visualizer

Please select how do you want to do SQL JOIN between two table Copy SQL

sql-joins.leopard.in.ua

Summary

STANDARD SQL (표준 SQL 개요)

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

- SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 서브쿼리 기능들

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

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

JOIN - 두개 이상의 테이블들을 연결 / 결합하여 데이터를 출력
- JOIN은 관계형 데이터베이스의 가장 큰 장점이면서 대표적인 핵심 기능
- 일반적인 경우 행들은 PK나 FK 값에 의해 JOIN에 의해 성립된다.
- 어떤 경우에는 PK, FK 관계가 없어도 논리적인 값들의 연관만으로 JOIN으로 성립이 가능하다.
- 하나의 SQL 문장에서 여러 테이블을 조인해서 사용할 수도 있다.
- FROM 절에 여러 테이블이 나열되더라도 SQL에서 데이터를 처리할 때는 두 개의 집합 간에만 JOIN이 일어난다.
- FROM 절에 A, B, C 3개의 테이블이 나열되었더라도 특정 2개의 테이블만 먼저 조인되고,
  그 조인된 새로운 결과 집합과 남은 한 개의 테이블이 다음 차례로 조인된다.
EQUI JOIN (등가조인)
- 두 테이블의 칼럼 값이 정확하게 일치하는 경우, 대부분 PK ↔ FK 관계 기반
- JOIN 조건은 WHERE 절에 기술

SELECT 테이블1.칼럼명, 테이블2.칼럼명
FROM 테이블1, 테이블2
WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2;

-- WHERE 절에 JOIN 조건절을 넣는다.

SELECT 테이블1칼럼명, 테이블2칼럼명
FROM 테이블1 INNER JOIN 테이블2 ON 테이블1.칼럼명1 = 테이블2.칼럼명2

-- ON 절에 JOIN 조건을 넣는다.

/*"선수 테이블과 팀테이블에서 선수 이름과 소속된 팀의 이름을 출력하시오.*/
SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 소속팀명
FROM PLAYER,TEAM
WHERE PLAYER.TEAM_ID = TEAM.TEAM_ID;

SELECT PLAYER.PLAYER_NAME 선수명, TEAM.TEAM_NAME 소속팀명
FROM PLAYER INNER JOIN TEAM PLAYER.TEAM_ID = TEAM.TEAM_ID;

※ 조인 시 주의사항

: 조건절에 테이블에 대한 ALIAS명을 적용하여 SQL 문장을 작성했을 경우,

WHERE 절과 SELECT절에는 테이블 명이 아닌 ALIAS를 사용

Non EQUI JOIN (비등가 조인)
- 두 테이블의 칼럼 값이 정확하게 일치하지 않는 경우
- Non EQUI JOIN의 경우에는 "=" 연산자가 아닌 다른 (Between, >, >=, <, <= 등) 연산자들을 사용하여 JOIN 수행

SELECT 테이블1.칼럼명, 테이블2.칼럼명
FROM 테이블1, 테이블2
WHERE 테이블1.칼럼명1 BETWEEN 테이블2.칼럼명1 AND 테이블2칼럼명;

/* 
선수들 별로 홈그라운드 경기장이 어디인지를 출력하고 싶다고 했을 때,
선수 테이블과 운동장 테이블이 서로 관계가 없으므로
중간에 팀테이블이라는 서로 연관관계가 있는 테이블을 추가해서
세개의 테이블을 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
ORDER BY 선수명;

SELECT P.PLAYER_NAME 선수명, P.POSITION 포지션, 
       T.REGION_NAME 연고지, T.TEAM_NAME 팀명,
       S.STADIUM_NAME 구장명
FROM PLAYER P 
INNER JOIN TEAM T ON P.TEAM_ID = T.TEAM_ID
INNER JOIN STADIUM S ON T.STADIUM_ID = S.STADIUM_ID
ORDER BY 선수명;

일반 집합 연산자
- 두 개 이상의 테이블에서 JOIN을 사용하지 않고, 연관된 데이터를 조회하는 방법
- 집합 연산자는 2개 이상의 질의 결과를 하나의 결과로 만든다.
- SELECT 절의 컬럼 수가 동일해야 하고, 동일 위치 데이터 타입이 상호 호환이 가능해야 한다.
- UNION: 합집합
- INTERSECTION: 교집합
- DIFFERENCE: EXCEPT(MINUS) 차집합
- PRODUCT: CROS JOIN 곱집합
순수 관계 연산자
- SELECT연산 → WHERE 절 : 조건에 맞는 행 조회
- PROJECT연산 → SELECT 절 : 조건에 맞는 열 조회
- (NATURAL) JOIN 연산 → 다양한 JOIN 기능 : 여러 JOIN 존재
- DIVIDE 연산은 현재 사용하지 않는다.
FROM 절의 JOIN 형태
- ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태
  : INNER JOIN / NATURAL JOIN / USING 조건절 / ON 조건절 / CROSS JOIN / OUTER JOIN
- 기존 WHERE 절 그대로 사용 가능
- FROM 절에서 JOIN 조건을 명시적으로 정의 가능
INNER JOIN - 내부 JOIN
- JOIN 조건에서 동일한 값이 있는 행만 반환
- DEFALUT 옵션이므로 생략이 가능하지만, CROSS JOIN / OUTER JOIN과는 같이 사용하지 못한다.
- USING 조건절이나 ON 조건절을 필수적으로 사용한다.
- 중복 테이블의 경우 별개의 칼럼으로 표시한다.

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

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

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

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

NATURAL JOIN
- 두 테이블 간 동일한 이름을 갖는 모든 칼럼에 대해 EQUI JOIN을 수행
- USING, ON, WHERE에서 JOIN을 정의할 수 없다.
- JOIN에 사용된 컬럼은 같은 데이터 타입이어야 한다.
- ALIAS나 접두사를 붙일 수 없다.

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

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

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

SELECT *
FROM DEPT
JOIN DEPT_TEMP USING (DEPTNO);

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

-- WHERE 절과의 혼용
/* "부서코드 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 조건절 + 데이터 검증 조건 추가
/* "매니저 사원번호가 7698번인 사원들의 이름 및 소속 부서코드, 부서 이름을 출력하시오." */
SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO AND E.MGR = 7698);
==
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO) WHERE E.MGR = 7698;

-- ON 조건절 예제
/* "팀과 스타디움 테이블을 팀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
/* "사원과 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);

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

/* "사원번호와 사원이름, 소속부서코드와 소속부서 이름을 찾아본다." */
SELECT ENAME, DNAME
FROM EMP CROSS JOIN DEPT
ORDER BY ENAME;

OUTER JOIN
- JOIN 조건에서 동일한 값이 없는 행도(NULL 포함) 출력
- USING 조건절이나 ON 조건절을 필수로 사용
- IN / ON 연산자 사용시 에러
- 표시가 누락된 칼럼이 있을 경우 OUTER JOIN 오류 발생, FULL OUTER JOIN 미지원
- FULL OUTER JOIN 미지원으로 인해 STANDARD JOIN을 주로 사용
  1. LEFT OUTER JOIN (↔ RIGHT)
    - 좌측 테이블에서 먼저 데이터를 읽은 후, 우측 테이블에서 JOIN 대상을 읽음
    - 좌측 테이블 기준이며, OUTER 키워드는 생략 가능하다.
  2. RIGHT OUTER JOIN
    - LEF OUTER JOIN과 반대로 우측 테이블이 기준이 되어 결과를 생성한다.
  3. FULL OUTER JOIN
    - 합집합 개념으로 LEFT와 RIGHT를 모두 읽어 온다.
    - JOIN이 되는 모든 테이블의 데이터를 읽어 JOIN 함

셀프 조인(Self Join)
- 동일 테이블 사이의 조인, 반드시 테이블 별칭(Alias)을 사용해야 한다.

SELECET ALIAS명1.칼럼명1, ALIAS명2.칼럼명1, __
FROM 테이블명, ALIAS명1, 테이블명 ALIAS명2
WHERE ALIAS명1.칼럼명2 = ALIAS명2.칼럼명1

728x90

저작자표시

'Language > RDBMS' 카테고리의 다른 글

[DML_INSERT] 데이터 삽입 (0)	2023.03.03
[DML_SELECT] 서브 쿼리 (0)	2023.03.03
[DML_SELECT] Multi-Row Function (0)	2023.03.03
[DML_SELECT] SQL 함수(숫자, 날짜, 변환, 일반) (0)	2023.03.03
[DML_SELECT] SQL 함수(문자) (0)	2023.03.03

-. --- - .

[DML_SELECT] JOIN 연산

'Language > RDBMS' 카테고리의 다른 글

+ Recent posts

티스토리툴바