[6일차] SQL 라이브 세션 4

⊙ Join 이란?

 

조인은 두 개 이상의 테이블에서 데이터를 결합하여 원하는 정보를 추출하는 SQL 작업. 

테이블들이 각각 분리된 정보를 갖고 있을 때 공통 컬럼을 써서 데이터를 결합할 수 있다.

테이블들이 각각 분리된 정보를 갖고 있을 때 공통 컬럼을 통해 데이터를 결합하여 전체적인 통찰을 제공

 

* 실무에서 가장 많이 사용하는 조인

- Inner join : 두 테이블의 공통된 데이터만 조회 

가장 기본적, 가장 많이 사용되며 결합 기준이 명확한 데이터를 조회할 때 적합

- left join : 왼쪽 테이블의 모든 행을 반환. 오른쪽에 없는 데이터는 null로 표시

 

*기타 조인

- Right join : 오른쪽 테이블을 기준으로 반환

- Cross join : 모두 가능한 조합을 생성 -> 테스트 데이터 생성이나 조건 확인 시 유용하나 실무 활용도는 낮다

- Self join : 동일 테이블 내에서 데이터를 비교하거나 그룹화 할 때 유용 (특정 조건을 만족하는 매칭 찾기)

 

* Key 란?

데이터 베이스에서 키는 테이블의 각 행을 고유하게 식별하는 데 사용되는 컬럼

- 기본 키 primary key : 행을 유일하게 식별하는 컬럼으로 null값과 중복 허용 X

- 외래 키 foreign key : 다른 테이블의 기본키를 참조해 테이블 간 관계를 설정하는 컬럼

- 대체 키 candidate key : 기본 키로 선택 가능한 후보 컬럼

- 복합 키 composite key : 두 개 이상의 컬럼을 결합하여 만든 기본 키

https://inpa.tistory.com/entry/DB-%F0%9F%93%9A-%ED%82%A4KEY-%EC%A2%85%EB%A5%98-%F0%9F%95%B5%EF%B8%8F-%EC%A0%95%EB%A6%AC

 

⊙ Inner Join 

 

Q. 상품 주문 이력이 있는 고객의 고객 정보와 주문 내역 조회하기

select *
from customers c inner join orders o on c.고객ID = o.고객ID

 

Q. 주문 데이터를 활용해 VIP 회원의 구매 상품 목록만 조회하기

select c.고객ID, c.이름, o.상품명
from customers c inner join orders o on c.고객ID = o.고객ID
where c.회원등급 = 'VIP'

 

Q. 한 번 이상 결제이력이 있는 고객들의 회원등급별 결제액 합산을 구하기

select c.회원등급,
       sum(p.결제금액) as 총결제금액
from customers c inner join payments p on c.고객ID = p.고객ID
group by 1

한 사람의 여러 번 결제 건의 경우 중복된다는 점을 우리는 알고 있어야 한다. 

 

⊙ Left (outer) Join 

 

Q. 주문 여부와 상관없이 모든 고객 정보를 포함한 고객-결제 내역을 조회하기

select *
from customers c left join payments p on c.고객ID = p.고객ID

 

Q. 결제를 한 번도 안한 고객의 정보를 조회하기

select c.고객ID, 
       c.이름, 
       c.연령대, 
       c.성별, 
       c.회원등급
from customers c left join payments p on c.고객ID = p.고객ID
Where p.결제금액 is null

 

⊙ Self Join 

 

Q. 같은 연령대, 다른 성별에 속한 고객 간 매칭결과를 조회하기

SELECT c1.연령대, 
       c1.고객ID AS 고객1_ID, 
       c1.이름 AS 고객1_이름, 
       c1.성별 AS 고객1_성별, 
       c2.고객ID AS 고객2_ID, 
       c2.이름 AS 고객2_이름, 
       c2.성별 AS 고객2_성별
FROM customers c1 INNER JOIN customers c2 
ON c1.연령대 = c2.연령대
AND c1.성별 != c2.성별;

같은 테이블에서 데이터를 뽑아서 분석하는 조인 

문제 : 데이터가 중복이 된다. 

 

Q. + 중복제거

SELECT c1.연령대, 
       c1.고객ID AS 고객1_ID, 
       c1.이름 AS 고객1_이름, 
       c1.성별 AS 고객1_성별, 
       c2.고객ID AS 고객2_ID, 
       c2.이름 AS 고객2_이름, 
       c2.성별 AS 고객2_성별
