SQL all columns except

Question

I have two tables:

Table1 has columns A, B, C, D, E, F, G

Table2 has columns G, H, I, J, K, L, M, N

I want to join those two tables on column G. however, to avoid the duplicate columns(ambiguous G). I have to do the query like below.

select 
 t1.*, 
 t2.H,
 t2.I,
 t2.J,
 t2.K,
 t2.L,
 t2.M,
 t2.N
from Table1 t1
inner join Table2 t2
on t1.G = t2.G

I have already use t1.* to try to avoid typing every columns names from table1 however, I still have to type in all the columns EXCEPT for the joined column G, which is a complete disaster if you have a table with many columns...

Is there a handy way some where we can do

select 
 t1.*
 t2.*(except G)
....

Thanks a lot!

I know I can print out all the columns names and then copy and paste, however, the query is still too long to debug even if I don't have to type that in manually....

Answer 1

It's usually strongly discouraged to use SELECT * FROM in anything but ad-hoc queries for testing.

The reason is that table schemas change, which can break code that assumes the presence of a certain column, or the order of columns in a table.

Even if it makes your query quite long, I'd suggest specifying each and every column you want to return in your dataset.

However, to answer your question, no there is no way to specify every column except one in a SELECT clause..

Answer 2

You can use a natural join:

A natural join is a type of equi-join where the join predicate arises implicitly by comparing all columns in both tables that have the same column-names in the joined tables. The resulting joined table contains only one column for each pair of equally named columns.

SELECT * FROM T1 NATURAL JOIN T2;

Please checkout this demo.

Note, however, that NATURAL JOINs are dangerous and therefore strongly discourage their use. The danger comes from inadvertently adding a new column, named the same as another column in the other table.