Alternatives to USING in a JOIN for SQL Server for avoiding duplicated columns in joined table

Question

SQL Server does not offer the keyword USING in the context of a JOIN,
nor it provides a NATURAL JOIN.

Besides explicitly (manually) listing all the columns (link to otherwise duplicated question), is there an alternative to obtain a table in which the columns I am joining onto, which have the same name in the 2 joined tables, are not duplicated?

As an intermediate step, I have tried to save in a temporary table the result

SELECT INTO #MyTempTable * FROM tableA 
INNER JOIN tableB 
ON tableA.commonColumn = tableB.commonColumn;

But I already get an error:

Column names in each table must be unique. Column name 'commonColumn' in table '#MyTempTable' is specified more than once.

if you use `select * from ...` then you have to make sure column name should be unique — wiretext, Sep 09 '15 at 13:37
Assuming you join two tables, both of which have a "Name" column, in what form would you want the resulting column set? (Rename one of the "Names", keep only one, or what?) — Philip Kelley, Sep 09 '15 at 13:38
`Select A.*, B.ColsnotInA, B.Cols2NotInA...` or vice versa pick the table with fewer columns to type out. — xQbert, Sep 09 '15 at 13:40
Re: Name column, if only one, then which one? Say, "Person.Name" vs. "Store.Name"? (It's slightly simpler if they are the columns you joined on, but only slightly.) — Philip Kelley, Sep 09 '15 at 13:56
@PhilipKelley They are exactly identical (title and content) in the join result, so it doesn't matter — Antonio, Sep 09 '15 at 14:01
I +1ed @X.L.Ant's answer. @ BlackJacketMack's idea could work, but unless you've got dozens of similar queries like this, it is almost certainly more effort than it's worth. — Philip Kelley, Sep 09 '15 at 14:04

score 4 · Answer 1 · answered Sep 09 '15 at 13:37

4

No there is not. The only way to avoid this error in a SELECT INTO query is to write out your column list after SELECT, instead of SELECT *.

answered Sep 09 '15 at 13:37

Tab Alleman

31,483
7
36
52

I believe there must be a way, even if it was a complicated one. – Antonio Sep 09 '15 at 13:59
Well you could replace your tables with views or derived tables that replace common column names with different names, but at some point or other in that chain of code, you are specifying column names. There's no way around that. – Tab Alleman Sep 09 '15 at 14:17
Like I said, at some point in the code stack, you're specifying column names, even if you create the list programmatically. But you can't do it with a `SELECT *` query. – Tab Alleman Sep 09 '15 at 14:37
If the listing is done automatically, my problem is solved. – Antonio Sep 09 '15 at 14:41
If that is true, then you asked the wrong question. – Tab Alleman Aug 18 '17 at 15:46

xlecoustillier · Answer 2 · 2015-09-09T14:25:44.480

2

The only alternative is to avoid SELECTing *:

TableA

foo | bar
---------

TableB

foo | baz
---------

When selecting * from both tables, you'll end up with 2 columns named foo, which is not allowed in a single table.

Name the selected columns using unique names, and this will work.

SELECT INTO #TableTemp t1.foo foo1, t1.bar, t2.foo foo2, t2.baz
FROM tableA t1
INNER JOIN tableB t2 ON t1.foo = t2.foo

But while you're at it, no need to insert the common column twice (as t1.foo = t2.foo). Select just one of them:

SELECT INTO #TableTemp t1.foo, t1.bar, t2.baz
FROM tableA t1
INNER JOIN tableB t2 ON t1.foo = t2.foo

EDIT: As stated by Philip Kelley, this problem only occurs when you try to save the resultset into a table. As long as you only select data, everything works fine with duplicate column names.

edited Sep 09 '15 at 14:25

answered Sep 09 '15 at 13:38

xlecoustillier

16,183
14
60
85

A fussy point: the result set generated by a query *can* contain duplicated column names. The duplicated name problem only kicks in when you try to save this as a table via the `into` clause. – Philip Kelley Sep 09 '15 at 14:01
@PhilipKelley Of course. I selected so many duplicate columns in my life that I forgot to mention this point :) – xlecoustillier Sep 09 '15 at 14:27

score 0 · Answer 3 · answered Sep 09 '15 at 13:49

I wouldn't recommend this, but in an effort to answer your question, you could create a sql clause builder function that automatically creates column aliases for your individual tables prefixed with the tablename (or customize it if you'd like):

declare @tableName nvarchar(100);


set @tableName = 'TestTable';

select @tableName + '.' + c.name + ' AS ' + @tableName + '_' + c.name
from sys.tables t
    inner join sys.columns c on c.object_id = t.object_id
where
    t.name = @tableName

Output for my little 2-column table

TestTable.TestColumn1 AS TestTable_TestColumn1
TestTable.TestColumn2 AS TestTable_TestColumn2

Hypothetical usage

declare @sql nvarchar(1000) = '';
set @sql = myClauseBuilder('TestTable');

set @sql = @sql + ',' + myClauseBuilder('TestTable2');

set @sql = @sql + ' FROM TestTable INNER JOIN TestTable2....(etc.)

exec @sql

score 0 · Accepted Answer · edited May 23 '17 at 10:30

It is not easy. But it is possible. Developing from this, you would first create two separated lists with all the columns of the 2 tables, excluding the common column on which you want to join:

DECLARE @columnsA varchar(8000)

SELECT @columnsA = ISNULL(@columnsA + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableA' AND COLUMN_NAME <> 'commonColumn'
ORDER BY ORDINAL_POSITION

DECLARE @columnsB varchar(8000)

SELECT @columnsB = ISNULL(@columnsB + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableB' AND COLUMN_NAME <> 'commonColumn'
ORDER BY ORDINAL_POSITION

Then you would use them for your query, selecting the commonColumn only for one of the table:

EXEC ('SELECT tableA.commonColumn, ' + @columnA + ', ' + @columnsB + ' FROM tableA INNER JOIN tableB ON tableA.commonColumn = tableB.commonColumn;')

So there's at least one way to do it. :) It is apparently also moderately efficient. I am not an SQL expert, but I suppose there is a way to create a function out of this, maybe one function to "select all columns but [...]" and one function that would do the join as USING would do.

It becomes a little bit simpler if in listing we add also the containing table. In this way we need to extract only the column names from one table.

DECLARE @columnsB varchar(8000)

SELECT @columnsB = ISNULL(@columnsB + ', ','') + QUOTENAME(table_name) + '.' + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableB' AND COLUMN_NAME <> 'commonColumn'
ORDER BY ORDINAL_POSITION

And the query is modified into:

EXEC ('SELECT tableA.* ', ' + @columnsB + ' FROM tableA INNER JOIN tableB ON tableA.commonColumn = tableB.commonColumn;')

That's the only way. On top of that. I'd probably go for `WHERE TABLE_NAME IN ('tableA', 'tableB' AND COLUMN_NAME <> 'commonColumn'` with picking `DISTINCT column_name`. There might be names with the same name there, can't there? And only then form columns list. — Evaldas Buinauskas, Sep 09 '15 at 14:57
@EvaldasBuinauskas Apart from the "joining" columns, I am keeping off from having common column names into different tables. I lost a lot of time today checking if I could add a `originatingTable.` prefix to the columns of my joined table. — Antonio, Sep 09 '15 at 15:22

Alternatives to USING in a JOIN for SQL Server for avoiding duplicated columns in joined table

4 Answers4

Linked