8.SQL로 데이터 조회하기(SQLでデーター照会）PART 4

JOIN

 

implicit JOIN vs emplicit JOIN

 

WHERE절 뒤의 E.dept_id = D.id라는  JOIN condition이 있다.

이걸로 eployee 테이블과 department 테이블을 JOIN시켜 준다.

위와 같이 WHERE절 뒤에서 JOIN condition에 의해서 테이블을 JOIN시켜주는 것을 implicit JOIN이라고 한다. 

그러나 implicit JOIN 방식은 가독성이 떨어 진다.

explicit JOIN 방식으로 아래와 같이 고칠 수가 있다.

 

inner JOIN vs outer JOIN

inner JOIN

아래의 그림에서는 inner JOIN과 outer JOIN의 설명을 쉽게 하기 위하여 SQL문을 약간 수정을 하였다. 

사실 JOIN이라는 키워드는 앞에 inner가 생략이 돼 있는 것이다. 고로 JOIN -> inner JOIN으로 바꾸었다.

inner join을 본격적으로 설명하기에 앞서 먼저 아래의 2가지를 기억해 놓자( 그 이유는 차후에 밝혀짐 )

1. Employee 테이블의 6번째 tuple의 dept_id가 NULL이라는 점

2. Department 테이블의 2번째 tuple의 name Attribute, 즉 HR이라는 부서에 소속돼 있는 임직원이 한 명도 없다는 

점.(왜냐하면, HR의 부서id는 1002인데, Employee 테이블에서 1002의 부서id를 가지는 임직원이 없기 때문)

[inner] JOIN의 결과

위 SQL문은 join condition, 즉 E.dept_id = D.id의 결과가 true인 경우에만 tuple을 join시킨다.

join table을 한 번 살펴보자

우선 employee table의 6번째 tuple이 존재하지 않는다.

왜?WHY?

-> "E.dept_id = D.id"에서 E.dept_id의 값은 NULL이다. three-value-logic에서 살펴 보았듯이

NULL = D.id의 결과값은 UNKOWN이다. 즉 join condition의 결과 true가 아니므로 join이 되지 않은 

것이다. 

그 다음, Department 테이블의 2번째 tuple도 join table에 존재하지 않는다.

왜?WHY?

-> "E.dept_id = D.id"에서 D.id = 1002이다.

그러나 Employe table에서 E.dept_id = 1002를 만족하는 tuple들이 하나도 없기 때문에 join condition은 

false를 반환을 하게 된다. 그래서 Department 테이블의 2번째 tuple이 JOIN이 되지 않은 것이다.

[inner] JOIN의 정리

 

outer JOIN

 

주의 : FULL OUTER JOIN은 Mysql에서는 지원하지 않는 SQL문법이며, Postgre SQL에서는 지원을 한다. 

 

 

위 그림을 보면 inner join에서는 얻을 수 없었던 Employee table의 6번재 tuple도 join table에 포함돼 있는 것을 알 수가 있

다.  LEFT OUTER JOIN에서 LEFT는 FROM뒤의 table1과 table2, 2개의 table중 왼쪽 즉 table1인 Employee table을 가리

키고 있다. 그러므로, join condition을 만족하지 않지만 해당 tuple이 join이 돼, joined table에 포함이 돼 있는 것이다. 

 RIGHT가 Department TABLE을 뜻함을 알고 있으면, 위 joined table의 결과를 이해할 수가 있다. 

FULL OUTER JOIN은 Mysql에서는 지원이 되지 않는 것을 기억해 놓자(그래서 Postgre SQL에서 실행을 시켰다.)

FULL OUTER JOIN의 실행 결과

equi JOIN

join conditino에서 '='(equality comparator)을 사용하는 JOIN.

위 정의에 의해 아래의 case의 JOIN의 경우 equi join이라고 할 수 있다.

equi JOIN의 예시

 

equi join에 대한 두 가지 시각

1. inner join이든 outer join이든 상관없이, join condition에서 ' = ' 를 사용했다면 equi JOIN라고 부르는 시각

2. inner join에 한정하여 ' = ' 을 사용했을 경우에만 equi JOIN라고 부르는 시각

(여기서는 1의 시각을 따르도록 하겠다.)

 

USING

USING을 쉽게 설명하기 위하여 아래와 같이 Department의 id를 dept_id라는 이름으로 바꾸었다고 가정할 것이다.

 

위 SQL문의 실행 결과이다.

근데, 여기서 두 가지 거슬리는 것이 있다.

1. joined table을 보면 dept_id Column이 2개로 중복되서 나온다. 

2. ON뒤의 Join condition을 보면 똑같은 이름인데 dept_id를 2번씩이나 써 줘야 한다는 것이다.

Solution : Using 키워드를 사용하면 위 2가지 문제점을 해결이 가능하다.(아래 참조)

USING 키워드를 사용하면, 위 그림과 같이 이름이 Attribute이름이 같은 tuple들은 별도의 table로써 따로 빼준다.

고로, 위 그림은 [Using Tuples] + [Eployee Tuples] +[Department Tuples], 이 3개로 구성이 되어 있는 것이다. 

 

USING 정리

 

Natural JOIN

Natural join을 쉽게 설명하기 위해 아래에서는 Department.name를 Department.dept_name으로 바꾸어 주었다.(변경한 이유가 매우매우 중요!! 아래에서 자세히 설명을 할테니 지금은 그냥 넘어가자)

Employee의 dept_id와 Department의 dept_id는 서로 Attribute명이 같으므로, Natural Join의 실행 결과가 위와 같이 나온

다.( USING에 의해서, dept_id라는 tuple들의 집합이 employee , department tuple들의 집합과는 별도로 따로 맨 앞으로 빠

져 있다.)

 

자! 우리는 위 예제들을 설명하기 위하여 Department.name을 Department.dept_name으로 바꾸어 주었었다. 그러나 이제 

다시 Department.name으로 바꿔서 Natural JOIN에 대해서 생각해 보자.

SQL V0

SQL V1

SQL V2

 

SQL V0의 쿼리는 SQL V1, SQL V2와 같이 치환을 할 수가 있다. 

그러나 여기서 심각한 문제점이 있다.

Q.위 SQL V0, SQL V1, SQL V2를 실행하면 우리가 예상한 동작이 이루어 질까?????

A. Nope!!! Empty Set이 출력이 된다. 

왜냐하면 Employee.id = Department.id에서는 별 문제가 없지만, Employee.name=Department.name에서 문제가 발

생을 한다.  Employee.name은 임직원의 이름이고, Department.name은 부서의 이름이다. 같은 name이 있을 리가 없다.

고로, 그 어떠한 tuple들이 선택되지 않게 되어, 아무것도 반환하지 않는 "Empty Set"상태가 된다.

Solution : Cross Join(아래에서 설명함)

 

Cross Join( "세타 Join "이라고도 부름 )

Cross JOIN : (Employee의 tuples) x (Department의 tuples) -> Cartesian Product

Employee의 tuple과 Department의 tuple들의 모든 가능한 조합들이 각각 1개의 tuple들로 JOIN되었다.

 

MySQL에서 사용하는 Cross JOIN은 SQL 표준의 Cross JOIN과는 조금 다르다.

MySQL에서의 Cross JOIN은 다음과 같다. 

Cross JOIN = [inner] JOIN

여기서 의문점이 든다. 

위 inner JOIN예제, JOIN예제에서의 실행 결과와 Cross JOIN의 실행 결과가 다른데 왜 이 3개의 키워드의 기능이 같다고

하는 걸까??

그 이유는 바로 USING 키워드와 ON 키워드에 있다.

1.Cross JOIN 사용 시, USING 키워드나 ON 키워드를 사용하면 Cross JOIN은 [inner JOIN]으로 동작하게 된다.

2.[inner] JOIN 사용 시, USING 키워드나 ON 키워드를 사용하지 않으면 [inner] JOIN은 Cross JOIN으로 동작하게 된다. 

-> USING, ON 키워드 사용 여부에 따라, [inner] JOIN이 Cross JOIN가 될 수도 있고, Cross JOIN이 [inner] JOIN으로 될 수

있기에, Cross JOIN = [inner] JOIN은 성립되는 말이다. 

그러나 SQL 표준에서는 

1. Cross JOIN 사용 시 , USING 키워드 ON 키워드 사용 금지.

2. [inner] JOIN 사용 시, USING 키워드 ON 키워드 무조건 사용해야 함.

 

Self JOIN

Table이 자기 자신에게 join하는 경우!!

 

자 이제부터는 우리가 여태껏 배운 join들을 이용하여 예제 2개를 풀어 보자.

SQL 작성 TIP!!

1.결국에 SQL문의 핵심은 JOIN한 tuples들에서 우리가 알고 싶어하는 attribute인 projection attribute를 출력시키는 것

이다. JOIN 키워드를 적극적으로 사용하라!

2. WHERE절 뒤에는 selection condition만 적고, join condition은 FROM뒤에 적자!!!

예제1

예제1

예제2

예제2