[DB] SQL JOIN의 모든 것: Implicit VS Explicit, Inner VS Outer, Equi, Natural, Cross Join과 예시까지!

반응형

 

 

💋 인트로

안녕하세요 깃짱입니다.

JOIN 예제를 살펴보면서 열심히 SQL 숙련자가 되어 보겠습니다.

 

소스 코드 ⇒ https://github.com/gitchan-Study/2023-sql-sample

GitHub - gitchan-Study/2023-sql-sample: 깃짱이 복잡한 SQL 명령어를 실습하기 위해 작성한 샘플 코드

 

 

이번 포스팅은 쉬운코드님의 데이터베이스 강의를 바탕으로 작성했습니다.

 

💋 데이터 소개

시작하기에 앞서, 아래 덤프 파일을 통해서 데이터를 가져올 수 있다.

가져오는 방법 ⇒ mysqldump 명령어로 데이터베이스 백업(dump)하기

 

2023_sql_sample_dump.sql

0.01MB

 

 

✔️ Employee 테이블

회사의 임직원들을 정리한 테이블이고, dept_id는 Department 테이블의 id와 대응하는 FK이다.

 

 

id 15에 GITCHAN 튜플의 dept_id가 null인 것에 주목!

 

✔️ Department 테이블

회사의 부서들을 정리한 테이블이고, leader_id는 Employee 테이블의 id와 대응하는 FK이다.

 

 

HR 부서의 leader_id가 null이고, 리더만 없는 것이 아니라, Employee 테이블을 보더라도 dept_id = 1002인 튜플이 하나도 없음에 주목!

 

 

💋 Implicit Join VS Explicit Join

✔️ Implicit Join

 

select *
from employee e,
	 department d
where e.id = 1
  and e.dept_id = d.id;

 

• from절에는 table들만 나열하고 where절에 join condition을 명시하는 방식
• old-style join syntax ⇒ 요즘은 잘 안쓰는 스타일
• where절에 selection condition과 join condition이 같이 있기 때문에 가독성이 떨어진다
• 복잡한 join 쿼리를 작성하다 보면 실수로 잘못된 쿼리를 작성할 가능성이 크다

 

⇒ 잘 안쓴다!

 

✔️ Explicit Join

 

select *
from employee e
		 join department d on d.id = e.dept_id
where e.id = 1;

 

• from절에 JOIN 키워드와 함께 joined table들을 명시하는 방식
• from절에서 ON 뒤에 join condition이 명시된다
• 가독성이 좋다
• 복잡한 join 쿼리 작성 중에도 실수할 가능성이 적다

 

💋 Inner Join VS Outer Join

✔️ Inner Join

• FROM table1 [INNER] JOIN table2 ON join_condition
• join condition에 사용 가능한 연산자는 =, <,>,!= 등등으로 다양하다.</,>
• join condition에서 null 값을 가지는 tuple은 result table에 포함되지 않는다!!!

 

select *
from employee e
		 inner join department d on e.dept_id = d.id;

 

join condition인 dept_id 값으로 null을 가지던 GITCHAN 튜플, 그리고 Employee 테이블에 포함되지 않았던 Department의 HR은 조인 테이블에 포함되지 않았다.

 

 

✔️ Outer Join

• 두 table에서 join condition을 만족하지 않는 tuple들도 result table에 포함하는 join

 

FROM table1 LEFT (OUTER) JOIN table2 ON join_condition

FROM table1 RIGHT (OUTER) JOIN table2 ON join_condition

FROM table1 FULL (OUTER) JOIN table2 ON join_ condition

 

• join condition에 사용 가능한 연산자는 =, <,>,!= 등등으로 다양하다.</,>

 

[Left Outer Join]

 

select *
from employee e
		 left outer join department d on e.dept_id = d.id;

 

왼쪽 테이블이던 Employee의 값은 모두 포함하는 JOIN이다. 따라서, GITCHAN 튜플은 오른쪽 Department과 관련된 내용이 없어서 전부 null로 채워졌다.

그치만, 오른쪽 테이블인 Department 테이블의 HR 튜플은 여전히 조인 테이블에 없다.

 

 

 

[Right Outer Join]

 

select *
from employee e
		 right outer join department d on e.dept_id = d.id;

 

왼쪽 테이블이던 Employee의 GITCHAN 튜플은 오른쪽 Department과 관련된 내용이 없어서 조인 테이블에 없다.

그치만, 오른쪽 테이블인 Department 테이블의 값은 모두 포함되어, HR 튜플은 왼쪽 테이블 내용을 null로 채웠다.

 

 

[Full Outer Join]

