mariaDB에서 여러테이블 다루기(JOIN,UNION), 집계 함수와 그룹화

 

 

JOIN, UNION을 쓰는 이유?

 

• 하나의 테이블에 데이터를 다 담기란 어렵기 때문. 데이터베이스에서는 데이터를 보다 효율적으로 관리하기 위해 테이블을 분리하여 저장하는 경우가 많다.

 

 

 

JOIN - 여러 테이블의 데이터를 조합하여 원하는 정보를 조회

 

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

 

왼쪽, 오른쪽 테이블 나누는 기준 

FROM 글자 뒤에 있는 테이블이 왼쪽, JOIN 글자 뒤에 있는 테이블이 오른쪽

 

예시)

SELECT *
FROM A   -- 왼쪽 테이블
LEFT JOIN B ON A.id = B.id;  -- 오른쪽 테이블

 

왼쪽 테이블 A의 모든 데이터를 가져옴.

오른쪽 테이블 B에서 일치하는 값이 있으면 연결하고, 없으면 NULL

 

SELECT *
FROM A   -- 왼쪽 테이블
RIGHT JOIN B ON A.id = B.id;  -- 오른쪽 테이블

 

오른쪽 테이블 B의 모든 데이터를 가져옴.

왼쪽 테이블 A에서 일치하는 값이 있으면 연결하고, 없으면 NULL.

 

 

---

 

 

 

JOIN 쿼리문 예시1)

SELECT customers.name, customers.email, orders.product, orders.price, orders.order_date
FROM customers  -- 왼쪽(Left) 테이블
INNER JOIN orders ON customers.id = orders.customer_id;  -- 오른쪽(Right) 테이블

 

왼쪽 테이블(Left Table): FROM 절에 등장하는 테이블 → customers

오른쪽 테이블(Right Table): JOIN 절에서 합쳐지는 테이블 → orders

조인 조건: ON customers.id = orders.customer_id

customers.id(왼쪽 테이블)과 orders.customer_id(오른쪽 테이블)의 값이 일치하는 행만 반환된다.

ON 키워드는 SQL에서 JOIN을 수행할 때 두 테이블을 결합하는 기준(조인 조건)을 지정하는 역할을 한다.

 

결과 : customers 테이블을 가져와서 orders 테이블과 customers.id = orders.customer_id 조건으로 INNER JOIN 수행.

조인된 결과에서 customers.name, customers.email, orders.product, orders.price, orders.order_date 컬럼을 선택해서 출력.

여기서 customers 테이블은 고객정보 테이블이고 orders는 주문정보 테이블이었다.

결과적으로 INNER JOIN을 통해 주문을 한 고객만 출력하게 됨.

 

 

 

---

 

 

 

JOIN 쿼리문 예시2)

SELECT customers.name, customers.email, orders.product, orders.price, orders.order_date
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

 

결과 : customers 테이블을 먼저 가져온 다음 customers 테이블과 orders 테이블을 customers.id = orders.customer_id 조건으로 LEFT JOIN 수행. (ON 키워드 다음은 조건임)

 

즉, 왼쪽 테이블인 customers의 모든 행을 유지하면서 오른쪽 테이블인 orders 테이블에 일치하는 데이터가 있으면 가져오고, orders 테이블에 일치하는 데이터가 없으면 NULL 값이 들어감.

 

 

여기서 customers 테이블은 고객정보 테이블이고 orders는 주문정보 테이블이었다.

결과적으로 LEFT JOIN을 통해 주문을 하지 않은 고객도 포함해 출력하게 됨. 

주문을 하지 않은 고객의 주문정보 데이터는 NULL로 출력됨.

 

 

---

 

 

 

집계함수 

함수	설명
COUNT(*)	행의 개수 계산
SUM	해당 컬럼 값의 합계
AVG	평균 값 계산
MAX	최대값
MIN	최소값

 

 

집계함수와 그룹화 예시

SELECT customers.name, COUNT(orders.id) AS order_count
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name;

 

해석 : 

1) FROM customers  > customers 테이블을 기준(왼쪽 테이블)으로 가져온다.

2) LEFT JOIN orders ON customers.id = orders.customer_id 

> customers 테이블과 orders 테이블을 customers.id = orders.customer_id 조건으로 LEFT JOIN한다.

(즉, 모든 고객정보를 가져오면서 주문한 고객은 주문 데이터도 함께 가져온다. 주문하지 않은 고객은 NULL값이 들어감)

3) GROUP BY customers.name > customers.name을 기준으로 그룹화한다. 즉, 같은 고객이름을 하나의 그룹으로 묶는다.

4) SELECT customers.name, COUNT(orders.id) AS order_count

