SQL – Two Joins on Same Table Using Alias

alias

I've got 2 SQL tables: NAMES and RELATIONSHIPS Currently NAMES just has 2 columns: name and name_id. The other is a list of relationships between people in the NAMES table. It has 3 columns: primaryperson_id, relatedperson_id, and relationship_id. The primaryperson_id and related_person_id are name_ids from the NAMES table. Each person in NAMES can have multiple entries in either the primary or related columns of RELATIONSHIPS (is that a many-to-many relationship?).

This query works:

SELECT  people.name AS 'primary', relationships.related_person_id AS relatedto
FROM relationships
JOIN people
ON people.name_id=relationships.primary_person_id 
ORDER BY people.name_id;

But I'd like to show a name (i.e. text) in the relatedto column rather than the id number. How can I do that?

Best Answer

You can assign a table alias when you join tables, just like you do with the column names. Not only does it save you a lot of typing, but it makes the code a little more readable, and it solves the problem of joining the same table twice.

SELECT p1.name AS "primary",
       relationships.related_person_id AS relatedto,
       p2.name AS related_name
FROM people AS p1
INNER JOIN relationships AS r
    ON p1.name_id=r.primary_person_id 
INNER JOIN people AS p2
    ON p2.name_id=r.related_person_id 
ORDER BY p1.name_id;

In the example above, I've assigned p1 and p2 to the people table and r to the relationships table.

Note: some database platforms don't like the "AS" keyword. You can skip "AS" alltogether if you want, like so:

...
FROM people p1
INNER JOIN relationships r
...

Related Solutions

Joining two tables with an alias

It's because you're using the SELECT * and joining a table to itself. The DB engine will try to return all columns, so it needs to know which columns you are referring to if you have multiple with the same name. If you want both you need to specify them with aliases to indicate which order to output them with.

The reason the aggregation works is because you're not trying to return any duplicated columns. Replace the SELECT * with the columns that you want, using the Aliases to specify which tables you want them from.

As a general best practice, do not use SELECT *; always specify your column names.

PostgreSQL Column Alias in FROM Clause

The column aliases there override the column names/aliases of the internal select subquery (derived table). The same way, they can override the column names of the table (whether it's base table, a view, a derived table or a cte does not matter at all).

So, the simple example will give an error:

select 
    a, count_a              -- invalid here (have been overridden)
from 
    ( select t.a, count(*) as count_a
      from t
      group by t.a
      order by count_a desc           -- count_a is valid here
      limit 8
    ) 
      as d (b, count_b) ;

but this will work:

select 
    b, count_b              -- valid column aliases
from     
    -- identical as above

the names a and count_a are valid inside the subquery (derived table) but not outside because they have been overridden by b and count_b.

Do you have to know the exact sequence of columns defined in the table, or can you set an alias just for one or two of these in the FROM clause?

Yes, you do have to know the sequence of columns.

But you don't have to change all columns. Say the table has 5 columns. If you use:

select t.*
from table_name as t (a,b,c) ;

only the first 3 columns will appear with the new names (a,b,c). The 4th and 5th will show with their real names. You'll get an error if you provide more aliases than needed (eg. 6 aliases for a 5-column table).

What if you only want to set a column_alias for one column with a very long name (and leave the other columns not aliased); is this possible? (If so, is this Postgres specific?)

Only if it's the first. Or by providing all the previous column names up to the column you want to alias with a different name.

I suppose you can't just give an alias for the third column only, or something like that?

I don't know of any syntax to allow you to alias only the 3rd column, without providing the names of the 1st and 2nd column.

Overall, the usefulness of the feature is at least debatable when used for base tables. And the above query that overrides the names of just 3 of the possibly many columns reeks obfuscation and could be very well considered bad practice.

But the feature is provided because it's standard SQL and for completeness. It wouldn't make sense to have this only for subqueries and CTEs and not for other kinds of tables.

One case where it can be useful is (not with base table but with) the VALUES construct, where the columns get default names of column1, column2, etc. and this aliasing can be used to select more meaningful names:

select 
    a, b
from 
    ( values
         (1, 2),
         (2, 3), 
         (3, 5)
    ) 
      as d (a, b) ;

Best Answer

Related Solutions

Joining two tables with an alias

PostgreSQL Column Alias in FROM Clause

Related Question