How to join duplicate column name in joins in SQL Server?

Question

I have two tables, one is Employee and other one is Dept.

The Employee table has columns which are ID, Name, and DeptId, and Dept table has columns ID, DeptName.

Now if write a query:

Select 
    ID, Name, ID, DeptName
from 
    Employee
Inner Join 
    Dept On Employee.DeptID = Dept.ID

I get an error because it doesn't know which ID column I mean. How do I uniquely define columns?

Answer 1

Just include explicit aliases to distiguish the ID column in the Employee table from the ID column in the Dept table. Actually, it is best practice to always refer to a column by an alias when doing a join, so your query should look something like this:

SELECT e.ID AS employeeID,
       e.Name,
       e.DeptId,
       d.ID AS deptID,
       d.DeptName
FROM Employee e
INNER JOIN Dept d
    ON e.DeptID = d.ID

Answer 2

Use an Alias

Select E.ID   as EmpID
     , E.Name
     , D.ID   as DepID
     , D.DeptName  
 From Employee E
 Inner Join Dept D 
 On E.Employee.DeptID = D.Dept.ID

Answer 3

You can also use the table name as qualifier instead of an alias:

Select 
    Employee.ID, Employee.Name, Dept.ID, Dept.DeptName
from 
    Employee
Inner Join 
    Dept On Employee.DeptID = Dept.ID