Including the substring "_id" in column names I have a table of Teams. Each Team has an ID.
I have another table that holds Players. Each Player is assigned to a team.

So my tables might look something like

CREATE TABLE players (
   id        INTEGER   PRIMARY KEY
   name      STRING
   team_id   INTEGER   REFERENCES teams(id)
)

CREATE TABLE teams (
   id        INTEGER PRIMARY KEY
   name      STRING
)


Does writing _id make it any more clearer that the column is meant to hold an ID of a team (as opposed to maybe a team name)?

If I saw that team was linked to a key on the teams table I would assume the column is meant to hold an ID.

I find that if I started following a convetion where I tack on _id I'll end up with a lot of columns with _id in them, but am not sure whether it adds any value.
This is a matter of style, and likely to get religious.  Personally, I do find it valuable to explicitly label ID fields TeamID and PlayerID; as you say, it makes it perfectly clear that the field stores an ID and not some other type of data, such as a label.  Being explicit is usually more valuable than being brief, in the long term.

I prefer using PlayerID, TeamID, etc. over just ID, but offhand I can't give you a compelling reason.  It means that your joins are consistently X to X (P.TeamID = T.TeamID), whereas P.ID and T.ID are very different fields which should not be joined.  It's easier to confuse P.ID and T.ID when glancing through a query than P.PlayerID and T.TeamID.  On the other hand, there's value in consistently using the same field name for your PK, too.