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

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

하나도 행이 안나오는 문제

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를 사용해 중간 결과를 확인하여 문제의 원인을 좁힙니다.

에러 코드 문제

1. 에러 코드 1060: Duplicate column name 'category_id'

   • 여러 테이블을 조인할 때 동일한 이름의 열(category_id)이 두 개 이상의 테이블에 존재하는 경우 발생할 수 있습니다.

   • 이는 SELECT 문의 *를 사용할 때 중복된 열 이름을 구별할 수 없기 때문입니다.

2. 결과 행이 반환되지 않음

   • 조인 조건이 적절하지 않거나 테이블 간 연결 기준이 충족되지 않을 때 발생할 수 있습니다.

   • 데이터가 누락되었거나, 조건이 정확히 일치하지 않는 문제가 있을 수 있습니다.

원인:

1. 중복된 열 이름

   • category_id 같은 중복 열 이름이 SELECT 시 충돌을 일으킴.

   • 조인 결과에 명시적으로 별칭을 지정하지 않으면 시스템은 어떤 category_id를 반환할지 결정하지 못합니다.

2. 조인 조건의 문제

   • INNER JOIN을 사용할 경우 조건이 맞는 행만 반환합니다. 조건이 맞는 데이터가 없는 경우 결과가 빈 상태로 반환됩니다.

   • 테이블 간에 연결 고리가 되는 키 값이 실제 데이터에서 존재하지 않을 수도 있습니다.

해결책:

1. 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)을 추가합니다.

2. 데이터 확인 및 조인 조건 점검

   • 데이터가 정확히 매칭되는지 확인하세요:

     SELECT * FROM table_a WHERE category_id IS NOT NULL;
     SELECT * FROM table_b WHERE category_id IS NOT NULL;

   • 두 테이블 간의 연결 키(category_id)에 데이터가 정확히 존재하고 일치하는지 점검합니다.

3. 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 데이터를 확인할 수 있습니다.

감사합니다.