Learn SQL: Table aliases
Writing less with aliases
Yasmine looks over your shoulder. "Do you really have to type `matches.` and `teams.` every time? My fingers hurt just watching." No, there's a shorter way.
A nickname per table
After a table name you may put an alias, a short nickname: players AS p, or without AS simply players p. From then on you write p.name everywhere instead of players.name.
The alias only applies within that one query.
SELECT p.name, p.position, t.name FROM players p JOIN teams t ON p.team_id = t.id| name | position | name |
|---|---|---|
| Jonas Verhoeven | goalkeeper | Men A |
| Wout Hermans | defender | Men A |
| Bram De Smet | midfielder | Men A |
| Yannick Diallo | forward | Men A |
| Elke Van Dyck | goalkeeper | Women A |
| Nora Aerts | midfielder | Women A |
| Charlotte Maes | forward | Women A |
| Ines Goossens | defender | Women A |
Ambiguous columns
Both tables have a name column and an id column. Write just name and SQLite doesn't know which one you mean, so it gives the error *ambiguous column name*. A column that exists in only one table may go without an alias.
In the result both columns are still called name. With AS you give them a clear heading.
SELECT p.name AS player, t.name AS team, category FROM players p JOIN teams t ON p.team_id = t.id| player | team | category |
|---|---|---|
| Jonas Verhoeven | Men A | seniors |
| Wout Hermans | Men A | seniors |
| Bram De Smet | Men A | seniors |
| Yannick Diallo | Men A | seniors |
| Elke Van Dyck | Women A | seniors |
| Nora Aerts | Women A | seniors |
| Charlotte Maes | Women A | seniors |
| Ines Goossens | Women A | seniors |
Try it yourself
For every team with a coach, show the team name and the coach's name. Use aliases.
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson