SQL select join: is it possible to prefix all columns as 'prefix.*'?

Question

I'm wondering if this is possible in SQL. Say you have two tables A and B, and you do a select on table A and join on table B:

SELECT a.*, b.* FROM TABLE_A a JOIN TABLE_B b USING (some_id);

If table A has columns 'a_id', 'name', and 'some_id', and table B has 'b_id', 'name', and 'some_id', the query will return columns 'a_id', 'name', 'some_id', 'b_id', 'name', 'some_id'. Is there any way to prefix the column names of table B without listing every column individually? The equivalent of this:

SELECT a.*, b.b_id as 'b.b_id', b.name as 'b.name', b.some_id as 'b.some_id'
FROM TABLE_A a JOIN TABLE_B b USING (some_id);

But, as mentioned, without listing every column, so something like:

SELECT a.*, b.* as 'b.*'
FROM TABLE_A a JOIN TABLE_B b USING (some_id);

Basically something to say, "prefix every column returned by b.* with 'something'". Is this possible or am I out of luck?

Thanks in advance for your help!

EDIT: advice on not using SELECT * and so on is valid advice but not relevant in my context, so please stick to the problem at hand -- is it possible to add a prefix (a constant specified in the SQL query) to all the column names of a table in a join?

EDIT: my ultimate goal is to be able to do a SELECT * on two tables with a join, and be able to tell, from the names of the columns I get in my result set, which columns came from table A and which columns came from table B. Again, I don't want to have to list columns individually, I need to be able to do a SELECT *.

Answer 1

DIfferent database products will give you different answers; but you're setting yourself up for hurt if you carry this very far. You're far better off choosing the columns you want, and giving them your own aliases so the identity of each column is crystal-clear, and you can tell them apart in the results.

Answer 2

select * usually makes for bad code, as new columns tend to get added or order of columns change in tables quite frequently which usually breaks select * in a very subtle ways. So listing out columns is the right solution.

As to how to do your query, not sure about mysql but in sqlserver you could select column names from syscolumns and dynamically build the select clause.

Answer 3

Hi Fred!

The only database I know that does this is SQLite, depending on the settings you configure with PRAGMA full_column_names and PRAGMA short_column_names. See http://www.sqlite.org/pragma.html

Otherwise all I can recommend is to fetch columns in a result set by ordinal position rather than by column name, if it's too much trouble for you to type the names of the columns in your query.

This is a good example of why it's bad practice to use SELECT * -- because eventually you'll have a need to type out all the column names anyway.

I understand the need to support columns that may change name or position, but using wildcards makes that harder, not easier.

Answer 4

I see two possible situations here. First, you want to know if there is a SQL standard for this, that you can use in general regardless of the database. No, there is not. Second, you wnat to know with regard to a specific dbms product. Then you need to identify it. But I imagine the most likely answer is that you'll get back something like "a.id, b.id" since that's how you'd need to identify the columns in your SQL expression. And the easiest way to find out what the default is, is just to submit such a query and see what you get back. If you want to specif7 what prefix comes before the dot, you can use "SELECT * FROM a AS my_alias", for instance.

Answer 5

There are two ways I can think of to make this happen in a reusable way. One is to rename all of your columns with a prefix for the table they have come from. I have seen this many times, but I really don't like it. I find that it's redundant, causes a lot of typing, and you can always use aliases when you need to cover the case of a column name having an unclear origin.

The other way, which I would recommend you do in your situation if you are committed to seeing this through, is to create views for each table that alias the table names. Then you join against those views, rather than the tables. That way, you are free to use * if you wish, free to use the original tables with original column names if you wish, and it also makes writing any subsequent queries easier because you have already done the renaming work in the views.

Finally, I am not clear why you need to know which table each of the columns came from. Does this matter? Ultimately what matters is the data they contain. Whether UserID came from the User table or the UserQuestion table doesn't really matter. It matters, of course, when you need to update it, but at that point you should already know your schema well enough to determine that.

Answer 6

There is no SQL standard for this.

However With code generation (either on demand as the tables are created or altered or at runtime), you can do this quite easily:

