RADIANT GARDEN

최근 게시글

02_04 JOIN과 GROUP BY 실습

15 min read

하위 태스크 1

Chapter5 JOIN 예제 실행

05-1.sql 의 조인 예제를 실행하고 SELECT/JOIN/WHERE 구조 분석

예제 1:

SELECT
	a.customer_id,
	a.store_id,
	a.first_name,
	a.last_name,
	a.email,
	a.address_id AS a_address_id,
	b.address_id AS b_address_id,
	b.address,
	b.district,
	b.city_id,
	b.postal_code,
	b.phone,
	b.location
FROM
	customer AS a
INNER JOIN address AS b ON
	a.address_id = b.address_id
WHERE
	a.first_name = 'ROSA';
  • SELECT: 나열된 13개 열을 조회한다. AS 키워드를 사용하여 열의 별칭을 설정하였다.
  • FROM: 좌측 테이블을 customer 테이블로 지정한다.
  • JOIN: customer 테이블과 address 테이블을 조인 조건에 따라 조인한다.
  • ON: customer 테이블의 address_id 열과 address 테이블의 address_id 열을 조인키로 사용한다.
  • WHERE: customer 테이블의 first_name 열의 값이 ROSA 인 행을 선택하기 위한 조건이다.

예제 2:

SELECT
	address_id
FROM
	customer AS a
INNER JOIN address AS b ON
	a.address_id = b.address_id
WHERE
	a.first_name = 'ROSA';

오류

address_id 열은 customer 테이블과 address 테이블에 모두 존재한다. 위 SELECT 문은 어떤 테이블의 존재하는 address_id인지 명시하지 않아 오류가 발생한다.

SQL Error [1052] [23000]: Column 'address_id' in field list is ambiguous
  • SELECT: 나열된 1개 열을 조회한다.
  • FROM: 좌측 테이블을 customer 테이블로 지정한다.
  • JOIN: customer 테이블과 address 테이블을 조인 조건에 따라 조인한다.
  • ON: customer 테이블의 address_id 열과 address 테이블의 address_id 열을 조인키로 사용한다.
  • WHERE: customer 테이블의 first_name 열의 값이 ROSA 인 행을 선택하기 위한 조건이다.

예제 3:

SELECT
	a.customer_id,
	a.first_name,
	a.last_name,
	b.address_id,
	b.address,
	b.district,
	b.postal_code
FROM
	customer AS a
INNER join address AS b ON
	a.address_id = b.address_id
	AND a.create_date = b.last_update
WHERE
	a.first_name = 'ROSA';

조인 조건

두 테이블 각각의 열의 이름이 달라도 데이터 유형이 일치하면 조인 조건으로 사용할 수 있다.

  • SELECT: 나열된 7개 열을 조회한다.
  • FROM: 좌측 테이블을 customer 테이블로 지정한다.
  • JOIN: customer 테이블과 address 테이블을 조인 조건에 따라 조인한다.
  • ON: customer 테이블의 address_id 열과 address 테이블의 address_id 열, 그리고 customer 테이블의 create_date 열과 address 테이블의 last_update 열을 조인키로 사용한다.
  • WHERE: customer 테이블의 first_name 열의 값이 ROSA 인 행을 선택하기 위한 조건이다.

예제 4:

SELECT
	a.customer_id,
	a.first_name,
	a.last_name,
	b.address_id,
	b.address,
	b.district,
	b.postal_code,
	c.city_id,
	c.city
FROM
	customer AS a
INNER JOIN address AS b ON
	a.address_id = b.address_id
INNER JOIN city AS c ON
	b.city_id = c.city_id
WHERE
	a.first_name = 'ROSA';
  • SELECT: 나열된 9개 열을 조회한다.
  • FROM: 좌측 테이블을 customer 테이블로 지정한다.
  • JOIN: 첫 번째 조인에서 customer 테이블과 address 테이블을 조인 조건에 따라 조인한다. 두 번째 조인에서 앞서 조인된 테이블과 city 테이블을 조인 조건에 따라 조인한다.
  • ON: 첫 번째 조인에서 customer 테이블의 address_id 열과 address 테이블의 address_id 열을 조인키로 사용한다. 두 번째 조인에서 address 테이블의 city_id 열과 city 테이블의 city_id 열을 조인키로 사용한다.
  • WHERE: customer 테이블의 first_name 열의 값이 ROSA 인 행을 선택하기 위한 조건이다.

