CODEWITHSUNDEEP
sqlpostgresql
Alexander Mills
Alexander Mills

Reputation: 100190

Table aliasing rules with nested join

I have this and it seems to be valid syntax:

SELECT
  A.email,
  A.handle as foo
FROM
  (
    user_table A
    INNER JOIN (
      klass_table K
      LEFT JOIN user_table B ON (B.x = A.y)
    )
  )

but if I re-use the alias (user_table A x2):

SELECT
  A.email,
  A.handle as foo
FROM
  (
    user_table A
    INNER JOIN (
      klass_table K
      LEFT JOIN user_table A ON (A.x = K.y)
    )
  )

then I will get this error:

Not unique table/alias: 'A'

Can anyone explain the logic of how aliasing works in this case? If it's the same table, why does it need a different alias? Note these are nonsense queries - I am more concerned about the semantics/syntax rules here.

Upvotes: 2

Views: 907

Answers (2)

forpas
forpas

Reputation: 164139

This:

SELECT
  A.email,
  A.handle as foo
FROM
  (
    user_table A
    INNER JOIN (
      klass_table K
      LEFT JOIN user_table B ON (B.x = A.y)
    )
  )

selects 2 columns, but from which table or subquery?
What is A?
A is an alias for user_table but it exists only inside this subquery:

  (
    user_table A
    INNER JOIN (
      klass_table K
      LEFT JOIN user_table B ON (B.x = A.y)
    )
  )

Outside of this subquery it does not exist unless you alias this whole subquery like:

  (
    user_table A
    INNER JOIN (
      klass_table K
      LEFT JOIN user_table B ON (B.x = A.y)
    )
  ) A

Of course this A is not the same as the previous A.
The 1st A was an alias for the table user_table but the 2nd Ais an alias for the subquery.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270391

That is correct. If you want to re-use an alias, use a CTE:

WITH A as (
      SELECT *
      FROM user_table 
     )
SELECT A.email, A.handle as foo
FROM A INNER JOIN
     klass_table K
     ON . . . LEFT JOIN
     A A2
     ON A2.x = K.y

However, multiple references to the same table or CTE in the same FROM clause require different aliases.

Upvotes: 1

Related Questions