해당 포스트는
SQLD를 학습하며 정리한 내용에 대한 포스트입니다.
이론을 학습한 내용을 정리하고 퀴즈, 기출문제 등을 기록하려고 합니다.
🌈 JOIN
두 개 이상의 테이블을 연결하여 데이터를 출력하는 것
여러 테이블로부터 원하는 데이터를 조회하기 위해서는 전체 테이블 개수에서 최소 N-1 개 만큼의 JOIN 조건이 필요하다.
일반적으로 JOIN은 PK와 FK 값의 연광성에 의해 성립된다.
- 테이블 간에 PK, FK의 연관 관계가 없어도 JOIN이 가능하다.
EQUI JOIN과 NON-EQUI JOIN은 하나의 쿼리에서 같이 사용할 수 있다.
JOIN 되는 두 테이블에 모두 존재하는 컬럼의 경우 컬럼명 앞에 반드시 테이블명이나 ALIAS를 명시해줘야 한다.
DBMS 옵티마이저는 FROM 절에 나열된 테이블이 아무리 많아도 항상
2개의 테이블씩 짝을 지어 JOIN을 수행한다.
💻 EQUI(등가)
조인은 여러 개의 릴레이션을 사용해서 새로운 릴레이션을 만드는 과정이다.
조인의 가장 기본은 교집합을 만드는 것이다.
두 개의 테이블 간의 컬럼 값들이 정확하게 일치하는 경우에 사용되는 방법이다.
EQUI JOIN은 = 연산자에 의해서만 수행되며, 그 이외의 비교연산자를 사용하는 경우에는 모두 NON EQUI JOIN이다.
- EQUI 조인은 EMP 테이블과 DEPT 테이블에서 DEPTNO 컬럼을 사용하여 같은 것을 조인한다.
- 위의 예를 보면 EMP 테이블의 DEPTNO와 DEPT 테이블의 DEPTNO가 같은 것을 사용하여 조인한다.
- EQUI 조인은 “=”을 사용해서 두 개의 테이블을 연결한다.
INNER JOIN
EQUI 조인과 마찬가지로 ISO 표준 SQL로 INNER JOIN이 있다.
INNER JOIN은 ON문을 사용해서 테이블을 연결한다.
- 위의 예를 보면 INNER JOIN구에 두 개의 테이블명을 서술하고 ON구조 조인 조건을 서술한다.
EQUI 조인을 한 후에 실행 계획을 확인해서 내부적으로 두 개의 테이블을 어떻게 연결했는지 확인할 수 있다.
위의 예를 보면 DEPT 테이블과 EMP 테이블 전체를 읽은 다음에 (TABLE ACCESS FULL) 해시 함수를 사용해서 두 개의 테이블을 연결한 것이다.
해시 함수는 테이블을 해시 메모리에 적재한 후에 해시 함수로써 연결하는 방법이다.
해시 조인은 EQUI 조인만 사용 가능한 방법
해시 조인(Hash Join)
해시 조인은 두 테이블을 결합하는 방법 중 하나로 특히 큰 테이블과 작은 테이블 간의 결합에 유용하다.
- 선행 테이블 결정 및 로드 : 먼저, 선행(작은) 테이블을 메모리에 로드한다.
- 해시 테이블 생성 : 메모리에 로드된 선행 테이블의 각 로우에서 해시 코드를 계산하고, 이를 사용하여 메모리 내부의 해시 테이블을 생성한다.
- 후행 테이블 스캐닝 및 일치 항목 검색 : 그 다음으로 후행(큰) 테이블을 디스크에서 순차적으로 스캐닝하며 각 로우의 해시 코드를 계산한다. 이 해시 코드를 사용하여 앞서 생성한 해시 테이블에서 일치하는 항목들을 찾아낸다.
주요한 제약 사항들은 다음과 같다
- 메모리 사용량: 해시 조인은 상당량의 메모리를 필요로 한다. 따라서 충분한 메모리가 확보되지 않으면 성능 저하가 발생할 수 있다.
- 조건 제약: 동등조건 (equality condition) 이외에는 사용할 수 없다.
- 데이터 크기와 속도 저하: 만일 데이터 용량이 너무 커서 메모리 영역보다 클 경우, 하드디스크 가상디스크(임시영역)에 저장되게 되어 속도 저하가 발생할 수 있다.
따라서 작은 데이터셋과 큰 데이터셋 사이에서 동등 조건 기반의 조인 연산을 할 때만 적절하게 활용해야 한다.
해시코드
메모리를 구별할 수 있는 유일한 값을 정수값으로 표식
해시 코드는 임의의 길이의 입력값(여기서는 각 로우 값)을 고정된 길이의 고유한 값으로 변환하는 함수
동일한 입력값은 항상 동일한 출력값(해시 코드)을 생성
SQL에서 해싱 함수는 주로 분산 저장 시스템에서 빠른 검색 속도를 제공하기 위해 사용
해싱 함수는 ‘해쉬 조인’ 같은 쿼리 최적화 기법에서도 중요하게 활용
예를 들어 Oracle DBMS 에서 제공하는 HASH 함수가 있으며 HASH(column_name) 형태로 사용되어 해당 컬럼 값들을 고정된 길이의 수치형으로 변환
온라인 트랜젝션 처리(OLTP)에는 해시조인보다 NESTED LOOP 방식이 유용하다. 그 이유는 NESTED LOOP방식은 적은 데이터를 조인할 때 유리한 구조이기 때문이다.
INTERSECT 연산
INTERSECT 연산은 두 개의 테이블에서 교집합을 조회한다.
즉, 두 개 테이블에서 공통된 값을 조회한다.
💻 Non-EQUI(비등가) 조인
Non-EQUI는 두 개의 테이블 간에 조인하는 경우 =을 사용하지 않고 BETWEEN, >, <, 〉=, <= 등을 사용한다.
즉, Non-EQUI 조인은 정확하게 일치하지 않는 것을 조인하는 것이다
💻 OUTER JOIN
JOIN 조건을 만족하지 않는 행들도 출력되는 형태
OUTER JOIN 에서 기준이 되는 테이블은 항상 모두 출력되지만 최종적으로 WHERE 조건에 의해 필터링을 거치게 된다.
OUTER JOIN은 두 개의 테이블 간에 교집합(EQUI JOIN)을 조회하고 한쪽 테이블에만 있는 데이터도 포함시켜서 조회한다.
예를 들어 DEPT 테이블과 EMP 테이블을 OUTER JOIN 하면 DEPTNO가 같은 것을 조 회하고 DEPT 테이블에만 있는 DEPTNO도 포함시킨다.
이때 왼쪽 테이블에만 있는 행도 포함하면
LEFT OUTER JOIN이라고 하고 오른쪽 테이블의 행만 포함시키면RIGHT OUTER JOIN이라고 한다.FULL OUTER JOIN은 LEFT OUTER JOIN과 RIGHT OUTER JOIN 모두를 하는 것이다.
Oracle에서는 모든 행이 출력되는 테이블의 반대편 테이블의 옆에
(+)기호를 붙여 작성하면 된다.단 좌변이나 우변 중 하나에만 표기해야 한다.
- 위의 예를 보면 FROM절에 DEPT와 EMP 테이블이 있다.
LEFT OUTER JOIN과 RIGHT OUTER JOIN
LEFT OUTER JOIN은 왼쪽에 표기된 테이블의 데이터는 무조건 출력되는 JOIN
- 위의 예처럼 왼쪽 부서코드에 40번까지 조회된다.
- RIGHT OUTER JOIN은 오른쪽에 표기된 테이블의 데이터는 무조건 출력되는 JOIN
위의 예처럼 오른쪽 사원번호 1002, 1003까지 포함되어서 조회된다
즉, 관리자 테이블의 NULL인 행도 조회된다.
FULL OUTER JOIN
왼쪽, 오른쪽 테이블의 데이터가 모두 출력되는 방식
LEFT OUTER JOIN과 RIGHT OUTER JOIN의 합집합이라고 이해하면 된다.
단, 중복값은 제거된다.
💻 NATURAL JOIN
A 테이블과 B 테이블에서 같은 이름을 가진 컬럼들이 모두 동일한 데이터를 가지고 있을 경우 JOIN이 되는 방식
SQL Serve(MSSQL)에서는 지원되지 않는다
NATURAL JOIN에는 ON절을 쓸 수 없다.
Oracle에서는 USING 조건절을 이요하여 같은 이름을 가진 컬럼 중 원하는 컬럼만 JOIN에 이용할 수 있다.
단, SELECT 절에서 USING 절로 정의된 컬럼 앞에는 별도의 ALIAS나 테이블명을 붙이지 않아야 한다.
💻 CROSS JOIN (상호조인)
CROSS JOIN은 A 테이블과 B 테이블 사이에 JOIN 조건이 없는 경우, 조합할 수 있는 모든 경우를 출력하는 방식
CROSS JOIN은 조인 조건구 없이 2개의 테이블을 하나로 조인한다.
조인구가 없기 때문에 카테시안 곱이 발생한다.
- 예를 들어 행이 14개 있는 테이블과 행이 4개 있는 테이블을 조인하면 56개의 행이 조회된다.
CROSS JOIN은 FROM절에 CROSS JOIN구를 사용하면 된다.
MERGE JOIN으로 실행결과가 출력된다.
💻 집합 연산자 사용
| UNION | - 각 쿼리의 결과 집합의 합집합이다. - 중복행은 한줄로 출력된다. 중복❌ |
| UNINON ALL | - 각 쿼리의 결과 집합의 합집합이다. 중복된 행도 그대로 출력된다. 중복✅ |
| INERSECT | 각 쿼리의 결과 집합의 교집합이다. - 중복된 행은 한 줄로 출력된다. 중복❌ |
| MINUS/EXCEPT | - 앞에 있는 쿼리의 결과 집에서 뒤에 있는 쿼리의 결과 집합을 뺀 차집합이다. - 중복된 행은 한줄로 출력된다. 중복❌ |
UNION
UNION 연산은 두 개의 테이블을 하나로 만드는 연산이다.
즉, 2개의 테이블을 하나로 합치는 것이다.
주의사항은 두 개의 테이블의 컬럼 수, 컬럼의 데이터 형식 모두가 일치해야 한다.
만약 두 개의 테이블에 UNION 연산이 사용될 때 컬럼 수 혹은 데이터 형식이 다르면 오류가 발생한다.
UNION 연산은 두 개의 테이블을 하나로 합치면서 중복된 데이터를 제거한다. 중복을 제거하는 과정에서 성능상 불리할 수 있다.
그래서 UNION은 정렬(SORT) 과정을 발생시킨다
UNION ALL
UNION ALL은 두 개의 테이블을 하나로 합치는 것이다.
UNION처럼 중복을 제거하거나 정렬을 유발하지 않는다.
MINUS/EXCEPT(차집합)
MINUS 연산은 두 개의 테이블에서 차집합을 조회한다.
- 즉, 먼저 쓴 SELECT문에는 있고 뒤에 쓰는 SELECT문에는 없는 집합을 조회하는 것이다.
MS—SQL에서는 MINUS와 동일한 연산이 EXCEPT이다.
- 위의 예처럼 DEPT와 EMP를 MINUS 연산을 하면 DEPT에만 있는 행을 조회하는 것이다.
- 앞의 MINUS 연산 결과를 보면 DEPT 테이블에만 존재하는 DEPTNO 40번이 조회된 것을 확인할 수 있다.
MINUS 연산을 하게 되면 중복된 행이 제거된다.
INTERSECT(교집합)
💻 SELF JOIN (셀프 조인)
셀프 조인은 말 그대로 나 자신과의 조인이다.
FROM 절에 같은 테이블이 두 번 이상 등장하기 때문에 혼란을 막기 위해 반드시 ALIAS를 표기해줘야 한다.
🎯 추가 내용
📚 레퍼런스
정미나. (유튜브 선생님에게 배우는) 유선배 SQL개발자(SQLD) 과외노트 / [정미나 저] (2023). Print.
한국데이터산업진흥원. SQL 자격검정 실전문제 : 국가공인 SQL전문가·국가공인 SQL개발자 / 한국데이터산업진흥원 [편] (2020). Print.