6

I query data from multiple tables using JOIN statements like this :

SELECT u.id,u.name,d.id,d.name FROM user u RIGHT JOIN dog d ON... ;

When I get the answer, in psql for example, I've got this kind of rows title :

|  id   | name  |  id  |  name  |
+-------+-------+------+--------+

Instead, I'd like to get the original and precise notation :

|  u.id   | u.name  |  d.id  |  d.name  |
+---------+---------+--------+----------+

For now I have to use AS statements, but it's quite repetitive to re-write and it add length to the query and makes it less readable.

Is there a way to get this result with a SQL keyword or with a DBMS parameter?

I searched but wasn't able to find anything on this specific problem.

0
10

There is no built-in method for modifying the column names displayed in the results of a query. This is by-design.

Use the AS <alias> construct to control the column-names. Format your query for readability, as in:

SELECT u.id         AS "u.id"
     , u.name       AS "u.name"
     , d.id         AS "d.id"
     , d.name       AS "d.name"
FROM user u 
    RIGHT JOIN dog d ON...;

Alternatively, you could rename all the columns, prefixing them with a different prefix for each table, or making them unique more descriptively. For example: dog_id, dog_name, user_id, user_name. This would work for almost all cases (except self-joins).

Be aware that modifying the column names after you've implemented your database into production will result in a vast amount of work ensuring you've changed all affected code both inside and outside the database. On the other hand, renaming generic names like id or name would certainly be beneficial in the long run. I would expect the benefits to outweigh the trouble of going through code to fix the names after the renaming.

As an aside, the practice of using id as the name of the surrogate key for every table results in a spaghetti mess of unreadable and unintelligible code that is error prone. Take for example:

CREATE TABLE T1
(
    id int PRIMARY KEY
);

CREATE TABLE T2
(
    id int PRIMARY KEY
    , t1_id int
);

CREATE TABLE T3
(
    id int PRIMARY KEY
    , t1_id int
    , t2_id int
);

SELECT *
FROM T1
    INNER JOIN T2 ON t1.id = t2.id
    INNER JOIN T3 ON t1.id = t2.id AND t2.id = t3.id
WHERE t2.id = 1;

Pretty clearly, the above query should be written as:

SELECT *
FROM T1
    INNER JOIN T2 ON t1.id = t2.t1_id
    INNER JOIN T3 ON t1.id = t2.t1_id AND t2.id = t3.t2_id
WHERE t2.id = 1;

If you do this instead, it becomes far clearer from the outset what is intended:

CREATE TABLE T1
(
    t1_id int PRIMARY KEY
);

CREATE TABLE T2
(
    t2_id int PRIMARY KEY
    , t1_id int
);

CREATE TABLE T3
(
    t3_id int PRIMARY KEY
    , t1_id int
    , t2_id int
);

SELECT *
FROM T1
    INNER JOIN T2 ON t1.t1_id = t2.t1_id
    INNER JOIN T3 ON t1.t1_id = t2.t1_id AND t2.t2_id = t3.t2_id
WHERE t2.t2_id = 1;

Added bonus; the output now shows somewhat more explicit column names:

╔═══════╦═══════╦═══════╦═══════╦═══════╦═══════╗
║ t1_id ║ t2_id ║ t1_id ║ t3_id ║ t1_id ║ t2_id ║
╚═══════╩═══════╩═══════╩═══════╩═══════╩═══════╝

And as you are using PostgreSQL, you can also benefit from making the join conditions more compact by using the USING clause:

SELECT *
FROM T1
    INNER JOIN T2 USING (t1_id)
    INNER JOIN T3 USING (t1_id, t2_id)
WHERE t2.t2_id = 1;

Not only do your queries become arguably more readable this way, there is also effect on the output: the join columns are not repeated. The above statement would produce output like this:

╔═══════╦═══════╦═══════╗
║ t1_id ║ t2_id ║ t3_id ║
╚═══════╩═══════╩═══════╝
0
5

If you want 'permanent' aliases you could use views instead of tables.

create view v_users
as
    select id as u_id,
           name as u_name,
           ...
    from   users
;

create view v_dogs
as
    select id as d_id,
           name as d_name,
           ...
    from   dogs
;

Now you can build a query in this way:

select u_id, u_name, d_id, d_name
from   v_users
join   v_dogs
on     ....
2

A few things,

  1. The idea of dots . in a name is repulsive. At least use underscores. Dots are qualifiers. If your query has dots then what happens if someone makes it a view? They have "view"."foo.bar" and it just gets worse for the ORMs.
  2. Columns names don't have to be universally unique, but you're better off if they are, this both eliminates the problems presented and it gives you the ability to use the USING clause. SELECT * FROM foo JOIN bar USING (client_id). I take the USING clause as the spec's nod at the universally unique convention.
  3. If you wanted to patch the server to do this, I think it's in the RangeTable QUEL stuff (which is the oldest part of the code base that predates SQL). I think it's specified here in buildRelationAliases

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.