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

SQL JOIN

JOIN은 데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현해 주는, Relation Database 에서 가장 많이 쓰이는 녀석이다.

 

 

---

(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 outer join은 1 : N 관계에서 어느 테이블을 기준으로 left할 것이냐에 따라, 레코드 갯수가 달리지게 된다. 그리고 또한 조인에 부합되지 않는 레코드는 테이블 기준에 따라 남기도 한다.

하지만 inner join 은 어느 테이블에서 기준으로 조인하든 조인 관계에 부합되는 레코드를 모두 가지게 된다. 그리고 조인에 부합되지 않는 레코드는 모두 삭제 된다.

왼쪽 외부 조인 = left outer join : 빈 필드 있어도 그냥 합쳐진다

---

LEFT / RIGHT OUTER JOIN

• 두 테이블이 합쳐 질때 왼쪽/오른쪽을 기준으로 했느냐에 따라 기준 테이블의 것은 모두 출력되어야 한다고 이해하면 된다.
• OUTER JOIN은 조인하는 테이블의 ON 절의 조건 중 한쪽의 데이터를 모두 가져온다
• OUTER JOIN은 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN 이렇게 3가지가 있다.
• LEFT OUTER JOIN을 거의 대부분 사용하여, FULL OUTER JOIN은 성능상 거의 사용하지 않는다.

​

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은 조인하는 테이블의 순서가 상당히 중요하다.
어떤 순서로 테이블을 조인하는지에 따라 결과 테이블에 조회되는 행의 개수며 구성 등이 달라질 수 있다.
따라서 JOIN 문을 작성할 때, 만약 LEFT JOIN을 할 거라면 가장 첫 번째의 테이블로 SELECT문에 가장 많은 열을 가져와야 할 테이블을 우선으로 적어준다.

2) 조인을 여러 번 해야하는데 시작을 LEFT JOIN으로 했다면 나머지 조인도 LEFT JOIN을 이어나간다.
즉, LEFT JOIN을 쓰다가 갑자기 INNER JOIN 이나 다른 조인을 사용하지 않는다는 이야기다.

 

Right 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

 

3중 조인

만일 원하는 정보가 테이블 3개로 흩어져있을때, 이 세개의 테이블을 모아야 할때 outer join을 연속으로 3번 사용하면 된다.

-- 3개의 테이블을 join하고 한국에대한 정보만 뷰로 생성해라

create view allView as 
( 
    select A.Name, A.CountryCode 
    from city A 
    left join country B 
    on A.countrycode = B.code -- 테이블 2개 조인 완료
    left join countrylanguage C 
    on B.code = C.countrycode -- 테이블 3개 조인 완료
    where A.countrycode in ('KOR'); 
)

---

​FULL OUTER JOIN

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

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

​

= (같은 구문)

(select * from topic LEFT JOIN autor on topic.auther_id = authoer.id)) 
UNION 
(select * from topic RIGHT JOIN autor on topic.auther_id = authoer.id))

* LEFT 조인한 테이블과 RIGHT조인한 테이블을 UNION 합집합 해주면 된다.

* UNION 은 DISTICT자동 포함이라, 따로 중복제거 안해줘도 자동으로 중복제거 해준다

---

UNION

• UNION은 여러 개의 SELECT 문의 결과를 하나의 테이블이나 결과 집합으로 표현할 때 사용
• 이때 각각의 SELECT 문으로 선택된 필드의 개수와 타입은 모두 같아야 하며, 필드의 순서 또한 같아야 한다.
• 기본 집합 쿼리에는 (DISTINCT) 중복제거가 자동 포함되어있다.

SELECT 필드이름 FROM 테이블이름
UNION
SELECT 필드이름 FROM 테이블이름

---

UNION ALL

• UNION은 DISTINCT 자동 포함이라 중복되는 레코드를 제거한다.
• 따라서 중복되는 레코드까지 모두 출력하고 싶다면, ALL 키워드를 사용하면 된다.

SELECT 필드이름 FROM 테이블이름
UNION ALL
SELECT 필드이름 FROM 테이블이름

---

EXCLUSIVE LEFT JOIN

• 어느 특정 테이블에 있는 레코드만 가져오는 것

​EXCLUSIVE JOIN는 만약 테이블 두개를 JOIN한다면 둘중 한가지 테이블에만 있는 데이터를 가져온다.

다른 JOIN들과는 다르게 별도의 EXCLUSIVE JOIN함수가 있는 것은 아니고 기존의 LEFT JOIN과 Where절의 조건을 함께 사용하여 만드는 JOIN 문법이다.

SELECT * 
FROM table1 A LEFT JOIN table2 B
ON A.ID_SEQ = B.ID_SEQ 
WHERE B.ID_SEQ IS NULL -- 조인한 B 테이블의 값이 null만 출력하라는 말은, 조인이 안된 A 레코드 나머지값만 출력하라는 말

---

자체 조인(SELF JOIN)

• 말 그대로 테이블 자기자신을 조인 한 것

-- 예) 모든 사원에 대해 사원의 이름과 직속 상사의 이름을 검색 해라.
-- EMPNAME 테이블에 어떤 사원의 MANAGER 번호가 같은 테이블 내에서 어떤 사원의 EMPNO와 같으면 그 사원이 직속 상관

SELECT E.EMPNAME as 사원, M.EMPNAME as 직속상관
FROM EMPLOYEE E, EMPLOYEE M -- inner join
WHERE E.MANAGER = M.EMPNO;

---

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

​

Distinct 사용

• mysql에서 지원하는 distinct 문법을 사용하면 된다.
• 매우 쉬운 방법이지만 레코드 수가 많은 경우 성능이 느리다는 단점이 있다.

 -- DISTINCT를 추가
 SELECT DISTINCT person.id, person.name, job.job_name 
 FROM person INNER JOIN job 
 ON person.name = job.person_name;

 

JOIN 전에 중복을 제거하기

• 앞서 말한 것처럼 SELECT DISTINCT는 간단하지만 성능이 느리다.
• 성능을 위해서는 JOIN 전에 중복을 제거하는 작업을 해주는 것이 좋다. (물론 더 좋은 것은 중복이 없도록 테이블 설계를 잘 하는 것이다)

JOIN 전에 중복 제거는 다음과 같이 조인할 테이블에 서브쿼리의 inline view를 사용해 distinct 하고 조인하면 된다.

select A.name, A.countryCode 
from city A 
left join ( select distinct name, Code from country ) as B 
-- 조인할 테이블에 먼저 distinct로 중복을 제거한 select문을 서브쿼리로 불러와 임시테이블로 만든뒤 조인한다
on A.countrycode = B.Code

언뜻보면 복잡해보일 수 있고 성능이 더 느려보일 수 있는데, 일반적으로 레코드 수가 많은 경우 JOIN 전에 중복을 제거해서 1:1 JOIN으로 바꾸는 것이 훨씬 빠르다.

물론 WHERE 절에 조건이 존재 여부, index 존재 여부, 한쪽 테이블의 크기가 엄청 적은 경우 등등 많은 시나리오가 있으니 성능 비교를 하는 것이 좋다.