[SQL] 조인 개념 및 크로스 조인/ 내부 조인/ 외부 및 셀프 조인

##조인 JOIN

원하는 재료집합이 2개 이상일때 연결해서 새로운 큰 재료집합을 만드는 연산

데이터의 무결성을 위해서 제약조건을 사용한다.

조인은 하나의 재료 집합으로부터 문제를 해결할수 없을떄 사용하며  재료집합이 가로로 확장됨으로  컬럼이 증가한다

###문법 : FROM절에 JOIN문법을 사용한다

###조인 조건: 연결고리 컬럼 간 관계(연결 조건) 정의 / 조인문법에 조건을 적는 곳이 있다

❓조인컬럼은 동등조건? 부등조건?

조인 조건은 일반적으로 동등 조건(equality condition)을 사용합니다. 즉, 두 개의 테이블을 조인할 때 일치하는 값이 있는 경우에만 조인이 이루어집니다

부등 조건(inequality condition)을 사용하여 조인할 수도 있지만, 이는 일반적인 경우보다는 특수한 경우에 사용됩니다. 예를 들어, 두 테이블 간의 범위를 정의하거나 특정 값보다 크거나 작은 값을 찾을 때 사용

###비조인 조건: 조인 전 재료 집합 필터링. 두재료 집합의 컬럼이 나타나지 않는 조건식

###조인 컬럼: 연결고리 컬럼, 참조 관계
###조인 유형: 새 재료 집합을 채우는 행을 선택하는 방법

무조건 조인 : CROSS JOIN

내부 조인 : INNER JOIN

외부 조인 : OUTER JOIN

셀프 조인 : SELF JOIN -동일 재료 집합 간 조인

 

 

--

제약조건이 없어도 조인을 사용할수있다. /참조관계가 없으면 의미없는 제약조건

참조 관계가 없는 경우에는 조인의 의미가 상쇄될 수 있습니다. 일반적으로 조인은 두 테이블 간의 관계를 기반으로 데이터를 연결하고 결합하는 데 사용됩니다. 따라서 만약 두 테이블 간에 어떠한 참조나 관계가 없는 경우에는 조인을 통해 얻는 정보가 무의미할 수 있습니다. 이 경우 조인을 사용하여도 유용한 결과를 얻기 어렵거나, 의미 없는 결과를 얻을 수 있습니다.

 

--

❓ 데이터 무결성

데이터 무결성은 데이터베이스 내의 데이터가 정확하고 일관성 있게 유지되어야 함을 의미합니다. 데이터 무결성을 유지하기 위해서는 데이터베이스 설계 단계에서 적절한 제약 조건을 설정하고, 데이터 입력 및 수정 시에 이를 준수하는 것이 중요합니다. 이러한 작업을 통해 데이터베이스의 신뢰성과 일관성을 보장할 수 있습니다.

 

--

❓ 제약조건

1. 기본 키 제약 조건 (Primary Key Constraint): 각 행을 식별하는 데 사용되는 열에 대해 기본 키 제약 조건을 정의합니다. 이를 통해 각 행은 고유한 값을 가져야 하며, 중복된 데이터가 허용되지 않습니다.

2. 외래 키 제약 조건 (Foreign Key Constraint): 다른 테이블의 기본 키를 참조하는 열에 대해 외래 키 제약 조건을 정의합니다. 이를 통해 부모 테이블과 자식 테이블 간의 관계를 설정하고 데이터의 무결성을 보장할 수 있습니다.

3. 고유 제약 조건 (Unique Constraint): 열에 대해 고유 제약 조건을 정의하여 해당 열의 값이 고유하다는 것을 보장합니다.

4. 체크 제약 조건 (Check Constraint): 열에 대해 특정 조건을 정의하여 데이터의 유효성을 검사합니다. 이를 통해 특정 조건을 만족하지 않는 데이터가 삽입되는 것을 방지할 수 있습니다.

 

--

❓데이터베이스 쿼리 옵티마이저에서 사용되는 조인 알고리즘 3가지!

1. NL 조인(네스티드 루프 조인)은 두 테이블 간의 모든 가능한 조합을 비교하는 방식입니다. 이 방식은 작은 테이블에 적합하지만 대용량 테이블에 대해서는 성능이 저하될 수 있습니다.

2. HASH 조인은 해시 함수를 사용하여 두 테이블 간의 조인 키를 해싱한 다음, 해시 테이블을 생성하여 매칭하는 레코드를 찾는 방식입니다. 이 방식은 메모리를 효율적으로 사용할 수 있으며, 특히 큰 테이블에 대해서는 효과적입니다. 하지만 해시 충돌이 발생할 경우 성능이 저하될 수 있습니다.

