Objective: I want to be able to remove the common/ duplicate columns, from the output of a JOIN between 2 tables.
Problem: Most of the other questions that I have checked here on this subject, talk about explicitly mentioning the column names after select
For Instance: SQL Server : removing duplicate column while joining tables
The tables that I am dealing with usually have 20+ columns each, and it is not optimal to manually write each column name separately.
So if TableA has A columns and TableB has B columns, with one common column between them, then the final number of non-suplicate columns from a JOIN would be A+B-1, where A, B > 20
Existing Query:
-- Following query includes the duplicate column in the result
SELECT *
FROM TABLEA A
INNER JOIN TABLEB B ON (A.ID = B.ID)
WHERE <SOME_CONDITION>
Other solutions that I have come across:
-- Following query omits the duplicate column in the result
SELECT A.ID, A.A1, A.A2, A.A3, A.A4, A.A5, A.A6, A.A7, A.A8, A.A9, A.A10,
B.A1, B.B2, B.B3, B.B4, B.B5, B.B6, B.B7, B.B8, B.B9, B.B10
FROM TABLEA A
INNER JOIN TABLEB B ON (A.ID = B.ID)
WHERE <SOME_CONDITION>
If the only duplicate columns are the JOIN columns, you can fix this with USING:
SELECT *
FROM TABLEA A INNER JOIN
TABLEB B
USING (id)
WHERE <SOME_CONDITION>;