Use join column only once in SELECT *

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.

"This is an explicit question to do it without." - you can't — Martin Smith, Apr 05 '18 at 07:44
@MartinSmith, Ok thanks. I'll leave it unanswered, until things change. — Davor Josipovic, Apr 05 '18 at 07:45
Is creating the SQL dynamically (either directly in T-SQL or in your client language of choice) an option? — Heinzi, Apr 05 '18 at 07:47
@DavorJosipovic: OK. Then I *think* you're out of options, but I'd be happy to be proven wrong. — Heinzi, Apr 05 '18 at 07:49
I suppose you need it to work within a CTE for something, because with just the SELECT from the CTE alone it shouldn't be an issue to use *. — LukStorms, Apr 05 '18 at 07:49
But a select within a CTE or subquery must return unique columnnames. It's only logical, because how would it be able to know from which table a CTE.columnname to take when there's 2. — LukStorms, Apr 05 '18 at 08:03

score 2 · Answer 1 · answered Apr 05 '18 at 07:43

2

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

answered Apr 05 '18 at 07:43

Giorgos Betsos

71,379
9
63
98

And then use a script to list the columns of the second table so that you don't have to type them in – Peter Smith Apr 05 '18 at 07:54
If `t1` has a column named `myColumn` then it would still fail (with different column but same problem). – EzLo Apr 05 '18 at 08:38
@EzequielLópezPetrucci This shouldn't be a problem, since the columns of the other table are *explicitly* specified. I've explained where the issue with the name conflict is, so it is clear enough that all columns with the same names should be omitted. – Giorgos Betsos Apr 05 '18 at 08:41
@GiorgosBetsos But one could mention that if you need to select columns with the same name from the second table that you could alias them. Especially if Peter Smith's suggestion is used. Although that's pretty much obvious to most, I guess. – LukStorms Apr 05 '18 at 08:56

EzLo · Answer 2 · 2018-04-05T08:36:21.413

1

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.

edited Apr 05 '18 at 08:36

answered Apr 05 '18 at 07:44

EzLo

13,780
10
33
38

Use join column only once in SELECT *

2 Answers2