조인

Join이란?

한 테이블의 행을 다른 테이블의 행에 연결하여 두 개 이상의 테이블을 결합하는 연산이다.

아래 SQL 문은 두 개의 테이블을 아무 조건 없이 연결한 예다.

SELECT * FROM 테이블1, 테이블2

두 테이블을 아무런 조건 없이 SELECT 시키면 관계대수의 카티션 프로덕트 연산이 된다.

기본 구조

일반 
SELECT 테이블.컬럼, 테이블.컬럼 
FROM 테이블1,테이블2 
WHERE 조건 

Join 의 종류

• Inner Join
• Natural Join
• Outer Join
• Left Join
• Right Join
• Cross Join

INNER JOIN

조인하는 테이블의 ON 절의 조건이 일치하는 결과만 출력합니다. 표준 SQL 과는 달리 MySQL에서는 JOIN, INNER JOIN, CROSS JOIN 이 모두 같은 의미로 사용됩니다.

select u.userid, name 
from usertbl as u inner join buytbl as b 
on u.userid=b.userid 
where u.userid="111" -- join을 완료하고 그다음 조건을 따진다.

inner join 함축 구문

단순히 from 절에 콤마 쓰며 inner join으로 치부됩니다.

select u.userid, name 
from usertbl u, buytbl b 
where u.userid=b.userid and u.userid="111"

LEFT JOIN

LEFT JOIN은 두 테이블이 있을 경우, 첫 번째 테이블을 기준으로 두 번째 테이블을 조합하는 JOIN입니다.

-- 예) 1학년 학생의 이름과 지도교수명을 출력하라. 단, 지도교수가 지정되지 않은 학생도 출력되게 하라.

SELECT STUDENT.NAME, PROFESSOR.NAME 
FROM STUDENT LEFT OUTER JOIN PROFESSOR -- STUDENT를 기준으로 왼쪽 조인
ON STUDENT.PID = PROFESSOR.ID 
WHERE GRADE = 1

LEFT JOIN을 여러번 할 때 주의할 점

1. INNER JOIN과는 달리 LEFT JOIN은 조인하는 테이블의 순서가 상당히 중요합니다. 어떤 순서로 테이블을 조인하는지에 따라 결과 테이블에 조회되는 행의 개수며 구성 등이 달라 질 수 있습니다.
2. 조인을 여러번 해야 하는데 시작을 LEFT JOIN으로 했다면 나머지 조인도 LEFT JOIN을 이어나갑니다. 즉, LEFT JOIN을 쓰다가 갑자기 INNER JOIN이나 다른 조인을 사용하지 않는다는 이야기입니다.

left outer join 예시

즐겨찾기 장소 관계에서 사용자가 즐겨찾기 등록한 장소를 그렇지 않은 장소와 함게 조회해서 별도로 표시할 경우

장소 기준 left outer join 즐겨찾기 형태로 처리해야 원래 보여주려던 장소 목록을 출력할 수 있다.

가령 장소가 100만개, 즐겨찾기 1000개

select ...
from 장소 left outer join 즐겨찾기
where.....

select ... (화면에 보여줄 10개의 row 만 먼저 추출)
from 장소
where ....

select ....(장소)
from 즐겨찾기
where 고객 = 로그인한 고객
각각 별도로 추출한 후 left outer join

• 화면에 보여 줄려는게 장소 10개라고 해보자 장소에 대해서 레프트 아우터 조인을 해야지만 장소 10개를 모두 보여줄 수 있다.
  • (inner join 하면 즐겨찾기한 장소만 가져오게된다.)
• 하지만 바로 이렇게 해버리면 성능에 문제가 생길 수 있다
  • (장소가 100만개 -> 100만개에 대해 1000개에 대한 카티션 프로덕트가 생겨버림)
  • 겨우 10개를 가져오기 위해서 100만개에 대한 left outer join을 하면 안됨
• 그래서 장소에 대해 서브쿼리를 해서 먼저 보여줄 10개를 먼저 추출한다
• 그리고 즐겨찾기도 로그인한 사용자의 즐겨찾기만 가져오는 식으로 데이터를 줄인다.
• 위 과정을 통해 데이터를 줄인 다음에 left outer join을 하면 된다.
• 모든 장소에 대해서 모든 즐겨찾기를 조회하지 말고 현재 장소 사용자의 즐겨찾기에서 조회를 하라는거다
• from 쪽에 서브쿼리를 둔 이유는 모든 쿼리문의 첫번째 시작은 from에서 시작한다.
• 그래서 from 쪽에 서브쿼리를 두지 않는다면 100만 * 1000의 카티션 프로덕트가 발생하기에 이걸 줄이기 위해서 from 쪽에 서브쿼리를 둬서 미리 조회할 데이터를 줄인거다.

RIGHT OUTER JOIN

RIGHT JOIN은 두 테이블이 있을 경우, 두 번째 테이블을 기준으로 첫 번째 테이블을 조합하는 JOIN 입니다.

-- 예) 1학년 학생의 이름과 지도교수명을 출력하라. 단, 지도교수가 지정되지 않은 학생도 출력되게 하라.