> customers.name을 출력하고, orders.id 개수를 세서(COUNT) order_count라는 임시변수를 출력.

COUNT 함수는 NULL 값을 세지 않기 때문에, 주문을 하지 않은 고객은 0으로 표시된다.

 

 

---

 

 

 

UNION

 

• 두개 이상의 테이블에서 데이터를 가져와 동일한 컬럼구조로 하나로 합치는 개념. (합집합)
• 여러 데이터 소스를 하나의 테이블처럼 합쳐 분석할 때 유용.

 

UNION의 특징

• 결합하려는 모든 테이블의 컬럼 개수와 데이터 타입이 동일해야 한다.
• 중복 데이터를 자동으로 제거한다.
• 중복 데이터를 포함하려면 UNION ALL을 사용한다.

 

 

 

UNION 쿼리문 예시 - A/B 테스트 통합 분석

SELECT customer_id, campaign_name, clicks, 'A' AS test_group
FROM email_campaign
UNION
SELECT customer_id, campaign_name, clicks, 'B' AS test_group
FROM sns_campaign;

 

해석 : 위 쿼리문은 A/B 테스트 그룹 데이터를 통합하는 내용이다.

이메일 캠페인을 A그룹, SNS 캠페인을 B그룹으로 분류하여 A/B 테스트 데이터를 통합한다.

customer_id, campaign_name, clicks 컬럼과 test_group 컬럼을 출력하게 된다. test_group 컬럼에서 A와 B데이터를 표시해서 구분을 쉽게 할 수 있게 한다.

 

 

 

 

 

JOIN과 UNION의 차이

 

1. JOIN: 두 개 이상의 테이블을 기준이 되는 공통 필드를 통해 결합하여 새로운 데이터를 생성하는 방식.

주로 고객 정보, 마케팅 캠페인, 거래 데이터 등을 통합하여 보다 정밀한 분석을 수행할 때 사용됨.

 

2. UNION: 여러 테이블에서 가져온 데이터를 동일한 컬럼 구조를 가진 경우 하나의 결과 집합으로 합치는 방식.여러 마케팅 채널(이메일, SNS, 광고 등)의 성과 데이터를 하나로 합칠 때 유용함.

 

JOIN은 데이터 연동, UNION은 데이터 한번에 모아보기!

 

 

 

 

 

FOREIGN KEY (외래키)

• FOREIGN KEY는 한 테이블의 특정 컬럼이 다른 테이블의 기본 키 (PRIMARY KEY)를 참조하도록 설정하는 제약조건.
• 데이터 무결성을 유지하기 위해 사용. (무결성 = 결함이 없다는 뜻)
• 참조된 테이블에 존재하지 않는 값이 입력되지 않도록 제한할 수 있다.
• FOREIGN KEY 는 반드시 참조되는 테이블의 기본키(PRIMARY) 또는 고유키 (UNIQUE KEY)를 가리켜야 한다. 만약 참조 대상이 기본키나 유니크키가 아니라면, 동일한 값을 가진 여러개의 행이 있을 수도 있어서 어떤 행을 참조하는지 명확하지 않게 된다.
• FOREIGN KEY를 사용하지 않아도 JOIN을 통해 데이터를 조합할 수 있지만 데이터에서는 무결성이 중요하기 때문에 FOREIGN KEY는 중요하다!

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10,2),
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);   # customer_id를 외래키로 연결. customers 테이블의 customer ID를 참조.

 

orders 테이블의 customer_id는 customers 테이블의 customer_id를 참조하는 외래키이다.

이를 통해서 orders 테이블에 존재하지 않는 고객 ID가 입력되는 것을 방지할 수 있다.

외래키로 연결시키면 customers 테이블을 지우려고 해도 지워지지 않는다. orders 테이블을 지워야 지워진다. (외래키로 연결했기 때문에 하위 테이블인 orders 테이블을 지운 다음에 상위테이블 customers를 지울 수 있음.)

 

 

 

ON DELETE CASCADE

• 외래 키가 참조하는 부모테이블의 데이터가 삭제될 때, 자식 테이블의 데이터도 자동으로 삭제되도록 설정하는 옵션

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(200),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id) ON DELETE CASCADE
);

 

 

authors 테이블의 특정 author_id가 삭제되면, books 테이블에서 해당 author_id를 참조하는 모든 행도 자동으로 삭제됨.

DELETE FROM authors WHERE author_id = 1;

 

authors 테이블에서 author_id=1을 삭제하면, books 테이블에서 해당 author_id를 가진 책들도 함께 삭제됨.

 

 

 

 

---

 

 

 

 

 

응용) 쿼리 예시문 1

