[SQL] 쉬워지는 JOIN 1편 - INNER, OUTER, CROSS

하나의 테이블 만으로 원하는 결과를 얻을 수 있다면 정말 좋겠지만, 우리는 대체로 아주 복잡한 서비스를 구축하고 사용합니다.

… 그렇다면 당연히 테이블의 개수도 늘어나고 복잡해지겠죠.

간단한 서비스라고 하더라도 유지보수, 중복방지 등의 목적으로 테이블을 분리해서 사용하기도 합니다.(정규화)

반대로 성능을 목적으로 테이블을 병합하는 등 중복된 데이터를 하용하기도 하죠(반정규화)

우리는 정규화를 통해 나눠진 테이블 간의 조합을 통해 원하는 결과를 도출해낼 수 있습니다.

바로, 조인(JOIN)이라는 방법으로 말이죠!

여러분들이 조금의 SQL 공부를 하셨다면 기본적으로 JOIN의 종류를 알고 계실 것입니다.

JOIN은 두 개의 테이블을 서로 묶어서 원하는 결과를 도출해내는 것을 말합니다.

기본적으로 알고있는 JOIN의 종류는 아래와 같습니다.

OverView

• INNER JOIN: 두 테이블 간, 공통으로 존재하는 데이터를 기반으로 행을 결합
  • EQUAL-JOIN: 일반적인 INNER JOIN → 두 테이블의 열이 같은 값을 가지는 경우
  • NON-EQUAL-JOIN: 두 테이블의 열이 특정 관계를 만족하는 경우(크기 비교)
  • NATURAL JOIN: 공통 열을 자동으로 찾아서 JOIN 수행
  • SELF JOIN: 같은 테이블 내에서 자신을 참조하여 JOIN 수행
  • SEMI JOIN: 한 테이블의 특정 조건을 만족하는 행과 매칭되는 다른 테이블의 일부만을 반환
  • ANTI JOIN: 한 테이블의 특정 조건을 만족하지 않는 행을 기준으로 다른 테이블에서 관련 없는 데이터를 반환
• OUTER JOIN
  • Left OUTER JOIN: 왼쪽 테이블을 기준으로 조인 조건에 부합하지 않는 오른쪽 테이블의 행은 NULL로 반환.
  • Right OUTER JOIN: 오른쪽 테이블을 기준으로 조인 조건에 부합하지 않는 왼쪽 테이블의 행은 NULL로 반환.
  • FULL OUTER JOIN: 양 테이블을 모두 기준으로 하여 서로 조인 조건에 부합하지 않는 행은 NULL로 반환
• CROSS JOIN: 두 테이블의 행을 모두 조합하여 결과를 반환
• SELF JOIN (보통 INNER JOIN이나 OUTER JOIN의 특수한 경우로 포함됨)
• JOIN 기술
  • HASH JOIN: 해시 테이블을 사용하여 JOIN을 수행합니다. 대규모 데이터에 효율적
  • MERGE JOIN: 두 테이블의 데이터를 정렬하여 병합하는 방식으로 JOIN을 수행
  • NESTED LOOP JOIN: 한 테이블의 각 행에 대해 다른 테이블의 모든 행을 순차적으로 비교하여 JOIN을 수행

너무 많은 종류의 조인이 있죠..? 참 어렵습니다ㅠ

그렇기 때문에 하나씩 자세히! 알아가보겠습니다.

오늘 포스팅에서 알아 볼 JOIN은

• INNER
• OUTER
• CROSS

입니다!

다루는 내용이 많은 만큼 글이 길어질 예정이기 때문에 상단 혹은 측면(아래 사진 참고)의 목차를 잘 활용해주시기 바랍니다!

예시 테이블

Students(학생 테이블)

student_id	name
1	Alice
2	Bob
3	Charlie

Enrollments(학생별 등록 강좌 테이블)

student_id	course
1	Math
1	Science
2	Art

INNER JOIN

💡 두 테이블 간에 공통된 값을 가진 행만을 반환하는 조인 방식.
즉, 두 테이블에서 조인 조건에 만족하는 행만 결과에 포함됩니다.

🧐예시를 통해 알아보는 INNER JOIN

명시적 사용

SELECT Students.name, Enrollments.course
FROM Students
INNER JOIN Enrollments ON Students.student_id = Enrollments.student_id;

암시적 사용

SELECT S.name, E.course
FROM Students S, Enrollments E
WHERE S.student_id = E.student_id;

결과 ⤵️

name	course
Alice	Math
Alice	Science
Bob	Art

해설

INNER JOIN은 Students와 Enrollments 테이블에서 student_id가 일치하는 행만 반환합니다. 따라서, 등록되지 않은 학생인 Charlie는 결과에 포함되지 않습니다.

 

 

---

 

OUTER JOIN

OUTER JOIN은 처음 접하는 분들은 어렵게 생각하지만 이름에서 쉽게 알아볼 수 있습니다!

💡 OUTER JOIN으로 알아보는 Oracle SQL의 고유 문법

Oracle SQL은 (+)기호를 사용해서 OUTER JOIN을 표기하는데요.
여겨서 (+)기호가 붙은 쪽이 ‘외부’를 뜻합니다.

즉, (+)기호가 붙은 테이블은 ‘기준’이 아니라는 것이죠.
OUTER JOIN에서는 기준 테이블의 모든 행을 반환하고 조인 조건에 부합하는 외부 테이블의 데이터를 조합해서 표시합니다.