FROM customers c1 INNER JOIN customers c2
ON c1.연령대 = c2.연령대
AND c1.성별 != c2.성별
AND c1.고객ID < c2.고객ID; #중복제거!!

 

⊙ Union

 

여러 select 문의 결과를 수직으로 결합하면서 중복된 행은 제거 -> 중복된 데이터가 제거 되므로 성능이 다소 떨어질 수 있다.

단, select 문의 열 수와 데이터 형식이 동일해야 한다.

# union 예시 : 중복 제거
select 고객ID
from customers
where 성별 = 'M'
union
select 고객ID
from orders;

쿼리 결과

⊙ Union All

 

여러 select 문의 결과를 수직으로 결합하며, 중복된 행도 포함

union과 동일하게 select 문의 열 수와 데이터 형식이 동일해야 한다.

# union all 예시 : 중복 포함
select 고객ID
from customers
where 성별 = 'M'
union all
select 고객ID
from orders

쿼리결과

차이점 : 유니언은 합친 후 정렬하고 중복을 제거. 유니언 올은 합치기만 하고 중복 제거도 안 함.

 

⊙ Full outer join 구현하기

 

MySql은 full outer join을 지원하지 않지만 Left join 결과와 Right join 결과를 Union 하여 동일한 결과를 얻을 수 있다.

SELECT c.고객ID, c.이름, p.결제ID, p.결제금액
FROM customers c
LEFT JOIN payments p
ON c.고객ID = p.고객ID

UNION

SELECT c.고객ID, c.이름, p.결제ID, p.결제금액
FROM customers c
RIGHT JOIN payments p
ON c.고객ID = p.고객ID;

 결과 노션 참고

 

⊙ 서브쿼리 소개

 

SQL 쿼리 내부에서 실행되는 중첩된 쿼리. 복잡한 데이터를 단순하게 만들어주고, 중간 결과를 생성해 외부 쿼리에서 활용하는데 사용된다.

서브쿼리는 반환하는 타입, 의존성, 위치에 따라서 분류가 된다.

반환하는 타입은 스칼라, 로우 값, 테이블 값  |  의존성은 상관/비상관 서브 쿼리  

 

 

* 특징

- 위치 : Select, From, Where, Having, Join 등

- 독립성 : 서브쿼리는 외부 쿼리와 상관없이 독립적으로 실행될 수도 있고, 외부 데이터를 참조하여 실행될 수도 있다. (비상관 / 상관 서브 쿼리)

- 실행 순서 : 서브 쿼리는 외부 쿼리보다 먼저 실행되며 반환된 결과는 외부 쿼리에서 사용된다.

 

* 반환 타입에 따른 분류

서브 쿼리는 반환값의 형태에 따라 세 가지로 나뉜다 -> 스칼라, 다중행(한 컬럼), 다중열(테이블)

 

- 스칼라 서브쿼리

서브쿼리가 스칼라 값(숫자, 문자열 등)을 반환

주로 Select, Where, Having 절에서 사용한다.

select customer_id,
       (select max(payment_value) from payments) as max_payment
from customers

 

- 다중 행 서브쿼리

서브쿼리가 1개의 컬럼에 여러 행을 반환 -> 마치 배열(리스트)처럼 동작

주로 In, Any, All 등의 연산자와 함께 사용한다.

select customer_id
from customers
where customer_id IN (
    select customer_id
    from orders
    where order_status = 'delivered'
)

 

- 다중 열 서브쿼리

서브쿼리가 여러 열과 여러 행으로 구성된 결과를 반환 -> 가상 테이블처럼 동작

From, Join에서 사용된다.

select customer_id, total_payment
from
(
select customer_id, 
       sum(payment_value) as total payment
from payments
group by customer_id
) as payment_summary

 

* 위치에 따른 분류

 

- select 절에서 사용

단일값만을 반환하며 외부 쿼리의 결과에 새로운 파생변수를 생성

-- 각 고객의 총 주문수 반환
SELECT customer_id,
       (SELECT COUNT(*) 
       FROM orders 
       WHERE orders.customer_id = customers.customer_id) AS order_count
FROM customers;

-- 각 고객의 최근 주문ID 반환
SELECT customer_id,
       (SELECT MAX(order_id) 
        FROM orders 
        WHERE orders.customer_id = customers.customer_id) AS latest_order
