Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL inner join query returns two identical columns

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

like image 505
Amir.F Avatar asked Dec 16 '22 10:12

Amir.F


2 Answers

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.

like image 96
Jacob Avatar answered Dec 19 '22 08:12

Jacob


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.

like image 36
piotrp Avatar answered Dec 19 '22 07:12

piotrp