[데이터베이스] 조인 연산의 종류와 조건

Joined Relations

조인 연산은 두 개의 관계(Relation)를 입력으로 받아 또 다른 관계를 결과로 반환하는 연산이다.

두 개 이상의 테이블에서 관련된 열(Column)을 기준으로 행(Row)을 결합할 때 사용된다.

일반적으로 FROM 절의 서브쿼리 표현식으로 활용된다.

조인의 종류

1. 자연 조인(Natural Join)
   • 두 테이블에서 동일한 이름을 가진 속성을 기준으로 자동으로 조인한다.
   • 중복된 속성은 하나로 합쳐진다.
2. 내부 조인(Inner Join = join)
   • 조인 조건을 만족하는 행들만 결과로 반환한다.
   • ON 절을 사용하여 특정 조건을 명시할 수 있다.
3. 외부 조인(Outer Join)
   • 한쪽 테이블에 매칭되는 데이터가 없어도 결과에 포함되도록 한다.
   • 세 가지 유형이 있다.
     • 왼쪽 외부 조인(Left Outer Join): 왼쪽 테이블의 모든 행을 포함하며, 매칭되지 않는 오른쪽 테이블의 값은 NULL로 채운다.
     • 오른쪽 외부 조인(Right Outer Join): 오른쪽 테이블의 모든 행을 포함하며, 매칭되지 않는 왼쪽 테이블의 값은 NULL로 채운다.
     • 완전 외부 조인(Full Outer Join): 양쪽 테이블의 모든 행을 포함하며, 매칭되지 않는 값은 NULL로 채운다.

---

Natural Join in SQL

NATURAL JOIN은 두 테이블 간에 이름이 같은 속성(컬럼) 을 기준으로 자동으로 매칭해서 조인을 수행합니다. 즉, 두개의 테이블에서 같은 속성 가지고 있는게 있으면 모든 속성이 다 같을떄 튜플만 반환합니다.

• 이름이 같은 속성이 여러 개일 경우, 모두 같은 값일 때만 행을 매칭합니다.
• 그리고 공통된 컬럼은 결과에 한 번만 나타납니다.

카테시안 곱으로 작성한 경우 (공통된 속성이 ID밖에 없다면)

select name, course_id
from students, takes
where student.ID = takes.ID;

 

natural join 문 사용

select name, course_id
from student natural join takes;

 

Natural Join 문법

from 절은 natural join을 하면서 여러 relation을 조합할 수 있다.

select A1, A2, … An
from r1 natural join r2... natural join rn
where P ;

student natural join takes

학생 테이들과 수강신청 테이블 natural join 한 예이다.

ID가 00128인 튜블은 takes 테이들의 ID 00128인 두개랑 합쳐져서 총 튜블 2개가 생성된 모습이다.

 

자연 조인(Natural Join) 사용 시 주의사항

자연 조인은 공통된 속성을 자동으로 기준으로 삼아 조인하는 방식이다.

그러나, 의도하지 않은 속성 이름이 같을 경우 예상치 못한 결과가 발생할 수 있다.

주의할 점

• 관련이 없는 속성이라도 이름이 동일하면 자동으로 조인 조건에 포함될 수 있다.
• 데이터베이스 설계 시 동일한 이름의 속성이 여러 테이블에 존재할 경우 주의가 필요하다.
• 예상치 못한 조인이 발생하지 않도록, 명시적인 조인 조건을 추가하는 것이 좋다.

 

잘못된 예제

select name, title
from student natural join takes natural join course;

• 자연 조인을 사용했을 때, student, takes, course 테이블 간의 공통 속성이 자동으로 조인된다.
• 만약 student와 course에 같은 이름의 속성이 존재하면, 의도하지 않은 조인 조건이 적용될 수 있다.

 

올바른 접근 방식

select name, title
from student natural join takes, course
where takes.course_id = course.course_id;

• student와 takes 테이블은 자연 조인으로 결합하되,course 테이블은 명시적으로 course_id를 기준으로 카테시안 곱을 한 후 조인한다.
• 이렇게 하면 원하지 않는 속성으로 인해 발생하는 오류를 방지할 수 있다.

---

on절을 이용한 join

ON 조건을 사용하면 조인하는 관계들 사이에서 특정 조건을 적용할 수 있다.

이는 조인할 두 테이블 간의 속성을 명확하게 지정하는 방법이다.

다음 두 개의 쿼리는 동일한 결과를 반환한다.

ON 조건을 사용한 조인

두 테이블을 조인할 때, student_ID와 takes_ID가 같은 경우만 선택한다.

select *
from student join takes on student_ID= takes_ID

WHERE 절을 사용한 조인

두 테이블을 나열한 후, WHERE 절에서 student_ID와 takes_ID가 같은 경우만 선택한다.

