一键选择Oracle SQL非唯一表别名 [英] Oracle SQL non-unique table alias in one Select
问题描述
有人知道为什么这两个表别名都为"x"吗?
Does somebody know why this works with both table alias "x"?
Select x.company_name
,x.employee_name
FROM company x
JOIN employee x ON x.company_id = 5
我知道ID为5的JOIN毫无意义...
I know that the JOIN with id 5 makes no sense...
感谢您的教训!
推荐答案
下面的前两个查询是等效的.在联接的ON
子句中,表别名x
仅指使用该别名的最后一个表,因此仅限制employee
表.
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.
在SELECT
和WHERE
表达式中,x
别名引用两个表-因此,在列名唯一的情况下可以成功引用它们,但在列名相同的情况下oracle引发
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).
我找不到与此相关的任何文档,但看起来非常像个错误.
I can't find any documentation on this but it looks very like a bug.
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' );
查询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 |
查询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 |
查询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 |
这篇关于一键选择Oracle SQL非唯一表别名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!