SQL select without showing joining columns in Joins

Question

How do i exclude duplicate columns of joining keys when we do a join?

Answer 1

I assume you mean you don't want to show them in the results. If so the way is not to use select * but to explicitly list every column by name in the select then you can choose the columns you want.

This is good practice anyway as when extra columns are added or columns you don't care about change then your query returns the same results and so you do not need to alter any code reading the results.

Also as a note if you have a column in the join criteria you do not need to show it in the result.

Answer 2

Don't do SELECT *, instead explicitly name your columns, e.g.:

SELECT a.ID, a.AccountNumber, b.Name, b.DOB
FROM Account a
JOIN Person b on a.ID = b.ID

Answer 3

Use an explicit column list or a NATURAL JOIN or USING (col) if your RDBMS supports this syntax. Oracle does SQL Server doesn't. (the question is tagged SQL with no particular flavour indicated)

Answer 4

Use alias of the coloumns e.g:

SELECT u.id as uid, u.name as username, s.id as sid  
FROM user u
JOIN SOMETHIG s on u.id = s.user_id