Queries with multiple tables

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