[Database] Join의 의미와 여러 종류의 Join

이미지 출처: https://miro.medium.com/v2/resize:fit:1400/0*gdxxtAKE9vYAjXBk.png

SQL에서 join

• join은 SQL에서 두 개 이상 테이블 데이터를 함께 조회하는 것을 말한다.
• join은 여러 종류의 join이 존재한다.

Implicit join과 Explicit join

SELECT D.name FROM employee AS E, department AS D WHERE E.id = 1 and E.dept_id = D.id;

• 위 SQL문은 implicit join을 사용한 예시이다.
• implicit join은 from 절에는 table들만 나열하고 where절에 join condition을 명시하는 방식이다.
• implicit join은 where절에 selection condition과 join condition이 같이 있기 때문에 가독성이 떨어지며, 실수할 가능성이 높아서 explicit join이 추가되었다.
• explicit join에서는 FROM절에서 ON 뒤에 join condition이 명시된다.

SELECT D.name FROM employee AS E JOIN department AS D ON E.dept_id = D.id WHERE E.id = 1;

• 이렇게 작성하면 가독성이 좋고 실수 가능성이 낮다.

Inner join

• Inner Join은 두 테이블에서 Join condition 을 만족하는 튜플들로 결과 테이블을 만든다. 이 때 null 값은 포함되지 않는다.

SELECT * FROM employee E INNER JOIN department D ON E.dept_id = D.id;

• 위 코드에서 만약 E.dept_id 또는 D.id 중 하나라도 null이면 쿼리 결과에 조회되지 않는다.
• three-valued logic에 의해 unknown은 true가 아니기 때문
• 참고로 그냥 JOIN만 적어도 INNER JOIN이다.

Outer join

• Outer join은 join condition을 만족하지 않는 튜플도 결과 테이블에 포함하는데, {left, right, full} outer join으로 구분한다.
• 참고로 OUTER 키워드는 생략 가능하다.

SELECT * FROM employee E LEFT OUTER JOIN department D ON E.dept_id = D.id;

• 위 SQL 문으로 만들어진 결과 테이블은 employee 테이블의 어트리뷰트는 모두 불러오지만, join condition에 매칭이 안된 department 어트리뷰트들은 모두 null이 된다.

SELECT * FROM employee E RIGHT OUTER JOIN department D ON E.dept_id = D.id;

• 위 SQL 문으로 만들어진 결과 테이블은 department 테이블의 어트리뷰트는 모두 불러오지만, join condition에 매칭이 안된 employee 어트리뷰트들은 모두 null이 된다.

pgrsql# SELECT * FROM employee E FULL OUTER JOIN department D ON E.dept_id = D.id;

• Mysql은 FULL OUTER JOIN을 지원하지 않아, PostgreSQL로 실행해야 한다.
• full outer join로 만들어진 결과 테이블은 join condition에 매칭이 되던 안 되던 모든 튜플들을 불러오고, 매칭이 안 된 어트리뷰트들은 모두 Null로 저장된다.

Equi join

• Equi join은 join condition에서 = 비교 연산자를 사용하는 join을 의미한다. 위에서 사용한 예시 SQL문은 E.dept_id = D.id를 사용했으므로 모두 equi join이라고 볼 수 있다.
• Equi join을 두 가지 시각으로 설명하는데, inner join outer join 상관없이 = 비교 연산자를 사용하는 경우와 inner join에서만 해당하는 경우로 구분한다.
• 조인 컨디션을 수정하고 실행한 결과를 통해 이퀴 조인의 동작 방식을 설명하며, 조인한 테이블의 결과를 확인 및 중복 여부에 대해 고민한다.
• 같은 컬럼명과 조인 컨디션을 사용하여 조인할 때, 결과가 중복되거나 동일하게 나타날 수 있어 어떻게 처리해야 하는지 고민하는 부분을 다룬다.

Using

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

SELECT * FROM employee E INNER JOIN department D ON E.dept_id = D.dept_id;

• 예를 들어 위 예시에서 dept_id가 employee, department 각 테이블에서 이름이 같게 쓰이는 경우, join 시 결과 테이블에서 똑같은 컬럼이 2개가 생긴다. 따라서 아래와 같이 using을 사용하면 중복된 행을 제거할 수 있다.

SELECT * FROM employee E INNER JOIN department D USING (dept_id);

Natural join

• 두 테이블에서 같은 이름을 가지는 모든 attribute pair에 대해서 equi join을 수행한다.
• join condition을 따로 명시하지 않는다.

SELECT * FROM employee E NATURAL INNER JOIN department D;

• 위 SQL문을 실행하면 만약 하나의 컬럼만 이름이 같으면, 그 컬럼을 기준으로 모든 attribute pair에 대해 equi join을 수행한다.
• 만약 dept_id가 employee, department에 모두 있다면 아래 쿼리와 실행 결과가 같다.

SELECT * FROM employee E INNER JOIN department D USING (dept_id);

• 다만, 여러 컬럼의 이름이 같으면 빈 집합을 리턴한다.

Cross join

• Cross join은 두 테이블의 모든 조합을 표현하고 결과 테이블로 반환한다.
• join condition 없이 두 가지 방법으로 나뉘며, implicit cross join과 explicit cross join이 있다.

SELECT * FROM employee CROSS JOIN department;

• 위 쿼리를 실행시키면 두 테이블간 가능한 모든 조합을 표현한다. 마치 순열로 모든 경우의 수를 구하는 것과 같다. 따라서 null 값이 있을 수 있다.
• MySQL에서는 cross join, inner join과 join이 모두 같은 의미로 사용된다.

Self join

• Self join은 자기 자신에게 조인을 하는 경우이다.

join 예시

ID가 1003인 부서에 속하는 임직원 중 리더를 제외한 부서원의 ID, 이름, 연봉을 알고 싶다.

SELECE E.id, E.name, E.salary FROM employee E JOIN department D ON E.dept_id = D.id
WHERE E.dept_id = 1003 and E.id != D.leader_id;

ID가 2001인 프로젝트에 참여한 임직원들의 이름과 직군과 소속 부서 이름을 알고 싶다.

SELECE E.name, E.position, D.name 
FROM works_on W JOIN employee E W.empl_id = E.id LEFT JOIN department D ON E.dept_id = D.id
WHERE W.proj_id = 2001;

• left join은 null 값이 있어도 결과를 유지할 수 있어 필요하다.

참고

• https://youtu.be/E-khvKjjVv4?si=4Htscumy2k-yZw0i