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

Question

I'm analysing a rather horrible legacy database/codebase, trying to reduce server load by combining queries into joins (including an email alert cron job that typically invokes well over a million separate queries).

SELECT * 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

This spits out 120 columns...

aid | id | lid | uid | oid | catName | searchtext | alertfreq | listType | id | owner | title | section | shortDescription | description | featured | price | display | hitcount | dateadded | expiration | url | notified | searchcount | repliedcount | pBold | pHighlighted | notes | ...

To assist my analysis of how to construct the new queries it would be awesome if I could prefix the columns in the result with the table they came from in the JOIN e.g.

class_alerts_holding.aid | class_alerts_holding.id | class_listings.lid | ...

Is there a way to achieve this?

Answer 1

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.)}

Answer 2

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.

Answer 3

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.

Answer 4

The way to dynamically name columns is to generate a prepared statement that references the information_schema. This would give you the results you were looking for.

SET @sql = NULL;
SELECT CONCAT(
   'SELECT ',GROUP_CONCAT(c.TABLE_NAME,'.',c.COLUMN_NAME,' AS `',c.TABLE_NAME,'.',c.COLUMN_NAME,'`'),'
    FROM class_alerts_holding 
    INNER JOIN class_listings ON class_listings.id = class_alerts_holding.lid 
    INNER JOIN class_users ON class_users.id = class_alerts_holding.uid
    LEFT JOIN class_prodimages ON class_prodimages.pid = class_alerts_holding.lid'
)
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME IN ('class_alerts_holding','class_listings',
                       'class_users','class_prodimages');    
PREPARE sql_statement FROM @sql;
EXECUTE sql_statement;

The GROUP_CONCAT() function has a default limit of 1024 characters, so depending on the number of columns in your tables, you may need to raise this limit in order to generate the prepared statement.

SET SESSION group_concat_max_len = 1000000;

This command will raise the group concat limit if needed. -

Answer 5

I ended up just building the field set for the query, as as of 2020 this still isn't supported.

But, being a lazy programmer, I obviously didn't want to manually type this all out for all of the tables in my query. So I wrote a query to build the select statement:

SELECT
    CONCAT(table_name, ".", column_name, " AS ", CHAR(34), table_name, ".", column_name, CHAR(34)) field_names
FROM
    information_schema.columns
WHERE
    table_schema = "my_database"
    AND table_name IN(
        "table_1",
        "table_2"
    );

which will output something like:

| field_names                        |
|------------------------------------|
| table_1.id AS "table_1.id"         |
| table_1.name AS "table_1.name"     |
| table_2.id AS "table_2.id"         |
| table_2.number AS "table_2.number" |

That can then easily be copied into your SELECT statement.

Answer 6

I've found something usefull in this question MySQL concat() to create column names to be used in a query? . I think that this can be one of the solutions.

Answer 7

Based on the solution proposed by koljaTM and AndriyM, maybe an even better solution is to write your query like this:

select
  '--TABLE_AAA:--', TABLE_AAA.*,
  '--TABLE_BBB:--', TABLE_BBB.*,
  '--TABLE_CCC:--', TABLE_CCC.*,
  '--TABLE_DDD:--', TABLE_DDD.*
from ...

Unfortunately this is still not good enough in cases when one (or more) of the tables contains more column names than can fit on the screen width. (So you might see on your screen 20 columns but still not be visible on the screen the name of the table from which they come.)

It would still have been better if SQL provided a way to automatically prefix the column names with the table names...

Answer 8

@alden-w, You may add TABLE_SCHEMA condition to where to do not mix up same table names from different schemas

WHERE c.TABLE_SCHEMA='YOUR_SCHEMA_NAME' AND c.TABLE_NAME IN (....)

Answer 9

You may try dynamic sql to create a query on the go as per the table definition.

declare @col varchar(max)
set @col = Select stuff( 
          (select ', ' + column_name + '.' + table_name 
           from information_schema.columns 
           where table_name in ( 'table1', 'table2' ...) for xml 
           path('')),1,1,'')

declare @query nvarchar(max) = '
select ' + @col + ' 
from table1 
inner join table2 on table1.id = table2.id '

exec sp_executesql @query

Answer 10

CREATE OR REPLACE FUNCTION getAlias (mytable text, my_alias text, my_prefix text)
RETURNS SETOF TEXT AS $$
   SELECT my_alias || column_name || ' as ' || my_prefix
   FROM information_schema.COLUMNS
   WHERE TABLE_NAME = mytable;
$$ LANGUAGE SQL

-- You can write function in db. This function need to be a standard in SQL.

Answer 11

This works for me following DarkRob suggestion in MS SQL. Doing it this way avoids the "Ambiguous column name ..." error message if both tables share some of the column names.

DECLARE @cols1 NVARCHAR(max)
SET @cols1 =  (SELECT STUFF(
          (SELECT ', ati.' + [COLUMN_NAME] + ' AS ' + 'ati_' + [COLUMN_NAME] FROM information_schema.columns 
           WHERE [TABLE_NAME] in ('audit_trans_inv') FOR XML PATH('')),1,1,''))

DECLARE @cols2 NVARCHAR(max)
SET @cols2 =  (SELECT STUFF(
          (SELECT ', ti.' + [COLUMN_NAME] + ' AS ' + 'ti_' + [COLUMN_NAME] from information_schema.columns 
           WHERE [TABLE_NAME] in ('transaccion_inv') FOR XML PATH('')),1,1,''))

DECLARE @sql NVARCHAR(max) = '
SELECT TOP 5 ' + @cols1 + ',' + @cols2 + '
FROM [millennium].[AUDIT_TRANS_INV] ati
INNER JOIN [millennium].[TRANSACCION_INV] ti ON [ti].[AUDIT_TRANS_INV] = [ati].[AUDIT_TRANS_INV]

EXEC sp_executesql @sql