때문에 (+)가 붙은 테이블이 ‘외부’테이블이므로,
LEFT OUTER JOIN이면 오른쪽에 (+)기호를 RIGHT OUTER JOIN이면 왼쪽에 (+)기호를 붙여야 하는 것이죠!

LEFT OUTER JOIN : 왼쪽 테이블 데이터를 모두 반환!

LEFT OUTER JOIN은 왼쪽 테이블의 모든 행을 반환하고, 오른쪽 테이블에서 일치하는 값이 없으면 NULL을 반환합니다.

ANSI 표준 문법

SELECT Students.name, Enrollments.course
FROM Students
LEFT OUTER JOIN Enrollments 
ON Students.student_id = Enrollments.student_id; -- JOIN 조건: 학번이 일치하는 학생들으 정보를 반환

Oracle SQL 고유 문법

SELECT S.name, E.course
FROM Students S, Enrollments E
WHERE S.student_id = E.student_id(+);

결과 ⤵️

name	cource
Alice	Math
Alice	Science
Bob	Art
Charlie	NULL

해설

LEFT OUTER JOIN은 Students 테이블의 모든 학생을 반환하고, 각 학생이 등록한 수업을 표시합니다. 등록되지 않은 학생 Charlie의 경우, course 열에는 NULL이 표시됩니다.

RIGHT OUTER JOIN : 오른쪽 테이블의 데이터를 모두 반환!

RIGHT OUTER JOIN은 오른쪽 테이블의 모든 행을 반환하고, 왼쪽 테이블에서 일치하는 값이 없으면 NULL을 반환합니다.

ANSI 문법

SELECT Students.name, Enrollments.course
FROM Students
RIGHT OUTER JOIN Enrollments ON Students.student_id = Enrollments.student_id;

Oracle SQL 문법

SELECT S.name, E.course
FROM Students S, Enrollments E
WHERE S.student_id(+) = E.student_id;

결과

name	cource
Alice	Math
Alice	Science
Bob	Art

해설

RIGHT OUTER JOIN은 Enrollments 테이블의 모든 수업을 반환하고, 해당 수업에 등록된 학생을 표시합니다.

아쉽게도, 제가 준비한 예시에서는 Students 테이블에 있는 모든 학생이 Enrollments 테이블에 있는 수업에 등록되어 있으므로 결과가 INNER JOIN과 같습니다.

FULL OUTER JOIN : 양 테이블의 데이터를 모두 반환!

FULL OUTER JOIN은 두 테이블의 모든 행을 반환하되, 일치하는 값이 없을 경우, 해당 필드를 NULL로 표시합니다.

가능한 모든 데이터가 반환되고, 공통된 값이 없는 경우에도 데이터를 반환합니다.

ANSI 문법

SELECT Students.name, Enrollments.course
FROM Students
FULL OUTER JOIN Enrollments ON Students.student_id = Enrollments.student_id;

Oracle SQL 문법

Oracle의 (+)표기법은 FULL OUTER JOIN을 지원하지 않습니다.

💡 왜 지원하지 않을까요?

FULL OUTER JOIN은 모든 행을 포함하여 조인 조건에 합당하지 않은 행은 NULL로 표시되기 떄문에, (+)표기법으로는 표현할 수 없습니다.

⇒ Oracle SQL에서 외부 테이블을 표시하기 위해 (+)기호를 사용하는데, FULL OUTER JOIN은 양쪽 다 기준 테이블이기 때문에 외부 테이블이 존재하지 않습니다.

…때문에 !
⇒ Oracle SQL에서 ANSI 문법을 사용하지 않고 FULL OUTER JOIN을 구현하기 위해서는 LEFT OUTER JOIN의 결과와 RIGHT OUTER JOIN의 결과를 중복을 제외하고 병합해야 합니다.

 

SELECT S.name, E.course
FROM Student S, Enrollments E
WHERE S.student_id = E.student_id(+)
UNION
SELECT S.name, E.course
FROM Student S, Enrollments E
WHERE S.student_id(+) = E.student_id;

결과 ⤵️

name	cource
Alice	Math
Alice	Science
Bob	Art
Charlie	NULL

해설

FULL OUTER JOIN은 두 테이블의 모든 행을 포함합니다.

이 예시에서는 Students 테이블에서 모든 학생을 표시하고, 각 학생이 등록한 수업을 표시합니다.

등록되지 않은 학생 Charlie와 같은 경우, course 열에는 NULL이 표시됩니다.

---

CROSS JOIN

CROSS JOIN은 두 테이블에서 가능한 모든 조합을 반환합니다.

모든 조합을 반환하기 때문에 당연히 조인 조건은 필요없겠죠?

명시적 사용

SELECT Students.name, Enrollments.course
FROM Student
CROSS JOIN Enrollments;

암시적 사용

SELECT S.name, E.course
FROM Student S, Enrollments E;
-- INNER JOIN의 암시적 사용 문법에서 조인 조건만 빼주면 CROSS JOIN 문법이다.

결과 ⤵️

name	cource
Alice	Math
Alice	Science
Alice	Art
Bob	Math
Bob	Science
Bob	Art
Charlie	Math
Charlie	Science
Charlie	Art

해설

CROSS JOIN은 Students와 Enrollments의 모든 조합을 포함하여 결과를 반환합니다.

즉, 각 학생과 각 수업의 모든 가능한 조합이 포함됩니다.