4

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

animuson
  • 53,861
  • 28
  • 137
  • 147
Amir.F
  • 1,911
  • 7
  • 29
  • 52

6 Answers6

6
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.

Jacob
  • 41,721
  • 6
  • 79
  • 81
  • i should have mentioned it before, i am aware that it works if you specify all the columns you want, but i wondered if there was any other way to get this without specifying all the columns – Amir.F Aug 23 '11 at 08:09
5

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.

piotrp
  • 3,755
  • 1
  • 24
  • 26
1

You're getting all columns from all tables involved in your query since you're asking for it: SELECT *

If you want only specific column - you need to specify which ones you want:

SELECT e.EmpID, e.Name as 'Employee Name', d.Name AS 'Department Name'
FROM employee e 
INNER JOIN department d ON e.EmpID = d.EmpID
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    i should have mentioned it before, i am aware that it works if you specify all the columns you want, but i wondered if there was any other way to get this without specifying all the columns – Amir.F Aug 23 '11 at 08:10
  • @user765964: no - either you use `SELECT *` and you get **everything** - or you have to specify **what you want** - there's no other way.. – marc_s Aug 23 '11 at 08:33
  • In general, Crack's comment above provides a more elegant way to do what OP is after, alas, there is no support for SQL Server. – gnzg Nov 12 '18 at 10:29
0

Don't use *. Specify the columns you want in the field list.

SELECT E.EmpID, E.EmpName -- etc 
FROM employee as E
  INNER JOIN department as D
    ON E.EmpID=D.EmpID
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • i should have mentioned it before, i am aware that it works if you specify all the columns you want, but i wondered if there was any other way to get this without specifying all the columns – Amir.F Aug 23 '11 at 08:09
0

As stated by others, don't use *

See this SO question for reasons why:

Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc

Community
  • 1
  • 1
Curtis
  • 101,612
  • 66
  • 270
  • 352
0

Essentially, the answer to your question is that the output from a SQL SELECT query is not a relation, and therefore if you do not take care you may end up with duplicate attribute names (columns) and rows.

Standard SQL has some constructs to mitigate SQL's non-relational problems e.g. NATURAL JOIN would ensure the result has only one EmpID attribute. Sadly, SQL Server does not support this syntax but you can vote for it here.

Therefore, you are forced to write out in long-hand the columns you want, using the table name to qualify which attribute you prefer e.g. employee.EmpID.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138