Should I use table.column or just column identifiers in a SQL join?

Question

Which shape is best? Because my teacher does it this way :

SELECT board1.column1, board2.column2 
FROM board1 
JOIN board2 
    ON board1.common_column = board2.common_column;

But I think it's better and simpler:

SELECT column1, column2 
FROM board1 
JOIN board2 
    ON board1.common_column = board2.common_column;

which is the most correct?

Answer 1

There is no objective "best". Different techniques have pros and cons.

Your question comes down to whether to use table.column or just column to identify columns. It comes down to how much possible ambiguity you can accept.

For example, in a simple one table query there is no possible ambiguity. select this, that from some_table there is no ambiguity for the reader nor for the database that this refers to some_table.this.

In a join, there is ambiguity.

SELECT column1, column2 
FROM board1 
JOIN board2 
    ON board1.common_column = board2.common_column;

Does column1 refer to board1.column1 or board2.column1? The reader must be familiar with the schema of board1 and board2 to know.

Also consider the future. Right now there may be no conflict, but what if later board2 adds a column1? Now your query is ambiguous and the formerly correct query now raises an error.

---

When there are multiple tables in a query, the column names should be made unambiguous. However, table names can get quite long, so use table aliases.

SELECT b1.column1, b2.column2 
FROM board1 b1
JOIN board2 b2
    ON b1.common_column = b2.common_column;

This keeps things short, while also being unambiguous.

Make sure your alias relates to the table name. Don't use something too generic like a and b or t1 and t2.

Table aliases become necessary when doing self-joins, joining a table with itself.

---

Sometimes you might see people including the names of the table in the column names.

create table customers (
  customer_id integer primary key,
  customer_name text,
  customer_address text
);

-- verbose whether you need it or not
select customer_id, customer_name
from customers;

This forces queries on the table to always be verbose and (probably) unique even if that's not necessary. IMO it is better to use short, relative names and table aliases as needed in the query.

create table customers (
  id integer primary key,
  name text,
  address text
);

create table businesses (
  id integer primary key,
  name text,
  address text
);

create table business_customers (
  customer_id integer references customers,
  business_id integer references businesses
);

select id, name
from customers;

-- An example of a many-to-many join between tables with
-- ambiguous column names.
select c.id, c.name, b.name
from business_customers bc
join customers c on bc.customer_id = c.id
join businesses b on bc.business_id = b.id

Demonstration.