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.

Answer 1

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

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

Answer 3

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

Answer 4

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;')