您的位置: 首页  >  技术问答  >  混合隐式和显式 JOIN

混合隐式和显式 JOIN

分类: 技术问答 • 2022-03-25 19:53:27

混合隐式和显式 JOIN

问题描述：

我遇到了 Hibernate 生成无效 SQL 的问题.具体来说，混合和匹配隐式和显式连接.这似乎是一个开放错误.

I am having a problem with Hibernate generating invalid SQL. Specifically, mixing and matching implicit and explicit joins. This seems to be an open bug.

但是，我不确定为什么这是无效的 SQL.我想出了一个生成相同语法异常的小玩具示例.

However, I'm not sure why this is invalid SQL. I have come up with a small toy example that generates the same syntax exception.

CREATE TABLE Employee (
    employeeID INT,
    name VARCHAR(255),
    managerEmployeeID INT   
)

数据

INSERT INTO Employee (employeeID, name) VALUES (1, 'Gary')
INSERT INTO Employee (employeeID, name, managerEmployeeID) VALUES (2, 'Bob', 1)

工作 SQL

这两个查询都有效.我意识到有笛卡尔积；这是故意的.

Working SQL

Both of these queries work. I realize there is a Cartesian product; that's intentional.

显式连接:

SELECT e1.name,
       e2.name,
       e1Manager.name
  FROM Employee e1
 CROSS JOIN Employee e2
 INNER JOIN Employee e1Manager
    ON e1.managerEmployeeID = e1Manager.employeeID

隐式连接:

SELECT e1.name,
       e2.name,
       e1Manager.name
  FROM Employee e1,
       Employee e2,
       Employee e1Manager
 WHERE e1.managerEmployeeID = e1Manager.employeeID

无效的 SQL

此查询不适用于 MSSQL 2000/2008 或 MySQL:

Invalid SQL

This query does NOT work on MSSQL 2000/2008 or MySQL:

SELECT e1.name, 
       e2.name, 
       e1Manager.name
  FROM Employee e1,
       Employee e2
 INNER JOIN Employee e1Manager 
    ON e1.managerEmployeeID = e1Manager.employeeID

在 MS2000 中，我收到错误:

In MS2000, I get the error:

列前缀e1"不匹配使用表名或别名在查询中.

The column prefix 'e1' does not match with a table name or alias name used in the query.

在 MySQL 中，错误是:

In MySQL, the error is:

未知列e1.managerEmployeeID"在'on从句'中.

Unknown column 'e1.managerEmployeeID' in 'on clause'.

问题

  1. 为什么这个语法无效?
  2. 额外奖励:有没有办法强制 Hibernate 只使用显式的 JOIN?
  1. Why is this syntax invalid?
  2. Bonus: Is there a way to force Hibernate to use only explicit JOINs?

答

导致错误，因为根据 SQL 标准，JOIN 关键字的优先级高于逗号.关键点是表别名在之后在FROM 子句中对相应的表进行评估之前是不可用的.

It results in an error because according to the SQL standard, the JOIN keyword has higher precedence than the comma. The sticky point is that table aliases are not usable until after the corresponding table has been evaluated in the FROM clause.

因此，当您在 JOIN...ON 表达式中引用 e1 时，e1 尚不存在.

So when you reference e1 in your JOIN...ON expression, e1 doesn't exist yet.

在我研究 Hibernate 时请稍等，看看你是否能说服它在所有情况下都使用 JOIN.

Please stand by while I research Hibernate and find out if you can persuade it to use JOIN in all cases.

嗯.Hibernate.org 上的所有内容似乎都在重定向到 jboss.org.所以现在无法在线阅读 HQL 文档.我相信他们最终会弄清楚他们的名字服务.

Hmm. Everything at Hibernate.org seems to be redirecting to jboss.org. So no way to read HQL documentation online right now. I'm sure they'll figure out their name serving eventually.

相关推荐