CREATE TABLE [dbo].[stackoverflow_329931_a](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [col2] [nchar](10) NULL,
    [col3] [nchar](10) NULL,
    [col4] [nchar](10) NULL,
 CONSTRAINT [PK_stackoverflow_329931_a] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[stackoverflow_329931_b](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [col2] [nchar](10) NULL,
    [col3] [nchar](10) NULL,
    [col4] [nchar](10) NULL,
 CONSTRAINT [PK_stackoverflow_329931_b] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

DECLARE @table1_name AS varchar(255)
DECLARE @table1_prefix AS varchar(255)
DECLARE @table2_name AS varchar(255)
DECLARE @table2_prefix AS varchar(255)
DECLARE @join_condition AS varchar(255)
SET @table1_name = 'stackoverflow_329931_a'
SET @table1_prefix = 'a_'
SET @table2_name = 'stackoverflow_329931_b'
SET @table2_prefix = 'b_'
SET @join_condition = 'a.[id] = b.[id]'

DECLARE @CRLF AS varchar(2)
SET @CRLF = CHAR(13) + CHAR(10)

DECLARE @a_columnlist AS varchar(MAX)
DECLARE @b_columnlist AS varchar(MAX)
DECLARE @sql AS varchar(MAX)

SELECT @a_columnlist = COALESCE(@a_columnlist + @CRLF + ',', '') + 'a.[' + COLUMN_NAME + '] AS [' + @table1_prefix + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table1_name
ORDER BY ORDINAL_POSITION

SELECT @b_columnlist = COALESCE(@b_columnlist + @CRLF + ',', '') + 'b.[' + COLUMN_NAME + '] AS [' + @table2_prefix + COLUMN_NAME + ']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2_name
ORDER BY ORDINAL_POSITION

SET @sql = 'SELECT ' + @a_columnlist + '
,' + @b_columnlist + '
FROM [' + @table1_name + '] AS a
INNER JOIN [' + @table2_name + '] AS b
ON (' + @join_condition + ')'

PRINT @sql
-- EXEC (@sql)

Answer 7

I am in kind of the same boat as OP - I have dozens of fields from 3 different tables that I'm joining, some of which have the same name(ie. id, name, etc). I don't want to list each field, so my solution was to alias those fields that shared a name and use select * for those that have a unique name.

For example :

table a : id, name, field1, field2 ...

table b : id, name, field3, field4 ...

select a.id as aID, a.name as aName, a. * , b.id as bID, b.name as bName, b. * .....

When accessing the results I us the aliased names for these fields and ignore the "original" names.

Maybe not the best solution but it works for me....i'm use mysql

Answer 8

Or you could use Red Gate SQL Refactor or SQL Prompt, which expands your SELECT * into column lists with a click of the Tab button

so in your case, if you type in SELECT * FROM A JOIN B ... Go to the end of *, Tab button, voila! you'll see SELECT A.column1, A.column2, .... , B.column1, B.column2 FROM A JOIN B

It's not free though

Answer 9

i need one help please can you replay for me (how to select the _ statement in sql server)

Answer 10

This is sth I need very much too!!!

Joining 11 tables with total 180 columns... I really need the information where does the column come from. I can't imagine doing aliases for each column. What if the DB schema changes? - which is not my part of work. With the wildcards if won't affect it...

I can't understand why there is no possibility to prefix column names with table name within the SQL standards...

Regards,

Answer 11

If concerned about schema changes this might work for you: 1. Run a 'DESCRIBE table' query on all tables involved. 2. Use the returned field names to dynamically construct a string of column names prefixed with your chosen alias.

Answer 12

for everyone claiming you wouldn't need to do this - i do need to and i have the same problem. the views solution sounds like it would work but i need to check it out.

basically you have a table of users - say first_name and last_name then you want to know who is the manager of these by a manager table say: manager_id and employee_id

then you want to select both the manager and employees first and last names which are stored in the same table - they need to be so they can be treated like equal users for most instances

so now you've got first_name, last_name, manager_id, employee_id, first_name, last_name

this is exactly what i need, but when i do mysql_fetch_assoc, the second first_name overwrites the first. also i have no way to reference it.

what i need is some way to change it to: manager_first_name, manager_last_name, manager_id, employee_id, employee_first_name, employee_last_name

of course that's not too hard with those tables, but consider that i have 50 fields i need to grab all together?

Answer 13

There is a direct answer to your question for those who use the MySQL C-API.

Given the SQL:

  SELECT a.*, b.*, c.* FROM table_a a JOIN table_b b USING (x) JOIN table_c c USING (y)

The results from 'mysql_stmt_result_metadata()' gives the definition of your fields from your prepared SQL query into the structure MYSQL_FIELD[]. Each field contains the following data:

  char *name;                 /* Name of column (may be the alias) */
  char *org_name;             /* Original column name, if an alias */
  char *table;                /* Table of column if column was a field */
  char *org_table;            /* Org table name, if table was an alias */
  char *db;                   /* Database for table */
  char *catalog;              /* Catalog for table */
  char *def;                  /* Default value (set by mysql_list_fields) */
  unsigned long length;       /* Width of column (create length) */
  unsigned long max_length;   /* Max width for selected set */
  unsigned int name_length;
  unsigned int org_name_length;
  unsigned int table_length;
  unsigned int org_table_length;
  unsigned int db_length;
  unsigned int catalog_length;
  unsigned int def_length;
  unsigned int flags;         /* Div flags */
  unsigned int decimals;      /* Number of decimals in field */
  unsigned int charsetnr;     /* Character set */
  enum enum_field_types type; /* Type of field. See mysql_com.h for types */

Take notice the fields: catalog,table,org_name

You now know which fields in your SQL belongs to which schema (aka catalog) and table. This is enough to generically identify each field from a multi-table sql query, without having to alias anything.

An actual product SqlYOG is show to use this exact data in such a manor that they are able to independently update each table of a multi-table join, when the PK fields are present.