SQL JOIN

SQL JOIN

• JOIN은 데이터 베이스 내에 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현해 주는, 관계형 디비(Relation Database)에서 많이 쓰인다.
• JOIN 관계에 부합되는 레코드들을 모두 가지게 되고, 조인에 부합되지 않는 레코드들은 모두 삭제된다.

---

INNER JOIN

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

SELECT u.userid, 이름
FROM userTB as u INNER JOIN buyTB AS b
ON u.userid = b.userid
-- join을 완료하고 그 다음 조건을 따진다고 한다.
WHERE u.userid="111"

INNER JOIN 함축 구문

• 단순히 FROM절에 콤마를 쓰면 INNER JOIN으로 치부된다고 한다.
  (DB마다 다른지는 확인이 필요하다.)

SELECT u.userid, 이름
FROM userTB u, buyTB b
WHERE u.userid=b.userid AND u.userid="111"

---

LEFT/RIGHT(OUTER JOIN)

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

---

LEFT JOIN / RIGHT JOIN

-- 1학년 학생의 이름과 지도교수명을 출력하라. 단, 지도교수가 지정되지 않은 학새도 출력되게 하라.
SELECT 학생.이름, 교수.이름
-- 학생을 기준으로 왼쪽 조인
FROM 학생 LEFT OUTER JOIN 교수
ON 학생.PID = 교수.ID
WHERE 학년 = 1

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

• INNER JOIN과는 달리 LEFT JOIN은 조인하는 테이블의 순서가 상당히 중요하다.
• 순서에 따라 테이블 조회의 행의 개수와 구성이 달라질 수도 있다.
• 따라서 SELECT문에 가장 많은 열을 가져와야 할 테이블을 우선적으로 적어주는것이 좋다.
• 조인을 여러 번 해야하는데 시작을 LEFT JOIN으로 했다면, 나머지 조인도 기본적으로 LEFT JOIN이 된다.

---

3중 조인

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

CREATE VIEW allVIEW AS
(
	SELECT 도시.이름, 도시.국가코드
	FROM city 도시
	LEFT JOIN country 국가
	-- 테이블 2개 조인완료
	ON 도시.국가코드 = 국가.코드
	LEFT JOIN countrylanguage 언어
	-- 테이블 3개 조인완료
	ON 국가.코드 = 언어.국가언어
	WHERE 도시.국가코드 IN('한국');
)

---

FULL OUTER JOIN

• 대부분의 DB는 FULL OUTER JOIN을 지원하지 않는다.

-- 대부분의 DB에서 지원하지 않음.
SELECT *
FROM topic FULL OUTER JOIN autor
ON topic.auther_id = authoer.id

UNION

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

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

---

UNION ALL

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

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

---

EXCLUSIVE LEFT JOIN

• 어느 특정 테이블에 있는 레코드만 가져오는 것
  
• 만약 테이블 두개를 JOIN한다면 둘 중 한가지 테이블에만 있는 데이터를 가져온다.
• 다른 JOIN들과는 다르게 별도의 EXCLUSIVE JOIN함수가 있는 것은 아니고 기존의 LEFT JOIN과 WHERE절의 조건을 함께 사용하여 만드는 JOIN 문법 이다.

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

---

자체 조인(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 사람.id, 사람.이름, 직업.직업명
FROM 사람, 직업
ON 사람.이름 = 직업.사람이름;

JOIN 전에 중복을 제거하기

• SELECT DISTINCT는 간단하지만 성능이 느리다.
• 성능을 위해서 JOIN전에 작업을 해주는 것이 좋다.(테이블 설계를 그전에 잘하는 것이 베스트)

-- JOIN전에 중복제거, 조인할 테이블에 서브쿼리의 inline view를 사용해 distinct하고 조인하면 된다.
SELECT 도시.이름, 도시.국가코드
FROM city 도시
LEFT JOIN (SELECT distinct 이름, 코드 FROM 국가) AS B
-- 조인할 테이블에 먼저 DISTINCT로 중복을 제거한 SELECT문을 서브쿼리로 불러와 임시테이블로 만든뒤 조인한다.
ON 도시.국가코드 = B.코드

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

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

---

참고

그림 및 조인 개념 참고자료