-2

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 ...
philipxy
  • 14,867
  • 6
  • 39
  • 83
Cyber
  • 11
  • 4
  • Does this answer your question? [When to use SQL Table Alias](https://stackoverflow.com/questions/198196/when-to-use-sql-table-alias) – philipxy Oct 28 '22 at 17:22

2 Answers2

1

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • my question regards the fact that i need to specify the table name twice even if the column name is not the same in both the tables i use in the query – Cyber Oct 28 '22 at 20:55
  • But that's not true. You don't have to specify the table name twice. Qualifying columns with the table name is optional. Why do you think you have to specify the table name? – Bill Karwin Oct 28 '22 at 22:27
  • It's like saying "why do I have to put mustard in my sandwich?" It's asking "why" about a counterfactual statement. Sometimes it's a great thing to put mustard in a sandwich. But it's not always needed, nor required. – Bill Karwin Oct 28 '22 at 22:34
  • so im a beginner at sql, and im practicing queries in HackerRank (a coding practice-website) and when i submit my answer without stating the table name as i explained above (twice, while having a unique column name for each table), it says i have an error. so... yeah – Cyber Oct 29 '22 at 13:46
  • That's up to HackerRank's idea of the correct answer. It's not required by SQL. Like I wrote, qualified column names are required sometimes, and I guess HackerRank decided it was easier to require qualified column names in all cases than to deal with the subtlety of figuring out when they're required and when they're not. – Bill Karwin Oct 29 '22 at 14:12
0

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.

Siqq0
  • 21
  • 3
  • my question regards the fact that i need to specify the table name twice even if the column name is not the same in both the tables i use in the query – Cyber Oct 28 '22 at 20:55