3. SORT MERGE 조인은 각 테이블을 조인 키에 따라 정렬한 다음, 정렬된 데이터를 병합하는 방식입니다. 이 방식은 대용량 테이블에 대해서도 효율적이며, 일반적으로 HASH 조인보다 메모리를 덜 사용합니다. 그러나 정렬 작업이 추가로 필요하기 때문에 초기 비용이 더 들 수 있습니다.

NL 조인은 두 테이블을 전부 비교하는 방식

HASH 조인은 해시 테이블을 사용하여 매칭하는 레코드를 찾는 방식

SORT MERGE 조인은 정렬된 데이터를 병합하는 방식

 

--

❓쿼리 옵티마이저?

쿼리 옵티마이저 는 주어진 쿼리와 데이터의 크기, 인덱스의 유무 등을 고려하여 가장 효율적인 조인 알고리즘을 선택합니다. 종종 이러한 알고리즘 중에서 최적의 조인 방법을 결정하는 것은 성능에 큰 영향을 미칩니다.

 

##조인문법 2가지

###ANSI표준조인(SQL조인) - 조인이 키워드로 되어있다.

조인유형별로 키워드가 있다.

- INNERJOIN(기본조인) -

- OUTER JOIN은 ANSI가 편하다(컬럼이잘 정리되어있어서) 

   -LEFT

   -RIGHT

   -FULL

- CROSS JOIN

- NATURAL JOIN -  조인조건없는데도 조인이 되는 공통점이 있는

   -LEFT

   -RIGHT

   -OUTER

   -INNDER

- 조인조건키워드 ON

- 비조인조건 ON절에서 AND연산자로 묶기

    - WHERE에 기술

- USING 키워드

 

 

 

###ORACLE조인 - 키워드가 없고 심플하다.

오라클 데이터베이스 전용 구문

ANSI조인의 키워드 사용안함. 

FROM 절에
    재료 집합을 컴마로 구분하여 나열
• 조인 조건
    WHERE 절에
• 비조인 조건
     WHERE 절에
• OUTER JOIN
    조인 조건에서 행이 연결되지 않는 쪽 재료 집합의 컬럼에 (+) 기호
• FULL OUTER JOIN
    직접 지원하지 않음
    OUTER JOIN과 ANTI JOIN을 UNION ALL한 쿼리로 직접 작성

 

###조인시 컬럼이름이 모호한 문제

두 재료 집합의 컬럼 이름이 서로 같은경우

 컬럼 이름 앞에 테이블 이름을 명시
테이블명.컬럼명
• 테이블 이름으로 컬럼 이름을 한정함으로써 해당 재료 집합의
컬럼임을 명확히 함
• 컬럼이 모호하지 않더라도 사용하면 성능 향상 기대

 

테이블 별칭 

테이블 이름 또는 인라인 뷰 뒤에 공백을 둔 후 테이블 별칭 정의. AS 키워드를 사용하지 않음

사용 예)

컬럼 이름을 한정하려는 데 테이블 이름이 길 경우 (옵션)
셀프 조인인 경우 (필수) - 테이블 별칭을 집합의 성격을 나타낼 수 있도록 의미있게 지정
인라인 뷰(SQL문장 내)에 임시적으로 이름을 부여해야 할 경우 (필수)

주의) 테이블 별칭 정의 후 테이블 이름이 사용될 곳에 반드시 테이블 별칭을 사용해야 함. 그렇지 않으면 문법 에러.
별칭을 지정한 현재 SELECT 문에서만 별칭이 유효.

 

###크로스 조인(CROSS JOIN) - 무조건조인

모든경우를 다 따져볼수있는 JOIN조건 / 자주사용하지는 않음

항상 TRUE인 조건 /WHERE절을 안쓰는 ?

--ANSI구문
SELECT last_name, department_name
FROM employees CROSS JOIN departments;

--ORACLE구문
SELECT last_name, department_name
FROM employees, departments; -- 콤마로 구분

 

순서가 작은 쪽에서 큰걸로 연결됨?

 

###내부조인(INNER JOIN)

조인조건을 만족하는 행으로만 연결하여 조인 결과 집합으로 생성

문법 FROM TB1 [INNER] JOIN TB2 ON 조인조건

/* 모든 부서의 주소를 생성하는 쿼리를 작성하세요. 단, 출력 포맷은
부서 이름, 위치 ID, 주소, 도시 입니다.*/

--ANSI
SELECT departments.department_name, LOCATIONS.LOCATION_ID, STREET_ADDRESS, CITY
FROM departments
INNER JOIN LOCATIONS
ON departments.LOCATION_ID = LOCATIONS.LOCATION_ID;

