BigQuery执行Join时为何生成重复列Id_1和Date_1？

Hey there! Let's break down why those duplicate columns are showing up and how to fix your query.

The Root Cause #

When you use SELECT * for a JOIN operation, BigQuery pulls every column from both tables you're combining. Since both daily_Activity and sleep_day have Id and Date fields, BigQuery automatically renames the duplicates from the second table with a _1 suffix to avoid naming conflicts. That’s exactly where Id_1 and Date_1 come from—they’re just the Id and Date values from the sleep_day table, which match your join keys perfectly (since you used those fields to link the tables in the first place).

Fixes to Try #

1. Explicitly Select Only the Columns You Need (Recommended)

Instead of SELECT *, list out the specific columns you want to keep. This keeps your results clean and eliminates unnecessary duplicate data. Using table aliases also makes your query shorter and easier to read:

SELECT 
  da.*, -- Grab all columns from daily_Activity
  sd.TotalSleepRecords, -- Add specific sleep_day columns you need
  sd.TotalMinutesAsleep,
  sd.TotalTimeInBed
FROM
  `bellabeat-case-study-373821.bellabeat_case_study.daily_Activity` da
JOIN
  `bellabeat-case-study-373821.bellabeat_case_study.sleep_day` sd
ON
  da.Id = sd.Id
  AND da.Date = sd.Date

This way, you only get the Id and Date from daily_Activity (plus the sleep metrics you care about) with no redundant duplicates.

2. Rename Duplicate Columns (If You Really Need Them)

If for some edge case you need to keep both sets of Id/Date columns, you can manually rename them to clarify their source. Use EXCEPT to avoid re-including the renamed fields:

SELECT 
  da.Id AS Activity_Id,
  da.Date AS Activity_Date,
  da.* EXCEPT(Id, Date), -- Get all other daily_Activity columns
  sd.Id AS Sleep_Id,
  sd.Date AS Sleep_Date,
  sd.* EXCEPT(Id, Date) -- Get all other sleep_day columns
FROM
  `bellabeat-case-study-373821.bellabeat_case_study.daily_Activity` da
JOIN
  `bellabeat-case-study-373821.bellabeat_case_study.sleep_day` sd
ON
  da.Id = sd.Id
  AND da.Date = sd.Date

Note: This is rarely necessary since your join condition guarantees da.Id = sd.Id and da.Date = sd.Date—duplicating these columns just adds redundant data.

Quick Recap #

SELECT * is convenient but risky for joins because it pulls everything, including duplicate field names. Being explicit about your columns will make your query more readable and your results free of unnecessary clutter.

内容的提问来源于stack exchange，提问作者Jose Aldana