FROM customers;

 

- from 절에서 사용

복잡한 데이터 집합을 가상 테이블로 구조화하여 외부 쿼리에서 활용

SELECT customer_id, SUM(payment_value) AS total_payment
FROM (
    SELECT customer_id, payment_value
    FROM payments
    WHERE payment_date >= '2023-01-01'
) AS recent_payments
GROUP BY customer_id;

 

- where 절에서 사용

비교 연산자 혹은 논리 연산자와 함께 특정 조건을 필터링

1. 비교 연산자

부등호(>, <, =, !=, >=, <=)를 활용하여 조건을 비교

SELECT customer_id
FROM customers
WHERE age > ( -- 고객의 나이가 모든 고객의 평균 나이보다 큰 경우
    SELECT AVG(age)
    FROM customers
);

2. 논리 연산자

• ANY: 서브쿼리 결과 중 하나라도 조건을 만족하면 참
• ALL: 서브쿼리 결과의 모든 값에 대해 조건을 만족해야 참
• IN : 서브쿼리 결과 값 중 하나와 일치하면 참
• EXISTS :서브쿼리의 결과가 존재하면 참(한 행이라도 존재하면 OK)

SELECT customer_id
FROM customers
WHERE customer_id IN ( --서브쿼리 결과값 중 하나라도 매칭되면 true
    SELECT customer_id
    FROM orders
    WHERE order_status = 'shipped'
); 

SELECT customer_id
FROM customers
WHERE age > ANY ( --서브쿼리 결과값 중 하나라도 조건을 만족하면 true
    SELECT age
    FROM customers
    WHERE city = 'Seoul'
); 

SELECT customer_id
FROM customers
WHERE age <= ALL (  -- 서브쿼리 결과값의 모든 조건을 만족해야 true
    SELECT age
    FROM customers
    WHERE city = 'Seoul'
) AND city = 'Busan';

SELECT customer_id, name
FROM customers c
WHERE EXISTS ( -- 고객이 주문 기록이 있는 경우 참!
    SELECT 1 -- SELECT * 와 동일하다는 점, 꼭 기억해주세요!
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
-- 서브쿼리 결과가 한 행이라도 존재하면 True

 

* 의존성에 따른 분류

 

- 비상관 서브쿼리 Independent Subquery

서브쿼리가 외부 쿼리와 독립적으로 한번만 실행됨

내부 쿼리 결과가 외부 쿼리에서 사용됨

SELECT customer_id
FROM customers
WHERE age > (
    SELECT AVG(age)
    FROM customers
);

 

- 상관 서브쿼리

내부 쿼리가 외부 쿼리의 데이터를 참조하며 외부 쿼리의 각 행에 대해 반복적으로 실행된다.

각 행 별로 동적으로 조건을 비교가 가능하다

 

1. 외부 데이터를 참조 : 내부 쿼리가 외부 쿼리의 데이터를 사용

2. 반복 실행 : 외부 쿼리가 실행될 때마다 내부 쿼리도 반복 실행

3. 성능 고려 필요 : 반복 실행으로 인한 성능 저하. 성능 이슈를 완화하기 이해 상관 서브쿼리를 JOIN 혹은 윈도우 함수로 변환

-- 상관 서브쿼리의 예1
SELECT order_id, customer_id, order_amount
FROM orders o1
WHERE order_amount > (
    SELECT AVG(order_amount)
    FROM orders o2
    WHERE o1.customer_id = o2.customer_id
);

-- 상관 서브쿼리의 예1: JOIN으로 변환
SELECT o1.order_id, o1.customer_id, o1.order_amount
FROM orders o1
JOIN (
    SELECT customer_id, AVG(order_amount) AS avg_order_amount
    FROM orders
    GROUP BY customer_id
) avg_orders
ON o1.customer_id = avg_orders.customer_id
WHERE o1.order_amount > avg_orders.avg_order_amount;

-- 상관 서브쿼리의 예2
SELECT customer_id, 
       (SELECT COUNT(*) 
        FROM orders 
        WHERE orders.customer_id = customers.customer_id) AS order_count
FROM customers;

-- 상관 서브쿼리의 예2: JOIN으로 변환
SELECT c.customer_id, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

-- 상관 서브쿼리의 예2: 윈도우 함수로 변환!
SELECT customer_id, COUNT(order_id) OVER(PARTITION BY customer_id) AS order_count
FROM orders;