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

问题:

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.


回答1:

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.


回答2:

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.

  • 发表于 2019-02-21 18:53
  • 阅读 ( 202 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除