예제 5:

SELECT
	a.address,
	a.address_id AS a_address_id,
	b.address_id AS b_address_id,
	b.store_id
FROM
	address AS a
LEFT OUTER JOIN store AS b ON
	a.address_id = b.address_id;
  • SELECT: 나열된 4개 열을 조회한다. AS 키워드를 사용하여 열의 별칭을 설정하였다.
  • FROM: 좌측 테이블을 address 테이블로 지정한다.
  • JOIN: address 테이블과 store 테이블을 조인 조건에 따라 LEFT OUTER JOIN한다.
  • ON: address 테이블의 address_id 열과 store 테이블의 address_id 열을 조인키로 사용한다.

예제 6:

SELECT
	a.address,
	a.address_id AS a_address_id,
	b.address_id AS b_address_id,
	b.store_id
FROM
	address AS a
LEFT OUTER JOIN store AS b ON
	a.address_id = b.address_id
WHERE
	b.address_id IS NULL
  • SELECT: 나열된 4개 열을 조회한다. AS 키워드를 사용하여 열의 별칭을 설정하였다.
  • FROM: 좌측 테이블을 address 테이블로 지정한다.
  • JOIN: address 테이블과 store 테이블을 조인 조건에 따라 LEFT OUTER JOIN한다.
  • ON: address 테이블의 address_id 열과 store 테이블의 address_id 열을 조인키로 사용한다.
  • WHERE: store 테이블의 address_id 열의 값이 NULL 인 행을 선택하기 위한 조건이다.

예제 7:

SELECT
	a.address,
	a.address_id AS a_address_id,
	b.address_id AS b_address_id,
	b.store_id
FROM
	address AS a
RIGHT OUTER JOIN store AS b ON
	a.address_id = b.address_id;
  • SELECT: 나열된 4개 열을 조회한다. AS 키워드를 사용하여 열의 별칭을 설정하였다.
  • FROM: 좌측 테이블을 address 테이블로 지정한다.
  • JOIN: address 테이블과 store 테이블을 조인 조건에 따라 RIGHT OUTER JOIN한다.
  • ON: address 테이블의 address_id 열과 store 테이블의 address_id 열을 조인키로 사용한다.

예제 8:

SELECT
	a.address_id AS a_address_id,
	a.store_id,
	b.address,
	b.address_id AS b_address_id
FROM
	store AS a
RIGHT OUTER JOIN address AS b ON
	a.address_id = b.address_id
WHERE
	a.address_id IS NULL;
  • SELECT: 나열된 4개 열을 조회한다. AS 키워드를 사용하여 열의 별칭을 설정하였다.
  • FROM: 좌측 테이블을 store 테이블로 지정한다.
  • JOIN: store 테이블과 address 테이블을 조인 조건에 따라 RIGHT OUTER JOIN한다.
  • ON: store 테이블의 address_id 열과 address 테이블의 address_id 열을 조인키로 사용한다.
  • WHERE: store 테이블의 address_id 열의 값이 NULL 인 행을 선택하기 위한 조건이다.

예제 9:

SELECT
	a.address_id AS a_address_id,
	a.store_id,
	b.address,
	b.address_id AS b_address_id
FROM
	store AS a
LEFT OUTER JOIN address AS b ON
	a.address_id = b.address_id
UNION
 
SELECT
	a.address_id AS a_address_id,
	a.store_id,
	b.address,
	b.address_id AS b_address_id
FROM
	store AS a
RIGHT OUTER JOIN address AS b ON
	a.address_id = b.address_id;
  • SELECT: 나열된 4개 열을 조회한다. AS 키워드를 사용하여 열의 별칭을 설정하였다.
  • FROM: 좌측 테이블을 store 테이블로 지정한다.
  • JOIN: store 테이블과 address 테이블을 조인 조건에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN한다.
  • ON: store 테이블의 address_id 열과 address 테이블의 address_id 열을 조인키로 사용한다.

예제 10:

SELECT
	a.address_id AS a_address_id, a.store_id,
	b.address, b.address_id AS b_address_id
FROM store AS a
	LEFT OUTER JOIN address AS b ON a.address_id = b.address_id
WHERE b.address_id IS NULL
 
UNION
 
