So far, we have always addressed exactly one table in our queries. That will change in this lesson. Because combining spreadsheets makes things possible that go beyond a simple spreadsheet like Excel.
Table names and aliases
Since columns from more than one table can now occur, the column names are not always unique. Therefore, it may be necessary to mention the tables when we refer to the columns, for example in the field list or in the WHERE clause.
Ambiguous column names
SELECT id, firstname FROM tasks INNER JOIN users ON assignee = id
In this example, the column name ID is not unique because it occurs in both tables. Accordingly, an error message is displayed:
“column ambiguously defined: ID: line 5 col 17 (at pos 81)”
Column names with table names
SELECT tasks.id users.firstname FROM tasks INNER JOIN users ON tasks.assignee = users.id
In this case, the column names are unique because the table name was always specified. But this is often a lot of paperwork and not very flexible.
If a table occurs more than once, it will no longer work this way.
Column names with table alias
SELECT t.id u.firstname FROM tasks AS t INNER JOIN users AS u ON t.assignee = u.id
If we define an alias for the table with AS , then we can refer to it for column accesses.
It is recommended to always assign aliases. This can also be used to further define the role of the table in this query. For example, if a table USERS is used in a query once for project managers and once for agents (ASSIGNEE), you can choose the corresponding alias names.
Assign column alias with AS
With the keyword AS after a column you can rename it. For example, if the ID comes from multiple tables such as TASKS and USERS, then you can adjust the name accordingly.
SELECt t.id AS task_id, u.id AS user_id FROM tasks AS t LEFT OUTER JOIN users AS u ON t.assignee = u.id