I am confused, how is sql allowing me to select multiple columns with same name.
as of my knowledge, it should show column ambiguous. How is it allowing like this, and what is the use of it ???
Asked
Active
Viewed 3,272 times
0
Learner
- 1,286
- 6
- 34
- 57
-
1-1 because the duplicate question was very, very easy to find on SO. – Frank Schmitt Jun 10 '14 at 12:04
-
actually ambiguity will come into picture when some qualification in say where or having or ON clause is added with same column name. – Deepshikha Jun 10 '14 at 12:11
-
The possible duplicate provided by @FrankSchmitt has some meaningful reasons. – Learner Jun 10 '14 at 12:17
2 Answers
4
In your first query, you are actually selecting the same column 3 times. Nothing wrong with that and no ambiguity for the query engine.
In the second query, you are using the table alias prefix to select EmpName from E1 twice, and EmpName from E2 once (which is the same table - nothing wrong with that either, and no ambiguity for the query engine).
If you want to avoid ambiguity in the output, make sure to alias the columns you select:
SELECT E1.EmpName AS [Employee Name], E2.EmpName AS [ManagerName]
FROM tblEmp E1 INNER JOIN tblEmp E2 ON E2.EmpId = E1.MgrId
Dan
- 10,480
- 23
- 49
-
Ambiguity in the output will become a problem if you try to use the query to create a view. – Walter Mitty Jun 10 '14 at 12:16
0
That is the way it works. sql lets you select columns of the same name. but it is going to show column ambiguous when you include it in a where condition. to avoid that use alias
ElenA
- 78
- 6