SELECT
	a.address_id AS a_address_id,
	a.store_id,
	b.address,
	b.address_id AS b_address_id
FROM
	store AS a
RIGHT OUTER JOIN address AS b ON
	a.address_id = b.address_id
WHERE
	a.address_id IS NULL;
  • SELECT: 나열된 4개 열을 조회한다. AS 키워드를 사용하여 열의 별칭을 설정하였다.
  • FROM: 좌측 테이블을 store 테이블로 지정한다.
  • JOIN: store 테이블과 address 테이블을 조인 조건에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN한다.
  • ON: store 테이블의 address_id 열과 address 테이블의 address_id 열을 조인키로 사용한다.
  • WHERE: store 테이블의 address_id 열이 NULL인 행을 선택하기 위한 조건이다.

예제 11:

SELECT
	a.num,
	b.name
FROM
	doit_cross1 AS a
CROSS JOIN doit_cross2 AS b
ORDER BY
	a.num;
  • SELECT: 나열된 2개 열을 조회한다.
  • FROM: 좌측 테이블을 doit_cross1 테이블로 지정한다.
  • JOIN: doit_cross1 테이블과 doit_cross2 테이블을 교차 조인한다.

예제 12:

SELECT
	a.num,
	b.name
FROM
	doit_cross1 AS a
CROSS JOIN doit_cross2 AS b
WHERE
	a.num = 1;
  • SELECT: 나열된 2개 열을 조회한다.
  • FROM: 좌측 테이블을 doit_cross1 테이블로 지정한다.
  • JOIN: doit_cross1 테이블과 doit_cross2 테이블을 교차 조인한다.
  • WHERE: doit_cross1 테이블의 num 열이 1인 행을 선택하기 위한 조건이다.

예제 13:

SELECT 
	a.customer_id AS a_customer_id,
	b.customer_id AS b_customer_id
FROM
	customer AS a
INNER JOIN customer AS b ON
	a.customer_id = b.customer_id
  • SELECT: 나열된 2개 열을 조회한다. AS 키워드를 사용하여 열의 별칭을 설정하였다.
  • FROM: 좌측 테이블을 customer 테이블로 지정한다.
  • JOIN: customer 테이블을 SELF JOIN한다.
  • ON: customer 테이블의 customer_id 열을 조인키로 사용한다.

예제 14:

SELECT
	a.payment_id,
	a.amount,
	b.payment_id,
	b.amount,
	b.amount - a.amount AS profit_amount
FROM
	payment AS a
LEFT OUTER JOIN payment AS b ON
	a.payment_id = b.payment_id -1;
  • SELECT: 나열된 5개 열을 조회한다. AS 키워드를 사용하여 열의 별칭을 설정하였다.
  • FROM: 좌측 테이블을 payment 테이블로 지정한다.
  • JOIN: payment 테이블을 SELF LEFT OUTER JOIN한다.
  • ON: payment 테이블의 payment_id 열을 조인키로 사용한다.

하위 태스크 2

테이블 관계 구조 파악

customer/address/city 등의 관계를 다이어그램 또는 표로 정리

customer 테이블, address 테이블, city 테이블의 정의를 분석한 결과를 다이어그램으로 정리하였다.

customer 테이블:

customer 테이블의 기본키는 customer_id 열이다. address_id 열이 정의되어 있으며 해당 열의 데이터 유형은 address 테이블의 address_id 열과 같다.

SHOW COLUMNS FROM customer;

address 테이블:

address 테이블의 기본키는 address_id 열이다. city_id 열이 정의되어 있으며 해당 열의 데이터 유형은 city 테이블의 city_id 열과 같다.

SHOW COLUMNS FROM address;

city 테이블:

city 테이블의 기본키는 city_id 열이다.

SHOW COLUMNS FROM city;

하위 태스크 3 ~ 4

INNER/OUTER JOIN 비교

동일한 테이블 조합으로 INNER/LEFT/RIGHT JOIN 결과 비교

NULL 필터링 연습

OUTER JOIN 결과에서 NULL 만 골라 누락 데이터 찾기

INNER JOIN 결과:

SELECT
	a.customer_id,
	a.store_id,
	a.first_name,
	a.last_name,
	a.email,
	a.address_id AS a_address_id,
	b.address_id AS b_address_id,
	b.address,
	b.district,
	b.city_id,
	b.postal_code,
	b.phone,
	b.location