MySQL에서는 지원하지 않고, pqrSQL에서 지원한다고 하는데, 굳이 실습해보지는 않았다.

GITCHAN 튜플과 HR 튜플 모두 다 존재하고 각각 Department, Employee 내용은 다 null로 채워놨을 것이다.

 

 

💋 Equi Join

• join condition에서 = (equality comparator)를 사용하는 join
• 지금까지 나온 모든 쿼리들은 다 equi join이었다.

 

이 경우에, 만약에 join하는 테이블에서 연관된 attribute의 이름이 동일하다면, 아래의 두 쿼리 결과는 같게 된다. (지금은 동일하지는 않아서, 이렇게는 못쓴다.) Department에 id가 아니라, dept_id로 지정되어 있다고 하면 아래와 같이 생각할 수 있다.

 

select *
from employee e
		 inner join department d on e.dept_id = d.dept_id;

 

select *
from employee e
		 inner join department d using (dept_id);

 

✔️ USING

 

• 두 table이 equi join할 때 join하는 attribute의 이름이 같다면, USING으로 간단하게 작성할 수 있다
• 같은 이름의 attribute는 result table에서 한번만 표시 된다

 

FROM table1 (INNER) JOIN table2 USING (attribute(s))

FROM table1 LEFT (OUTER) JOIN table2 USING (attribute(s))

FROM table1 RIGHT (OUTER) JOIN table2 USING (attribute(s))

FROM table1 FULL (OUTER) JOIN table2 USING (attribute(s))

 

💋 Natural Join

 

• 두 table에서 같은 이름을 가지는 모든 attribute에 대해서 equi join을 수행
• join condition을 따로 명시하지 않아도 된다.

 

FROM table1 NATURAL (INNER) JOIN table2

FROM table1 NATURAL LEFT (OUTER) JOIN table2

FROM table1 NATURAL RIGHT (OUTER) JOIN table2

FROM table1 NATURAL FULL (OUTER) JOIN table2

 

그러면, 아래 두 쿼리는 동일한 쿼리가 된다.

 

select * from emplyee e natural inner join department d;

 

select * from emplyee e 
	inner join department d using (dept_id, name);

 

조심할 것은, name과 같이 실제로는 의미가 동일하지 않은데(임직원의 이름/부서의 이름) 조인이 되면서 아무 결과도 반환되지 않는 불상사가 발생할 수 있어서 조심해야 한다.

 

💋 Cross Join

 

• 두 table의 tuple pair로 만들 수 있는 모든 조합(= Cartesian product)을 result table로 반환한다.
• join condition이 따로 없다.
• implicit cross join: FROM table1, table2
• explicit cross join: FROM table1 CROSS JOIN table2

 

모든 경우의 수가 생긴다. 사실 implicit하게는 은근 많이 써왔던 것 같다.

 

 

💋 Cross Join in MySQL

• MySQL에서 cross join, inner join, join은 모두 같다.
• CROSS JOIN에 ON(or USING)을 같이 쓰면 inner join으로 동작한다
• INNER JOIN(or JOIN)이 ON(or USING) 없이 사용되면 cross join으로 동작한다

 

💋 실전 예시

 

• 아이디가 1003인 부서에 속하는 임직원 중 리더를 제외한 부서원의 아이디, 이름, 연봉 조회

 

select e.id, e.name, e.salary
from employee e
join department d on e.dept_id = d.id
where d.id = 1003 and d.leader_id != e.id;

 

• 아이디가 2001인 프로젝트에 참여한 임직원들의 이름과 직군과 소속 부서의 이름 조회

 

select e.name,
	   e.position,
	   (select d.name from department d where d.id = e.dept_id) as department_name
from employee e
		 join works_on wo on e.id = wo.empl_id
where wo.proj_id = 2001;

 

이렇게도 할 수 있다.

이때, employee의 dept_id가 null인 경우에도 결과를 날려먹으면 안되기 때문에 department 테이블은 left outer join하는 것이 중요하다.

 

select e.name, e.position, d.name as department_name
from employee e
		 join works_on wo on e.id = wo.empl_id
		 left join department d on d.id = e.dept_id
where wo.proj_id = 2001;

 

 

💋 참고자료

• https://www.youtube.com/watch?v=E-khvKjjVv4&list=PLcXyemr8ZeoREWGhhZi5FZs6cvymjIBVe&index=8

 

도움이 되었다면, 공감/댓글을 달아주면 깃짱에게 큰 힘이 됩니다!🌟
비밀댓글과 메일을 통해 오는 개인적인 질문은 받지 않고 있습니다. 꼭 공개댓글로 남겨주세요!