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](https://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea) and [here](https://stackoverflow.com/questions/413819/select-except) and one [for views](https://stackoverflow.com/questions/6423684/how-to-select-but-without-column-names-must-be-unique-in-each-view) 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](https://en.wikipedia.org/wiki/Join_(SQL)#Equi-join) syntax.*

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

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