FROM
	customer AS a
INNER JOIN address AS b ON
	a.address_id = b.address_id
WHERE
	a.first_name = 'ROSA';

LEFT OUTER JOIN 결과:

SELECT
	a.address,
	a.address_id AS a_address_id,
	b.address_id AS b_address_id,
	b.store_id
FROM
	address AS a
LEFT OUTER JOIN store AS b ON
	a.address_id = b.address_id
WHERE
	b.address_id IS NULL;

WHERE b.address_id IS NULL 절을 추가하여 매장에 연결되지 않은 주소를 조회했다.

RIGHT OUTER JOIN 결과:

SELECT
	a.address_id AS a_address_id,
	a.store_id,
	b.address,
	b.address_id AS b_address_id
FROM
	store AS a
RIGHT OUTER JOIN address AS b ON
	a.address_id = b.address_id
WHERE
	a.address_id IS NULL;

WHERE a.address_id IS NULL 절을 추가하여 매장에 연결되지 않은 주소를 조회했다.

하위 태스크 5

GROUP BY/집계 쿼리 작성

고객별/카테고리별/도시별 집계 리포트 쿼리 작성

다음 SELECT 문은 고객별 총 결제 금액과 결제 횟수를 조회한다.

SELECT
	c.customer_id,
	c.first_name ,
	c.last_name,
	SUM(p.amount),
	COUNT(*)
FROM
	customer c
JOIN payment p ON
	c.customer_id = p.customer_id
GROUP BY
	c.customer_id;

다음 SELECT 문은 영화 카테고리별 보유 영화 수를 조회한다.

SELECT
	c.name ,
	COUNT(*)
FROM
	film_category fc
JOIN category c ON
	fc.category_id = c.category_id
GROUP BY
	fc.category_id ;

다음 SELECT 문은 도시별 고객 수를 조회한다.

SELECT
	c2.city, COUNT(*)
FROM
	customer c
JOIN address a ON
	c.address_id = a.address_id
JOIN city c2 ON
	a.city_id = c2.city_id
GROUP BY
	c2.city_id;

하위 태스크 6

HAVING 절 활용

집계 결과에 조건을 적용해 특정 그룹만 필터링

다음 SELECT 문은 고객별 총 결제 금액과 결제 횟수를 조회하는 SELECT 문에 HAVING 절을 추가하고 일부 열을 조회 대상에서 제외하여 총 결제 금액이 100$ 이상인 고객을 출력한다.

SELECT
	c.first_name ,
	c.last_name,
	SUM(p.amount) AS total_amount
FROM
	customer c
JOIN payment p ON
	c.customer_id = p.customer_id
GROUP BY
	c.customer_id
HAVING
	total_amount > 100;

하위 태스크 7

JOIN + GROUP 리포트 템플릿

조인과 집계를 결합한 리포트 쿼리를 스크립트로 정리

다음 SELECT 문은 셀프 조인과 공통 테이블 표현식을 사용해서 도시별 상위 3명 VIP 고객 목록을 조회한다.

WITH customer_spending AS (
SELECT
	c2.city_id,
	c.customer_id,
	SUM(p.amount) AS total_amount
FROM
	customer c
JOIN payment p ON
	c.customer_id = p.customer_id
JOIN address a ON
	c.address_id = a.address_id
JOIN city c2 ON
	a.city_id = c2.city_id
GROUP BY
	c.customer_id,
	c2.city_id
)
SELECT
	t1.city_id,
	t1.customer_id,
	t1.total_amount
FROM
	customer_spending t1
LEFT JOIN customer_spending t2 ON
	t1.city_id = t2.city_id
	AND t1.total_amount < t2.total_amount
GROUP BY
	t1.city_id,
	t1.customer_id,
	t1.total_amount
HAVING
	COUNT(t2.customer_id) < 3
ORDER BY
	t1.city_id,
	t1.total_amount DESC;

다음 SELECT 문은 카테고리별 평균 대여 기간을 조회한다.

SELECT
	c.name,
	AVG(f.rental_duration)
FROM
	category c
JOIN film_category fc ON
	c.category_id = fc.category_id
JOIN film f ON
	f.film_id = fc.film_id
GROUP BY fc.category_id;

그래프 뷰