INNER JOIN查询仅返回team表字段，如何获取team_info表所有字段？

Hey there! The issue you're facing happens because both your team and team_info tables share duplicate column names (country_id exists in both, and team.id maps to team_info.team_id). When you use SELECT *, the database does return all columns, but when PDO maps results to an associative array, later columns with the same name overwrite earlier ones—making it look like only team table fields are coming through.

Here are two reliable fixes:

1. Explicitly list all fields with aliases (Recommended) #

This is the cleanest approach: it avoids ambiguity, makes your query readable, and ensures you don’t accidentally include unwanted fields. Use table aliases to distinguish duplicate column names:

SELECT 
    -- Team table fields (aliased for clarity where needed)
    team.id AS team_id,
    team.country_id AS team_country_id,
    team.link,
    team.name,
    team.update_at,
    -- Team_info table fields
    team_info.country_id AS info_country_id,
    team_info.venue_id,
    team_info.shield_link,
    team_info.founded,
    team_info.address,
    team_info.phone,
    team_info.fax,
    team_info.email,
    team_info.official_site
FROM team 
INNER JOIN team_info ON team.id = team_info.team_id 
WHERE team.id = :team_id

By aliasing country_id from both tables, you ensure both values are preserved without overwriting each other.

2. Use table-prefixed wildcards (Shorter but less explicit) #

If you want to avoid listing every field, use table-specific wildcards. Just note that duplicate column names will still overwrite each other—handle those separately if you need both values:

SELECT team.*, team_info.* 
FROM team 
INNER JOIN team_info ON team.id = team_info.team_id 
WHERE team.id = :team_id

In this case, team_info.country_id will overwrite team.country_id in the associative array. If you need both, explicitly alias at least one of them:

SELECT team.*, team_info.country_id AS info_country_id, team_info.*
FROM team 
INNER JOIN team_info ON team.id = team_info.team_id 
WHERE team.id = :team_id

Quick note on PDO fetch behavior #

By default, fetchAll() uses PDO::FETCH_ASSOC, which uses column names as array keys. To see all columns (including duplicates) without overwriting, you could use PDO::FETCH_NUM (numeric indexes) or PDO::FETCH_BOTH (both numeric and associative), but associative arrays with aliases are almost always more usable in applications.

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