Use join column only once in SELECT * [duplicate]

Question

Suppose:

WITH t12 AS (SELECT * FROM t1 INNER JOIN t2 ON (t1.id = t2.id))
SELECT * FROM t12

This will fail with:

The column 'id' was specified multiple times for 't12'.

Now this is a large query as t1 and t2 have many (+200) columns. I am not to list them all: I explicitly want to use the *.

Normally I would solve this with the USING (id) clause, but SQL Server doesn't seem to support that. Any ideas?

Other option is to work without the WITH clause -- which works but makes the code less readable.

There are many answers on SO that make you list all fields. This is an explicit question to do it without, in the WITH clause and in pure SQL. Related questions like "exclusion fields" here and here and one for views ask for non-ANSI SQL capabilities. This question is targeted for pure SQL and would be possible (as far as I understand) if SQL Server supported ANSI SQL-92 syntax.

Answer 1

The problem is not in the ON clause. It is in the SELECT. Using * selects both t1.id and t2.id. These two have the same name, hence the error that you get.

The only work-around I can see is to apply * to the table having the largest number of columns, and explicitly name the columns of the other table that you want to select:

WITH t12 AS (
   SELECT t1.*, t2.myColumn, etc...  
   FROM t1 
   INNER JOIN t2 ON (t1.id = t2.id)
)
SELECT * FROM t12

Answer 2

You can't use a subquery (CTE or regular subquery) with 2 or more columns with the exact same name. There is no workaround unfortunately. Your only option is explicitly changing repeated columns alias as you select them, either doing it manually or by dynamic SQL.