[ᴘʀᴏɢʀᴀᴍᴍᴇʀꜱ] SQL - 테이블 합치기(JOIN)

NewHa·2023년 11월 5일

ᴘʀᴏɢʀᴀᴍᴍᴇʀꜱ

목록 보기

7/8

☀️ JOIN

SELECT 컬럼명 
FROM 테이블1 AS A 
	< INNER | LEFT | RIGHT | FULL > JOIN 테이블2 AS B 
	-- 👆🏻 가독성을 위해 보통 테이블 명은 alias를 사용해 간단하게 표현해 사용합니다.
	ON A.foreign_key = B.id_key
	-- 👆🏻 조인 조건으로 A테이블의 외래키를 B테이블이 id_key라는 컬럼명으로 참조하고 있을 때,
	-- ON 으로 외래키와 참조키를 설정해주면, 두 컬럼을 비교해서 같은 값끼리 하나의 row로 나타내줍니다.
[WHERE 조건식]
[GROUP BY 컬럼명 혹은 표현식\
[HAVING 조건식]
[ORDER BY 컬럼명]

'연결하다, 합치다'라는 뜻으로 서로 다른 테이블을 합쳐서 하나의 테이블로 보이게 합니다. 관계형 데이터베이스에서 데이터들은 모두 관게를 맺고 있고, JOIN 함수를 통해 1 : N 의 관계를 나타낼 수 있습니다. 서로 다른 테이블 여러 개도 JOIN할 수 있습니다.

🫥 프로그래머스 SQL 코딩테스트 - 저자 별 카테고리 별 매출액 집계하기 (lv.4)

테이블 3개를 JOIN하여 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(TOTAL_SALES = 판매량 * 판매가) 을 구하여, 저자명, 카테고리, 매출액리스트를 출력하는 SQL문을 작성하는 문제입니다.
SELECT A.author_name, B.category, sum(S.sales * B.price) AS total_sales
FROM book AS B 
	INNER JOIN book_sales AS S 
    	ON B.book_id = S.book_id
	INNER JOIN author AS A 
    	ON B.author_id = A.author_id
GROUP BY A.author_id, B.category

JOIN을 사용할 때는 분석하여 조회하고자 하는 대상과 목표를 정확히 해야합니다.
SELECT로 조회할 컬럼명이 테이블1과 테이블2에 모두 있는 겹치는 이름이라면 점표기법을 사용해 테이블명.컬럼명으로 명시해주어야 합니다. 이때 테이블명은 alias로 붙인 별칭으로 대신할 수 있습니다.

🪴 외래키 (Foreign Key)

테이블 간의 연결고리를 외래키라고 합니다. 다른 테이블의 특정 row를 식별하게 하는 컬럼이면서 해당 테이블에서 참조하고 있는 관계에 있는 컬럼을 말합니다.

참조하는 테이블은 자식테이블이라고 부르고, 참조를 당하는 테이블은 부모테이블이라고 부릅니다.

다른 테이블의 특정 row를 식별할 수 있어야 하므로 주로 primary key를 참조하는 경우가 많습니다.

외래키를 설정하면 테이블이 이상한 id값을 추가하려고 시도하면 에러를 발생시켜서 엉뚱한 row가 추가되는 것을 방지할 수 있습니다.

외래키가 아닌 컬럼을 기준으로도 조인 조건을 지정할 수 있습니다. (이 경우 JOIN 종류별로 결과가 모두 달라지게 됩니다.)

🪴 Alias

컬럼의 alias와 테이블의 alias에는 약간의 용도 차이가 존재합니다.

컬럼의 alias : 각 컬럼 이름이 실제로 테이블에 변환되어 보여지게 하기 위한 용도입니다.

테이블의 alias : SQL문의 전체 길이를 줄여서 코드의 가독성을 높이기 위한 용도로 실제로 테이블에서 보여지지 않습니다.

🌿 JOIN의 종류

INNER JOIN

가장 많이 사용되는 조건입니다. 기준이 되는 테이블이 따로 없고 각 테이블에서 조인 조건으로 사용된 컬럼(ON으로 설정한 컬럼들)에 값이 둘 다 존재하는 경우, 즉 지정 조건을 만족하는 칼럼만 합칩니다. 따라서 기준 컬럼이 null값이 되는 경우가 없습니다. (쉽게 말해 테이블1과 테이블2의 교집합만 테이블로 만드는 것과 같습니다.)

대부분의 JOIN문제는 INNER JOIN으로 해결할 수 있습니다.

🫥 프로그래머스 SQL 코딩테스트 - 조건에 맞는 도서와 저자 리스트 출력하기 (lv.2)

어느 한 서점에서 판매중인 도서들의 도서 정보(BOOK)테이블과 저자 정보(AUTHOR) 테이블에서 '경제' 카테고리에 속하는 도서들의 도서 ID, 저자, 출판일 리스트를 출력하는 문제입니다.
SELECT B.book_id, A.author_name, B.published_date
FROM book B
  INNER JOIN author A
  ON B.author_id = A.author_id
WHERE B.category = '경제';

OUTER JOIN

INNER JOIN이 교집합적인 느낌이었다면, OUTER JOIN은 합집합 적인 성격을 띕니다. 조인 조건을 만족하지 않는 데이터까지 출력할 때 사용됩니다.

LEFT OUTER JOIN : 테이블1을 기준(LEFT OUTER)으로 테이블2를 합치게 됩니다. 쉽게 표현하자면 테이블1에 테이블2의 데이터를 포함시키는 것입니다. 테이블1에는 있지만 테이블2에는 없는 값(조인 조건을 만족하지 않는 컬럼)은 NULL로 표현됩니다.
RIGHT OUTER JOIN : 테이블2를 기준(RIGHT OUTER)으로 테이블1을 합치게 됩니다. 테이블2가 참조키를 가지는 테이블이므로 테이블1에만 있는 값은 제외됩니다.
FULL OUTER JOIN : 두 테이블의 LEFT OUTER JOIIN결과와 RIGHT OUTER JOIN결과를 합치는 조인으로 결과에 모두 존재하는 row는 한 번만 표현해 줍니다.

🫥 프로그래머스 SQL 코딩테스트 - 오랜 기간 보호한 동물(1) (lv.3)

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다.

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하기 위해 LEFT OUTER JOIN을 사용하는 문제입니다.
SELECT I.name, I.datetime
FROM animal_ins AS I 
	LEFT OUTER JOIN animal_outs AS O 
    ON I.animal_id = O.animal_id
WHERE O.animal_id IS NULL
LIMIT 3;
🫥 프로그래머스 SQL 코딩테스트 - 오랜 기간 보호한 동물(2) (lv.3)

입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 문제입니다. 입양 보낸 동물들을 기준으로 테이블을 합쳐야 하기 때문에 RIGHT OUTER JOIN을 사용하는 문제입니다

보호기간을 구하기 위해 DATEDIFF함수도 사용해야 하는 문제입니다.
SELECT O.animal_id, O.name
FROM animal_ins AS I 
	RIGHT OUTER JOIN animal_outs AS O 
    ON I.animal_id = O.animal_id
GROUP BY O.animal_id
ORDER BY DATEDIFF(O.datetime, I.datetime) DESC
LIMIT 2;

다양한 JOIN

NATURAL JOIN : 두 테이블에서 이름이 같은 컬럼을 찾아서 자동으로 조인 조건을 설정해 INNER JOIN을 해주는 조인입니다. 즉, ON 조인 조건을 적지 않습니다. 하지만 테이블 구조를 모르는 사람이라면 어떤 컬럼을 기준으로 조인이 되었는지 알 수 없기 때문에 INNER JOIN을 사용해 ON을 명시해 주는 것이 더 좋습니다.
CROSS JOIN : 한 테이블의 하나의 row에 대해 다른 테이블의 모든 row들을 매칭하고, 그 다음 row에 대해서도 반복함으로써 두 테이블의 row들의 모든 조합을 보여주는 조인입니다. 이를 두 테이블의 cartesian product(두 집합의 모든 원소들의 조합을 나타내는 카르테시안 곱)을 구하는 조인이라고 합니다. 잘 쓰이지 않습니다.
SELF JOIN : 테이블이 자기 자신과 조인하는 경우입니다.
Non-Equi JOIN : ON 조인 조건에 동등 조건이 아닌 다른 종류의 조건을 사용해 조인할 수도 있습니다. 예컨대 부등호를 사용해 특정 시점 이후의 데이터를 구하거나 할 수 있습니다.

🌿 USING

SELECT 컬럼명 FROM 테이블1 INNER JOIN 테이블2 USING 겹치는 컬럼명

두 테이블에서 조인 조건으로 사용되는 컬럼들의 이름이 같다면 USING을 사용해 컬럼명만으로 JOIN 할 수 있습니다.

🌿 UNION

서로 다른 종류의 테이블도 조회하는 컬럼을 일치시키면 집합 연산이 가능합니다.
총 컬럼의 수와 각 컬럼의 데이터 타입만 일치하면 UNION 연산이 가능합니다.
총 컬럼의 수가 맞지 않을 경우, 해당 컬럼이 없는 테이블에선 해당 컬럼에 다른 값을 넣어 채울 수 있습니다.
4.UNION을 사용하면 교집합 영역의 row들은 중복을 제거하고 하나의 row만 보여줍니다.
UNION을 사용했을 때 중복을 제거하면서 우연히 같은 값이라 누락되는 값이 있을 수 있습니다. 이 때 UNION ALL 연산자를 사용하면 합집합을 보여주면서 중복을 제거하지 않고 겹치는 값을 모두 볼 수 있습니다.

🫥 프로그래머스 SQL 코딩테스트 - 오프라인/온라인 판매 데이터 통합하기 (lv.4)

ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성하면서 ONLINE_SALE테이블에만 있는 USER_ID 값은 OFFLINE_SALE데이터에서 NULL 로 표시하는 문제입니다.
SELECT sales_date, product_id, user_id, sales_amount FROM online_sale
	WHERE sales_date LIKE '2022-03%'
UNION -- UNION을 사용해 테이블을 합칩니다.
SELECT sales_date, product_id, NULL, sales_amount FROM offline_sale
	-- user_id 값으로 NULL을 채워서 컬럼의 수를 맞춰주었습니다.
	WHERE sales_date LIKE '2022-03%'