--ORACLE
SELECT departments.department_name, LOCATIONS.LOCATION_ID, STREET_ADDRESS, CITY
FROM departments, LOCATIONS
WHERE departments.LOCATION_ID = LOCATIONS.LOCATION_ID;

예제 p214

/*90번 부서에 근무하는 사원들 중 직무가 한 번 이상 변경된 적이
있는 사원의 부서 번호, 사원 번호, 성, 현재 직무, 과거 직무, 과거
소속 부서 번호를 출력하는 쿼리를 작성하세요. */



/* ‘Lex De Haan’ 사원의 현재 직무와 과거에 수행했던 직무 및 그
직무를 수행했던 기간을 아래와 같은 정보로 출력하세요.
- 사원 번호, 사원 성, 현재직무, 과거직무, 시작일, 종료일
*/

 

###내추럴조인(NATURAL JOIN) 

두 재료 집합에서 이름이 같고 데이터 형이 호환되는 컬럼끼리 동등 조인
• 조인 조건 불필요
   ON 절을 사용하지 않음
   NATURAL 조인은 동등 조인임
• 조인 컬럼과 조인 조건을 제어 불가
• 조인 컬럼을 테이블 이름이나 테이블 별칭으로 한정하면 오류
  조인 결과 집합에 조인 컬럼은 한 번만 나타남

 

###JOIN ~USING절

• NATURAL 조인을 하되 필요한 컬럼만 지정하고자 할 때 사용
• 동등 조인 -  조인 조건 불필요
• 괄호 안의 컬럼은 두 재료 집합에 공통인 컬럼  - 이 컬럼들이 동등 조인 조건을 구성

 

###3-WAY 조인

  
--3-WAY 조인
--ANSI
SELECT employee_id, last_name, department_name, city
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;


--ORACLE

/*예제 - 우리 회사에 근무하는 사원의 수를 각 국가별로 집계하여 보고하세요. 
출력 형식은 국가명, 사원 수이고 국가명에 대해 오름차순으로 출력합니다*/
--(ORACLE), 명시적으로 JOIN을 사용하지않았기 때문에
SELECT c.country_name 국가명, COUNT(e.last_name) 사원수
FROM countries c, locations l, 
departments d, employees e
WHERE c.country_id = l.country_id --여러테이블 조인시 WHERE, AND사용
AND l.location_id = d.location_id
AND d.department_id = e.department_id
GROUP BY c.country_name
ORDER BY 1;

--ANSI)

 

##외부조인

외부 및 셀프 조인 - 조인조건을 만족하지 않는 행도 새로운 재료 집합에 포함. 

 

내부 조인 결과 + 왼/오른/양쪽 테이블의 내부 조인 탈락 행

조인 탈락 행의 상대편 재료 집합의 행은 NULL로 채워짐

OUTER조인은 ANSI로 쓰고 / 컬럼이 많은경우 ORACLE에서 INNER JOIN쓰는게 효율굿 

-

ANSI 외부 조인 문법
      FROM 테이블1 { LEFT | RIGHT | FULL } [OUTER] JOIN 테이블2 ON 조인조건
ORACLE 외부 조인 문법
       조인 조건에서 NULL로 채워지는 쪽 재료 집합의 컬럼에 (+) 기호 추가
       FULL OUTER JOIN은 지원하지 않음
NATURAL OUTER JOIN
       a NATURAL LEFT JOIN b LEFT JOIN c ON b.c1 = c.c1

 

LEFT OUTER +왼쪽에 RIGHT OUTER인경우 +오른쪽에

 

 

##셀프조인

같은 재료 집합 간의 조인,  한 재료 집합 내에서 컬럼 간에 참조 관계가 성립
한 재료 집합이 두 재료 집합으로 간주되므로 테이블 별칭이 필수
        재료 집합의 이름이 같아서 컬럼을 구별하기 힘들기 때문
        재료 집합의 역할을 나타내는 짧고 의미 있는 이름으로 지을것을 권고
내부 조인 및 외부 조인 모두 가능

SELECT WORKER.LAST_NAME 사원이름, MANAGER.LAST_NAME 매니저이름
FROM EMPLOYEES WORKER RIGHT JOIN EMPLOYEES MANAGER--순서는 옵티마이저가 알아서 정리해줌
ON WORKER.MANAGER_ID = MANAGER.EMPLOYEE_ID;
--RIGHT JOIN의 경우 모든사원의 정보가 안나옴, 사원이름이 NULL인 경우도 출력됨
--LEFT JOIN의 경우 모든사원의 정보가 안나옴, KING이 NULL로 출력

 

p229예제