In a sql query how not to show duplicate columns

Question

   A(p,q,r,...)
   B(p,l,m,n,...)

   select <columns>
   from A join B on A.p=B.p

how do I write expression where I would like to show

A.*

But everything in B except p

I don't want to list them by hand:

`select A.*, B.l, B.m, B.n, ..`.

Because the number of columns in B can be huge. Is there any SQL syntax to achieve this?

Since `B.p=A.p` does it matter? Note that if you're fetching into an associative array you'll only end up with one `p` value anyway. — Nick, Apr 03 '20 at 05:12
Does this answer your question? [Select all columns except one in MySQL?](https://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql) — user70, Apr 03 '20 at 05:15

score 1 · Answer 1 · answered Apr 03 '20 at 06:59

For tables

A(p,q,r,...)
B(p,l,m,n,...)

the query

select <columns>
from A join B on A.p=B.p

will produce 2 copies of p column, whereas

select <columns>
from A join B USING(p)

and (if possible)

select <columns>
from A NATURAL join B

will produce one single p column.

1 Answers1