SELECT STUDENT.NAME, PROFESSOR.NAME 
FROM STUDENT RIGHT OUTER JOIN PROFESSOR -- PROFESSOR를 기준으로 오른쪽 조인
ON STUDENT.PID = PROFESSOR.ID 
WHERE GRADE = 1

FULL OUTER JOIN

select * 
from topic FULL OUTER JOIN autor 
on topic.auther_id = authoer.id

대부분 DB는 FULL OUTER JOIN을 지원하지 않는다. 하지만 간접적으로 구현하는 방법이 존재한다.

UNION을 이용한 방법

FULL OUTER JOIN을 수행하기 위해 LEFT JOIN과 RIGHT JOIN을 결합하는 방법입니다. 이는 두 테이블 간의 모든 레코드를 결합하고, 중복된 레코드를 제거하여 FULL OUTER JOIN과 동일한 결과를 얻을 수 있습니다.

SELECT *
FROM TableA AS A
LEFT JOIN TableB AS B ON A.id = B.id

UNION

SELECT *
FROM TableA AS A
RIGHT JOIN TableB AS B ON A.id = B.id;

UNION과 COALESCE를 이용한 방법

UNION과 COALESCE 함수를 사용하여 더 정교한 방법으로 FULL OUTER JOIN을 구현할 수 있습니다.

COALESCE 함수는 NULL 값을 대체할 때 유용하게 사용됩니다.

SELECT
    COALESCE(A.id, B.id) AS id,
    A.column1 AS A_column1,
    B.column1 AS B_column1
FROM TableA AS A
FULL OUTER JOIN TableB AS B ON A.id = B.id
WHERE A.id IS NOT NULL OR B.id IS NOT NULL;

LEFT JOIN과 RIGHT JOIN을 사용한 방법

별도로 LEFT JOIN과 RIGHT JOIN을 수행하고, 이를 조합하여 FULL OUTER JOIN을 에뮬레이션하는 방법입니다.

-- LEFT JOIN 결과
SELECT A.id, A.column1, B.column1
FROM TableA AS A
LEFT JOIN TableB AS B ON A.id = B.id

UNION ALL

-- RIGHT JOIN 결과 중 LEFT JOIN에서 포함되지 않은 결과
SELECT B.id, A.column1, B.column1
FROM TableA AS A
RIGHT JOIN TableB AS B ON A.id = B.id
WHERE A.id IS NULL;

이 방법들은 대부분의 DBMS에서 FULL OUTER JOIN을 지원하지 않더라도 동일한 결과를 얻을 수 있게 해줍니다.

사용 시, 각 방법의 성능과 최적화를 고려하여 선택하는 것이 좋습니다.

JOIN 에서 중복된 레코드 제거하기

SQL에서 JOIN 연산을 수행할 때 중복된 레코드를 제거하는 방법은 여러 가지가 있습니다. 중복 레코드를 제거하려면 DISTINCT 키워드를 사용하거나 GROUP BY 절을 사용할 수 있습니다. 이 포스팅에서는 DISTINCT를 통해 중복 제거를 하는 과정을 설명하도록 하겠습니다.

DISTINCT를 사용하여 중복 제거

DISTINCT 키워드는 결과 집합에서 중복된 레코드를 제거하는 데 사용됩니다. 이 방법은 JOIN 결과에서 중복된 모든 열 값을 제거합니다.

SELECT DISTINCT A.column1, A.column2, B.column3
FROM tableA A
JOIN tableB B ON A.common_column = B.common_column;

이 쿼리는 tableA와 tableB를 JOIN 한 후 중복된 레코드를 제거합니다.

정리

데이터베이스에서 조인은 데이터를 효율적으로 결합하고 분석하는 데 필수적인 도구입니다.

이번 글에서는 INNER JOIN, OUTER JOIN, UNION, UNION ALL, DISTINCT 등 다양한 조인 방식과 연산자에 대해 살펴보았습니다.

요약 정리

• INNER JOIN:
  • 두 테이블 간의 공통된 레코드만 반환합니다. 데이터 일치가 중요한 경우에 유용합니다.
• OUTER JOIN:
  • LEFT, RIGHT, FULL OUTER JOIN을 포함하며, 일치하지 않는 레코드도 포함할 수 있습니다.
  • 데이터의 전체적인 맥락을 파악할 때 도움이 됩니다.
• UNION과 UNION ALL:
  • 여러 SELECT 쿼리의 결과를 하나의 결과 집합으로 결합합니다.
  • UNION은 중복을 제거하고, UNION ALL은 중복을 포함하여 모든 결과를 반환합니다.
• DISTINCT:
  • 결과 집합에서 중복된 레코드를 제거하는 데 사용됩니다.
  • 중복을 방지하고 고유한 데이터를 추출할 때 유용합니다.

이러한 다양한 조인과 연산자를 이해하고 적절하게 활용함으로써, 데이터베이스 쿼리를 최적화하고 원하는 결과를 효율적으로 얻을 수 있습니다. 실무에서는 상황에 따라 가장 적합한 조인 방식을 선택하여 사용해야 합니다.

참고

[MYSQL] 📚 테이블 조인(JOIN) - 그림으로 알기 쉽게 정리

SQL 기본 문법: JOIN(INNER, OUTER, CROSS, SELF JOIN)