select *
from student , takes
where student_ID= takes_ID

---

Outer Join

정보 손실을 피하기 위한 조인 확장 Outer Join은 일반적인 Join에서 발생할 수 있는 정보 손실(loss of information)을 방지하기 위해 사용되는 확장된 조인 방식이다.

• 일반 Join처럼 두 릴레이션을 조인한 후,
• 한 릴레이션에는 존재하지만 다른 릴레이션에 매칭되지 않는 튜플도 결과에 포함시킴
• 이때 매칭되지 않는 컬럼에는 NULL 값을 채워 넣음

Outer Join의 세 가지 형태

1. Left Outer Join

• 왼쪽 릴레이션의 모든 튜플을 결과에 포함
• 오른쪽 릴레이션에 매칭되는 튜플이 없으면, 오른쪽 컬럼은 NULL로 채움

2. Right Outer Join

• 오른쪽 릴레이션의 모든 튜플을 결과에 포함
• 왼쪽 릴레이션에 매칭되는 튜플이 없으면, 왼쪽 컬럼은 NULL로 채움

3. Full Outer Join

• 왼쪽과 오른쪽 모두의 모든 튜플을 결과에 포함
• 어느 쪽이든 매칭되지 않는 튜플은 해당되지 않는 컬럼에 NULL을 채워 포함

---

Left Outer Join

course ⟕ prereq

• 왼쪽 테이블의 튜플을 전부 유지하고 싶을때 이용한다.
• course natural left outer join prereq 는 왼쪽에 있는 course 테이블의 모든 튜플을 결과에 포함시켜야 한다.
• CS-347 같은 튜플이 prereq 테이블에 매칭되지 않더라도 결과에서 사라지지 않는다.
• Outer 조인은 조인 조건을 만족하지 않아도 튜플을 보존하고 싶을 때 사용한다.
• 첫 번째와 두 번째 줄은 natural join을 했을 때의 결과이고,
• 마지막 한 줄은 prereq 의 속성값들이 null로 채워진 채 결과에 포함된 것이다.

---

Right Outer Join

course ⟖ prereq

• 오른쪽 테이블의 튜플을 무조건 유지하고 싶을때 이용한다 .
• course natural right outer join prereq 는 오른쪽에 있는 prereq 테이블의 모든 튜플을 결과에 포함시켜야 한다.
• CS-315은 결과에서 사라지지 않고 포함되어야 하는데, 자연 조인만 쓰면 없어지게 된다.
• Outer 조인은 조인 조건을 만족하지 않아도 튜플을 보존하고 싶을 때 사용한다.
• 첫 번째와 두 번째 줄은 natural join을 했을 때의 결과이고,
• 마지막 한 줄은 _course_의 속성값들이 null로 채워진 채 결과에 포함된 것이다.

---

Full Outer Join

course ⟗ prereq

• 양쪽 테이블 모두의 튜플을 보존해야 할 때 사용하는 조인이다.
• 1, 2번째 줄은 natural join의 결과이고,
• 나머지 두 줄은 왼쪽과 오른쪽 테이블의 모든 튜플을 보존했기 때문에 추가된 것이다.
• CS-315는 prereq 테이블에 없기 때문에 prereq_id 는 null 값으로 대체되었다.
• CS-347은 course 테이블에 없기 때문에 해당 속성들은 null로 대체된다.
• natural join 의 확장 형태이므로, 심볼이 비슷하다는 점도 알 수 있다.

---

조인의 종류

• INNER JOIN: 조인 조건을 만족하는 튜플만 결과에 포함
• OUTER JOIN: 조건을 만족하지 않더라도 특정 테이블의 튜플을 결과에 포함
  • LEFT OUTER JOIN: 왼쪽 테이블의 튜플을 보존
  • RIGHT OUTER JOIN: 오른쪽 테이블의 튜플을 보존
  • FULL OUTER JOIN: 양쪽 테이블의 튜플을 모두 보존

---

조인 조건 명시 방법

join types 중에서 하나 선택한 후, 조건 3개 중에서 하나만 베타적으로 이용할 수 있다.

 

• NATURAL JOIN / NATURAL LEFT OUTER JOIN ...
  • 양쪽 테이블에 이름이 같은 모든 컬럼을 자동으로 조인 조건으로 사용
  • 조인 조건을 명시하지 않아도 된다. (오로지 조건 using만 쓸 수 있다.)
• ON
  • 조인 조건을 명시적으로 작성
  • 컬럼 이름이 달라도 조인 가능
  • 복수 조건, 비교 연산 등 자유롭게 지정 가능
• USING (column_name)
  • 양쪽 테이블에 동일한 이름의 컬럼이 있을 때
  • 그 컬럼을 기준으로 조인을 수행
  • 결과에는 해당 컬럼이 한 번만 표시됨