중급 연습문제 25번

안녕하세요. 답변 도우미입니다. 문의가 두개 인것 같아요. 각각에 대해 참고해보시면 좋을 만한 부분을 작성하여 공유드립니다.

하나도 행이 안나오는 문제

1. 조인 순서와 조건 확인

연결되는 테이블들은 아래와 같은 구조를 가집니다:

category → film_category (카테고리와 영화 연결)
film_category → film (영화 상세 정보)
film → inventory (재고 연결)
inventory → store (스토어 연결)
store → staff (스태프 정보)
staff → payment (결제 정보)

이 연결 구조에서 하나의 조인이라도 조건에 맞는 데이터가 없다면 전체 결과가 비게 됩니다.

2. 에러 원인 확인

(1) 테이블 간 데이터 누락 가능성

특정 테이블들 간의 매칭이 정확히 이루어지지 않을 가능성이 있습니다. 예를 들어:

inventory와 store 간 연결에서, 특정 store_id가 없을 수 있음.
staff와 payment 간 연결에서, 특정 스태프의 결제 데이터가 없을 수 있음.

이는 주어진 조인 조건들이 INNER JOIN을 사용하는데, 조건을 만족하지 못하는 경우 해당 데이터가 필터링되기 때문입니다.

(2) 데이터 정확성 점검

각 테이블에서 연결 기준이 되는 열에 적절한 데이터가 있는지 확인해야 합니다. 예를 들어:

inventory.film_id와 film.film_id의 데이터가 매칭되는지 확인.
payment.staff_id가 staff.staff_id에 모두 존재하는지 확인.

3. 해결 방안

(1) 각 테이블의 데이터 점검

아래와 같은 방식으로 각 조인 간의 데이터가 잘 연결되는지 확인합니다:

-- Step 1: category와 film_category 간 연결 확인
SELECT *
FROM category C
JOIN film_category FC ON C.category_id = FC.category_id
LIMIT 10;

-- Step 2: film_category와 film 간 연결 확인
SELECT *
FROM film_category FC
JOIN film F ON FC.film_id = F.film_id
LIMIT 10;

-- Step 3: inventory와 film 간 연결 확인
SELECT *
FROM inventory I
JOIN film F ON I.film_id = F.film_id
LIMIT 10;

-- Step 4: payment와 staff 간 연결 확인
SELECT *
FROM payment P
JOIN staff STA ON P.staff_id = STA.staff_id
LIMIT 10;

이 과정을 통해 어느 조인에서 데이터가 없어서 결과가 반환되지 않는지 확인할 수 있습니다.

(2) LEFT JOIN으로 데이터 누락 확인

INNER JOIN 대신 LEFT JOIN을 사용하면 누락된 데이터를 확인할 수 있습니다:

SELECT C.name AS category_name, P.amount
FROM category C
LEFT JOIN film_category FC ON C.category_id = FC.category_id
LEFT JOIN film F ON FC.film_id = F.film_id
LEFT JOIN inventory I ON F.film_id = I.film_id
LEFT JOIN store STO ON I.store_id = STO.store_id
LEFT JOIN staff STA ON STO.store_id = STA.store_id
LEFT JOIN payment P ON STA.staff_id = P.staff_id
WHERE P.amount IS NULL -- 누락된 데이터 확인
LIMIT 10;

(3) 최종 쿼리 수정

조건부로 LEFT JOIN을 검토하고, 중복 열 문제를 피하기 위해 필요한 열만 SELECT 및 GROUP BY에 명시적으로 기입하세요:

CREATE OR REPLACE VIEW top5_genres AS
SELECT
    C.name AS Genre,
    SUM(P.amount) AS Total_Sales
FROM
    category C
JOIN film_category FC ON C.category_id = FC.category_id
JOIN film F ON FC.film_id = F.film_id
JOIN inventory I ON F.film_id = I.film_id
JOIN store STO ON I.store_id = STO.store_id
JOIN staff STA ON STO.store_id = STA.store_id
JOIN payment P ON STA.staff_id = P.staff_id
GROUP BY C.name
ORDER BY SUM(P.amount) DESC
LIMIT 5;

4. 추가적인 점검 사항

데이터 무결성: sakila 데이터베이스는 샘플 데이터베이스로, 특정 데이터가 누락되었을 가능성이 있습니다.
결과 확인: 각 조인 단계에서 LIMIT를 사용해 중간 결과를 확인하여 문제의 원인을 좁힙니다.

에러 코드 문제

에러 코드 1060: Duplicate column name 'category_id'
- 여러 테이블을 조인할 때 동일한 이름의 열(category_id)이 두 개 이상의 테이블에 존재하는 경우 발생할 수 있습니다.
- 이는 SELECT 문의 *를 사용할 때 중복된 열 이름을 구별할 수 없기 때문입니다.
결과 행이 반환되지 않음
- 조인 조건이 적절하지 않거나 테이블 간 연결 기준이 충족되지 않을 때 발생할 수 있습니다.
- 데이터가 누락되었거나, 조건이 정확히 일치하지 않는 문제가 있을 수 있습니다.

원인:

중복된 열 이름
- category_id 같은 중복 열 이름이 SELECT 시 충돌을 일으킴.
- 조인 결과에 명시적으로 별칭을 지정하지 않으면 시스템은 어떤 category_id를 반환할지 결정하지 못합니다.
조인 조건의 문제
- INNER JOIN을 사용할 경우 조건이 맞는 행만 반환합니다. 조건이 맞는 데이터가 없는 경우 결과가 빈 상태로 반환됩니다.
- 테이블 간에 연결 고리가 되는 키 값이 실제 데이터에서 존재하지 않을 수도 있습니다.

해결책:

SELECT 절에 명시적 컬럼 지정

SELECT 
    a.column_name1, 
    b.column_name2, 
    a.category_id AS a_category_id, 
    b.category_id AS b_category_id
FROM table_a a
INNER JOIN table_b b
ON a.category_id = b.category_id;

* 대신 사용할 열을 명시적으로 지정하고, 필요한 경우 별칭(AS)을 추가합니다.

데이터 확인 및 조인 조건 점검
- 데이터가 정확히 매칭되는지 확인하세요:
```
SELECT * FROM table_a WHERE category_id IS NOT NULL;
SELECT * FROM table_b WHERE category_id IS NOT NULL;
```
- 두 테이블 간의 연결 키(category_id)에 데이터가 정확히 존재하고 일치하는지 점검합니다.
LEFT JOIN 사용 (필요 시)
- 데이터 누락 가능성을 확인하기 위해 LEFT JOIN을 사용해 보세요:
```
SELECT a.*, b.*
FROM table_a a
LEFT JOIN table_b b
ON a.category_id = b.category_id;
```
- 이렇게 하면 table_a의 모든 데이터와 매칭되지 않은 table_b 데이터를 확인할 수 있습니다.

감사합니다.

인프런 커뮤니티 질문&답변