SQL inner join query returns two identical columns

Question

Let's say I have the following SQL query:

SELECT *
FROM employee 
INNER JOIN department ON employee.EmpID = department.EmpID

I wanted to ask, why I am getting two EmpID columns, and how can I get only one of those, preferably the first.

I'm using SQL server

Answer 1

SELECT employee.EmpID, employee.name, ...
FROM employee 
INNER JOIN department ON employee.EmpID=department.EmpID

Be precise and specify which columns you need instead of using the astrisk to select all columns.

Answer 2

You get all columns from these two tables, that's why you have two EmpID columns. The only JOIN type that removes common column is NATURAL JOIN, which is not implemented by SQL Server. Your query would look then like this:

SELECT *
FROM employee 
NATURAL JOIN department

This generates join predicates by comparing all columns with the same name in both tables. The resulting table contains only one column for each pair of equally named columns.