SELECT u.channel, 
       COUNT(DISTINCT u.user_id) AS visitors, 
       COUNT(DISTINCT p.customer_id) AS buyers,
       (COUNT(DISTINCT p.customer_id) * 100.0 / COUNT(DISTINCT u.user_id)) AS conversion_rate
FROM user_tracking u
LEFT JOIN purchases p ON u.user_id = p.customer_id
GROUP BY u.channel;

 

해석 :

1) FROM user_tracking u > user_tracking 테이블에서 u라는 별칭으로 데이터를 가져옴.

2) LEFT JOIN purchases p ON u.user_id = p.customer_id

>  user_tracking 테이블을 기준으로 purchases 테이블을 user_tracking 테이블과 user_id = customer_id 조건으로 연결.

(purchase 테이블은 p로 별칭)

3) GROUP BY u.channel 

> channel을 기준으로 데이터를 그룹화해서 채널별 통계를 구함.

4) COUNT(DISTINCT u.user_id) AS visitors

> user_tracking 테이블에서 '각 채널을 방문한 방문자 수'를 계산.

5) COUNT(DISTINCT p.customer_id) AS buyers

> purchases 테이블에서 '각 채널에서 구매한 구매자 수'를 계산.

6) (COUNT(DISTINCT p.customer_id) * 100.0 / COUNT(DISTINCT u.user_id)) AS conversion_rate

> 전환율 계산 (전환율 = 구매자수 / 방문자수 X 100)

 

 

 

 

응용) 쿼리 예시문 2 - 서브쿼리

SELECT c.customer_id, c.name, c.email, b.cart_total
FROM customers c
JOIN (
    SELECT customer_id, SUM(price * quantity) AS cart_total
    FROM cart_items
    GROUP BY customer_id
) b ON c.customer_id = b.customer_id
LEFT JOIN purchases p ON c.customer_id = p.customer_id
WHERE p.customer_id IS NULL;

 

해석 :

1) cart_items 테이블에서 각 고객(customer_id)별로 장바구니(cart)의 총 가격(cart_total)을 계산해서 GROUP BY 로 고객별(cart_total) 그룹화. (서브쿼리)

2) customers 테이블과 서브쿼리에서 구한 값인 cart_total을 customer_id 기준으로 JOIN

( customers 테이블에 각 고객이 담은 장바구니 금액을 추가)

3) LEFT JOIN purchases p ON c.customer_id = p.customer_id

> purchases 테이블을 LEFT JOIN으로 결합해서 구매한 고객인지 확인. LEFT JOIN을 사용했기 때문에 구매하지 않은 고객도 포함됨. purchases 테이블에는 구매한 고객의 ID만 저장됨

4) WHERE p.customer_id IS NULL > purchases 테이블에서 NULL 값을 가지는 고객만 필터링

5) SELECT 에서 customer_id, name, email, cart_total을 출력

결과는 '장바구니에 물건은 담았지만 구매하지 않은 고객'만 조회.

 

 

 

 

응용) 쿼리 예시문 3

SELECT m.campaign_name, m.ad_spend, SUM(p.amount) AS total_revenue,
       (SUM(p.amount) - m.ad_spend) AS profit
FROM marketing_spend m
LEFT JOIN purchases p ON m.campaign_id = p.campaign_id
GROUP BY m.campaign_name, m.ad_spend;

 

 

해석 :

1) marketing_spend 테이블을 가져오기.

2) marketing_spend 테이블 기준으로 purchases 테이블과 LEFT JOIN하여 각 캠페인에 연결된 구매 데이터 가져오기

3) 각 캠페인별로 SUM(p.amount)를 계산하여 총 매출(total_revenue) 구하기
4) 총 매출 - 광고비를 계산하여 이익(profit) 구하기
5) 캠페인 이름과 광고비를 기준으로 GROUP BY하여 campaign_name, ad_spend, total_revenue, profit 출력

 

 

 

 

 

* 기억하기

VM 인스턴스에서 터미널 실행하고 기존계정(user1)으로 이동하기

1) cd / 입력

2) cd var 입력 

3) mysql -u user1 -p 입력 (비번은 입력창에 안 보임)

 

INT AUTO INCREMENT PRIMARY KEY - 'AUTO INCREMENT'가 붙으면 데이터가 자동으로 생성.

INT PRIMARY KEY - 내가 직접 데이터 생성

 

테이블 생성시 NULL - NULL값을 허용 / NOT NULL - NULL값을 허용하지 않음

NULL 값을 데이터에 삽입할 때는 따옴표 없이 NULL을 그대로 사용하면 된다.

 

DEFAULT NULL → 기본이 NULL 인 것. 입력값이 없어도 오류를 뱉어내지 않음.