Oracle SQL非唯一表别名

Does somebody know why this works with both table alias "x"?

有人知道为什么这两个表的别名都是“x”吗?

Select x.company_name  
      ,x.employee_name 
FROM company  x
JOIN employee x ON x.company_id = 5

I know that the JOIN with id 5 makes no sense...

我知道id 5的连接没有意义……

Thanks for the lesson!

谢谢你的教训!

2 个解决方案

#1

The first two queries below are equivalent. In the ON clause of the join the table alias x only refers to the last table to use that alias so only the employee table is restricted.

下面的前两个查询是等价的。在join的ON子句中，表别名x仅引用最后一个使用该别名的表，因此只限制employee表。

In the SELECT and WHERE expressions the x alias refers to both tables - so, where the column names are unique then they can be successfully referenced but where there are identical column names then oracle raises an ORA-00918: column ambiguously defined exception (as happens in query 3 if the comment is removed).

在选择和表达式x别名指的是两个表,所以,在列名是独一无二的然后他们可以成功地引用但有相同列名然后甲骨文提出了一个ora - 00918:列定义含糊不清地异常(如发生在查询3如果评论被删除)。

I can't find any documentation on this but it looks very like a bug.

我找不到任何关于这个的文档，但它看起来很像一个bug。

SQL Fiddle

SQL小提琴

Oracle 11g R2 Schema Setup:

Oracle 11g R2模式设置:

CREATE TABLE employee (
  company_id    NUMBER(3),
  employee_name VARCHAR2(20)
);

CREATE TABLE company (
  company_id    NUMBER(3),
  company_name VARCHAR2(20)
);

INSERT INTO employee VALUES ( 4, 'Four Emp' );
INSERT INTO employee VALUES ( 5, 'Five Emp' );
INSERT INTO employee VALUES ( 6, 'Six Emp' );

INSERT INTO company VALUES ( 4, 'Four Company' );
INSERT INTO company VALUES ( 5, 'Five Company' );
INSERT INTO company VALUES ( 6, 'Six Company' );

Query 1:

查询1:

SELECT *
FROM   company  x
       JOIN
       employee x
       ON x.company_id = 5

Results:

结果:

| COMPANY_ID | COMPANY_NAME | EMPLOYEE_NAME |
|------------|--------------|---------------|
|          5 | Four Company |      Five Emp |
|          5 | Five Company |      Five Emp |
|          5 |  Six Company |      Five Emp |

Query 2:

查询2:

SELECT *
FROM   company x
       CROSS JOIN
       (SELECT * FROM employee WHERE company_id = 5) x

Results:

结果:

| COMPANY_ID | COMPANY_NAME | EMPLOYEE_NAME |
|------------|--------------|---------------|
|          5 | Four Company |      Five Emp |
|          5 | Five Company |      Five Emp |
|          5 |  Six Company |      Five Emp |

Query 3:

查询3:

SELECT --x.company_id,
       x.company_name,
       x.employee_name
FROM   company x
       CROSS JOIN
       (SELECT * FROM employee WHERE company_id = 5) x

Results:

结果:

| COMPANY_NAME | EMPLOYEE_NAME |
|--------------|---------------|
| Four Company |      Five Emp |
| Five Company |      Five Emp |
|  Six Company |      Five Emp |

#2

I'd lay money on "Oracle bug" - Of all the things that've most held me back from adopting the ANSI JOIN syntax, it's been Oracle's painfully egregious and bug-laden implementation thereof. That said, "Failure to catch a syntax error" is pretty minor, and Oracle has, as far as I can tell, largely cleaned up their act.

我将把钱花在“Oracle bug”上——在所有阻碍我采用ANSI JOIN语法的因素中，它是Oracle痛苦的、糟糕透顶的实现。也就是说，“未能捕获语法错误”是非常小的错误，而且据我所知，Oracle已经在很大程度上清理了他们的行为。