CS/DB

SQL JOIN

Ropung 2023. 10. 15. 04:34

SQL JOIN

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

SQL_JOIN.png

INNER JOIN

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

스크린샷 2023-10-15 오전 3.17.30.png

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

스크린샷 2023-10-15 오전 3.25.55.png

-- 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을 지원하지 않는다.

풀아우터조인.png

-- 대부분의 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

  • 어느 특정 테이블에 있는 레코드만 가져오는 것
    EXCLUSIVE_LEFT JOIN.png
  • 만약 테이블 두개를 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;

자체조인예시.png

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 존재 여부, 한쪽 테이블의 크기가 엄청 적은 경우 등등 많은 시나리오가 있으니 성능 비교를 하는 것이 좋다고 한다.

참고

그림 및 조인 개념 참고자료