Intermediate SQL[1]. Join

Join Relations

• Join operation은 2개의 relation으로 하나의 다른 relation을 반환한다
• Join operation은 어떠한 조건을 만족하는 두 relation의 tuple을 요구하는 Cartesian product이다.
• Join operation은 from 절에 들어가는 subquery expression으로도 이용된다

Natural Join

• Natural join은 공통된 속성에서 같은 값을 갖는 tuple들을 매칭시키고,
  각 각 공통 column의 하나의 복사본만 유지한다

List the names of instructors along with the course ID of the courses that they taught

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

Same with natural join

select name, course_id
from student natural join takes;

• 속성의 이름이 같은 걸 먼저 찾고,
  걔네들끼리 같은 pair를 찾는다.

• student relation
  

• takes relation
  

• student natural join takes
  

• 두 table에서 같은 속성은 ID이다.

List the names of students along with the titles of courses that they have taken

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

• 위 코드에서, 새로 만든 student natural join takes table의 이름이 없다. 그래서 그냥 takes로 쓴다. 아래 where 절에 나온 takes.course_id의 takes는 원래 takes table이 아니라 natural join으로 생긴 table을 의미한다.
  student라고 쓰면 안될까? student에는 course_id가 없어서 안된다고 하셨당

• course relation
  

• 위 코드를 잘못 쓰면 이렇게 된다

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

• student natural join takes에도 dept_name이 있고,
  course에도 dept_name이 있다. 그럼 이 둘을 natural join하게 되면, 하나로 합쳐질 것이다.
• 문제는, 첫 번째 dept_name은 학생의 소속 학과를 의미하고,
  두 번째 dept_name은 과목의 소속 학과를 의미한다.
• 이 둘이 합쳐지게 되면, 타 학과의 수업을 듣는 학생의 정보는 없어지게 된다.

• 글로 보는 것보다 직접 해보는 게 더 나을 것 같다..

using

• 서로 다른 특성을 같게 해버리는 위험이 있기 때문에, 우리는 using construct를 사용하여 같아야 하는 column을 특정할 수 있다.

select name, title
from (student natural join takes) join course using (course_id)

• 이렇게 하면, dept_name은 고려하지 않고, cours_id만 가지고 natural join이 가능하다

on

• on을 사용하면, join되는 relation에 대해 general predicate를 준다.
• 글자만 on이지, where 절을 사용하는 것과 동일하다

select *
from student join takes on student_ID = takes_ID

• join에 대한 조건을 주어, 이러한 pair들에 대해서만 join되게 한다

equivalent to

select *
from student, takes
where student_ID, takes_ID

---

Outer Join

• Compute the join and then adds tuples form one relation that does not match tuples in the other relation to the result of the join
• null값을 사용한다
• 3 forms of outer join
  • left outer join
  • right outer join
  • full outer join

• course relation
  

• prereq relation
  

• course relation에는 CS-347이 없고,
  prereq relation에는 CS-315가 없는 걸 확인할 수 있다.

• 이 부분은 표가 너무 많아서 ppt 보고 공부하자..

• 어쨌든, left outer join과 right outer join은 각각 왼/오른쪽에 있는 걸 기준으로 join한다. 즉, course_id column에는 기준으로 잡은 relation의 정보를 그대로 적어주고, join해서 생긴 attribute의 값이 존재하지 않으면 null을 적어준다.

• full outer join은 그 둘을 모두 기준으로 잡아서, course_id에 양쪽에 존재하는 모든 값들을 다 써준다.

---

• Join operation은 from 절의 subquery expression으로 주로 이용된다.

Join condition

: defines which tuples in the two relations match

natural
on <predicate>
using <A1, A2, ..., An>

Join type

: defines how tuples in each relation that do not match any tuple in the other relation (based on the join condition) are treated

inner join (= natural join)
left outer join
right outer join
full outer join