고급 SQL 구문 (ft. MariaDB): JOIN, UNION, 집계 함수, 그룹화

MariaDB에서 여러 테이블 다루기 (JOIN)

데이터베이스에서 데이터의 효율적인 관리를 위해 테이블을 분리해 저장하는 경우가 많은데, 이 때 JOIN 함수를 사용하면 여러 테이블의 데이터를 조합해 원하는 정보를 조회할 수 있다. 

INNER JOIN	두 테이블에서 일치하는 데이터만 반환	교집합을 반환하며, 일치하지 않는 데이터는 제외
LEFT JOIN	왼쪽 테이블의 모든 데이터 + 오른쪽 테이블에서 일치하는 데이터	왼쪽 테이블과 일치하는 데이터가 없으면 NULL 반환
RIGHT JOIN	오른쪽 테이블의 모든 데이터 + 왼쪽 테이블에서 일치하는 데이터	오른쪽 테이블과 일치하는 데이터가 없으면 NULL 반환
FULL OUTER JOIN	두 테이블의 모든 데이터 포함	합집합을 반환하며, 일치하지 않는 데이터는 NULL로 표시 (MySQL 미지원, UNION으로 대체 가능)

 

PRIMARY KEY	NOT NULL + UNIQUE로, 테이블에서 대표되는 컬럼을 의미.
NOT NULL	결측값(N/A)이 허용이 안되고 필수로 입력되어야 하는 값.
UNIQUE	중복되지 않는 유일한 값을 의미.
CASCADE	데이터를 변경/삭제할 때 참조된 데이터도 자동 변경/삭제.
SET NULL	데이터를 변경/삭제할 때 참조된 데이터는 NULL 값으로 세팅.
FOREIGN KEY	한 테이블의 특정 컬럼이 다른 테이블의 PRIMARY KEY를 참조하도록 설정하는 제약 조건. 참조된 테이블에 존재하지 않는 값이 입력되지 않도록 제한.	orders.customer_id → customers.customer_id 참조
REFERENCES	FOREIGN KEY 정의 시 참조할 테이블과 컬럼을 지정.	FOREIGN KEY (author_id) REFERENCES authors(author_id)
ON DELETE CASCADE	FOREIGN KEY가 참조하는 부모 테이블의 데이터가 삭제될 때, 자식 테이블의 데이터도 자동 삭제.	FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE CASCADE
ON DELETE SET NULL	FOREIGN KEY가 참조하는 부모 테이블의 데이터가 삭제될 때, 자식 테이블의 데이터는 NULL로 세팅.	FOREIGN KEY (class_id) REFERENCES Class(class_id) ON DELETE SET NULL
DISTINCT	SELECT 조회 시 중복된 데이터를 제거하여 고유한 값만 출력.	SELECT DISTINCT product_name FROM sales;

 

연습문제: 학생 테이블과 수업 테이블 결합하기

student_id	name	grade	class_id
1	김민수	1학년	1
2	이지은	2학년	2
3	박철수	3학년	1
4	최영희	1학년	3
5	정우성	2학년	NULL (수업을 듣지 않는 학생)

 

• (자식) 테이블 생성 쿼리: CREATE TABLE Student (student_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, grade VARCHAR(10) NOT NULL, class_id INT NULL, FOREIGN KEY (class_id) REFERENCES Class(class_id) ON DELETE SET NULL);
• 데이터 삽입: INSERT INTO Student (name, grade, class_id) VALUES ('김민수', '1학년', 1), ('이지은', '2학년', 2), ('박철수', '3학년', 1), ('최영희', '1학년', 3), ('정우성', '2학년', NULL);

class_id	subject	teacher
1	수학	이승기
2	과학	한지민
3	영어	김태희

• 부모 테이블이라 이걸 먼저 생성: CREATE TABLE Class (class_id INT AUTO_INCREMENT PRIMARY KEY, subject VARCHAR(50) NOT NULL, teacher VARCHAR(50) NOT NULL);
• 데이터 삽입: INSERT INTO Class (subject, teacher) VALUES ('수학', '이승기'), ('과학', '한지민'), ('영어', '김태희');

(1) 학생(Student)과 수업(Class) 정보를 INNER JOIN으로 결합해 각 학생이 듣는 수업의 과목명과 담당 교사를 조회 

 
(2) 모든 학생을 포함하여 수업 정보를 조회하고, 수업을 듣지 않는 학생도 포함. (LEFT JOIN으로 모든 학생 포함 / NULL 값 표시)

 
(3) 모든 수업을 포함해 학생과 연결 (RIGHT JOIN으로 모든 수업 포함)
 

반응형

고급 SQL - 집계 함수와 그룹화

COUNT(*)	행의 개수 계산	SELECT COUNT(*) AS total_orders FROM orders;  (총 주문개수 조회)
SUM(컬럼명)	해당 컬럼 값의 합계	SELECT SUM(price) AS total_sales FROM orders                                                       (특정 기간 동안의 주문액 총합) WHERE order_date BETWEEN '2024-03-01' AND '2024-04-30'
AVG(컬럼명)	평균 값 계산	SELECT customers.name, AVG(orders.price) AS avg_spent FROM customers LEFT JOIN orders ON customer.id = orders.customer_id GROUP BY customers.name;                     (고객별 평균 주문액 조회)
MAX(컬럼명)	최대 값	SELECT product, MAX(price) AS highest_price FROM orders; (가장 비싼 주문 찾기)
MIN(컬럼명)	최소 값	SELECT product, MIN(price) AS highest_price FROM orders; (가장 싼 주문 찾기)
UNION	2개 이상의 테이블에서 데이터를 가져와 동일한 컬럼 구조로 하나의 결과 집합으로 결합하는 SQL 연산자. 여러 데이터 소스를 하나의 테이블처럼 합쳐서 분석할 때 유용 (컬럼 개수와 데이터 타입이 동일해야 함) 기본적으로 중복된 데이터를 제거해 반환하며, 중복값도 출력하려면 UNION ALL을 사용해야 함.

※ JOIN과 UNION의 차이

1. JOIN: 두 개 이상의 테이블을 기준이 되는 공통 필드를 통해 결합하여 새로운 데이터를 생성하는 방식.
   • 주로 고객 정보, 마케팅 캠페인, 거래 데이터 등을 통합하여 보다 정밀한 분석을 수행할 때 사용됨.
2. UNION: 여러 테이블에서 가져온 데이터를 동일한 컬럼 구조를 가진 경우 하나의 결과 집합으로 합치는 방식.
   • 예를 들어, 여러 마케팅 채널(이메일, SNS, 광고 등)의 성과 데이터를 하나로 합칠 때 유용함.

항목	UNION	JOIN
데이터 결합 방식	여러 테이블의 데이터를 위아래로 합침	공통 키를 기준으로 좌우로 결합
컬럼 개수	결합하려는 테이블의 컬럼 개수와 데이터 타입이 동일해야 함	테이블 간의 공통 키를 사용하여 여러 개의 컬럼을 조합
중복 데이터	기본적으로 제거됨 (UNION ALL 사용 시 제거 안 됨)	중복 데이터가 있을 경우 그대로 유지됨

이메일 캠페인과 SNS 캠페인의 클릭 데이터 통합해 분석 / 고객별로 어떤 채널에서 더 많은 반응을 보였는지 비교

이메일 캠페인을 A그룹, SNS 캠페인을 B그룹으로 분류해 A/B 테스트 데이터 통합 (클릭 순 정렬)

한국, 미국, 유럽의 캠페인 데이터 통합 / 매출 순으로 정렬