Why must I state the table I'm referring to TWICE when using a join on a query?

select table1.name
from table1
inner join table2
on etc ...

CodePudding user response：

You don't always have to qualify the column name.

But if both tables of the join have a column name, then it would be ambiguous if you refer to the column as simply name in the select-list. You need to qualify the column with the table name to make it clear which table's column to use.

I think it's a good habit to qualify all my columns, though it is not strictly needed in many cases. But it makes the query more readable. For example, look at this query:

select name, address, created_at from table1
inner join table2 on etc...

Even if each column is unambiguous, that is it comes from only one table, someone reading this code would wonder which table do each of the three columns in the select-list belong to? They would have to go check the table definitions for each case. It slows down their understanding of the query while reading code.

It's a good idea to help out those who follow you.

CodePudding user response：

If both of those tables have the same column names you want to display, the sql server exactly needs to know which column(s) it has to display. Thats why you have to use the tablename in the column specification.