In a join, how to prefix all column names with the table it came from

You could

select ah.*, l.*, u.*, pi.* from ...

then the columns will be returned ordered by table at least.

For better distinction between every two sets of columns, you could also add "delimiter" columns like this:

select ah.*, ':', l.*, ':', u.*, ':', pi.* from ...

(Edited to remove explicit aliases as unnecessary, see comments.)


You could name the fields in your query and give them aliases:

SELECT     ah.whateverfield1 AS 'ah_field1',
           ah.whateverfield2 AS 'ah_field2',
           l.whateverfield3 AS 'l.field3',
           [....]
FROM       class_alerts_holding ah 
INNER JOIN class_listings l ON l.id = ah.lid 
INNER JOIN class_users u ON u.id = ah.uid
LEFT JOIN  class_prodimages pi ON pi.pid = ah.lid

Its a bit of work to manually set up if you have that many fields, but you can simplify this with this query...

SHOW FULL FIELDS FROM your_table_name;

...and a good text editor and copy & paste.


I am convinced that such feature to prefix and/or postfix fields names with a table name in a join SHOULD BE INCLUDED INTO ANSI SQL STANDARD. Currently, in year 2019, there is still no elegant cross-platform way to do it, and all what's left is ugly-looking and error-prone manual hacking with aliases, or platform-specific solutions involving dynamic sql. Everyone would really benefit from having ability to specify custom prefix or/and postfix to fields denoted by 'dot-star' (.*). Sample select after adding such feature would be:

select a.* use prefix,b.* use postfix '_b' from table_a a inner join table_b b on a.id=b.id

As you can see, by default prefix or postfix would equal table name (or alias name), and can be overridden with any desired string literal.

Also what's aching to be added to standard, is ability to exclude certain fields from 'starred' (*) output, which is a shortcut to select all fields. I would add except keyword to list fieds which I do not want to be included for reasons of reducing network data transfer or/and brevity, e.g. :

select * except large_binary_data_field,another_notneeded_field,etc from my_table

Such feature would allow to avoid necessity of explicitly specifying full (and potentially large) list of fields which are needed as opposed to only specifying star and a few fields which are not needed.

So please, whoever reading this post and being able to reach out to ANSI SQL standard influencers, you know what to do )

P.S. yet another ugly, but at least automated & generic dynamic sql wrapper

For the Python advocates who work with psycopg, here is the convenient sub I use (strictly internally, as it's prone to possible sql injections)

def get_table_fields(table,alias,prefix='',suffix='',excluding=''):
    if type(excluding)==str: excluding=excluding.split(',')
    cur.execute('select * from '+table+' where 0=1');cur.fetchall()
    if not (cur.description is None):        
        return ','.join([alias+'.'+col.name+' '+prefix+col.name+suffix for col in cur.description if not (col.name in excluding)])

And the calling code, where I am joining 3 tables and want to avoid fetching large data field from the datasets table:

sql="""select %s,%s,%s from tasks t,features_sets f,datasets d 
        where 
                t.is_active=true and f.is_active=true 
                and f.task=t.id and t.train_dataset=d.id 
    """ % (
        get_table_fields('tasks','t',prefix='ts_'),
        get_table_fields('features_sets','f',prefix='fs_'),
        get_table_fields('datasets','d',prefix='ds_',excluding='data')
    )

which gets unrolled for me into mighty

select t.id ts_id,t.project ts_project,t.name ts_name,***,
    fs_id,f.task fs_task,f.name fs_name,f.description fs_description,***,
    d.id ds_id,d.project ds_project,d.name ds_name,***
from tasks t,features_sets f,datasets d 
    where 
        t.is_active=true and f.is_active=true 
        and f.task=t.id and t.train_dataset=d.id 

where *** means tons of other useful fields, some of them are common for more than one table (hence the need for prefixing). cur is obviously the psycopg cursor, and 0=1 condition is intended to retrieve only fields names without real data.