有一张表test,只有一个属性a,如下:
a
1
2
3
则:
select * from test natural join test 结果为:
a
1
1
1
2
2
2
3
3
3
select * from test t1 natural test test t2 结果为:
a
1
2
3
请问表加不加别名的区别是?
谢谢
a
1
2
3
则:
select * from test natural join test 结果为:
a
1
1
1
2
2
2
3
3
3
select * from test t1 natural test test t2 结果为:
a
1
2
3
请问表加不加别名的区别是?
谢谢
如果两个表中的同名列的所有数据类型不同,则出错
不允许在参照列上使用表名或者别名作为前缀
我自己用表试了一下:
select * from test_yixl1 natural join test_yixl
minus
select * from test_yixl1 t1 natural join test_yixl t2
--执行结果,没有记录表加不加别名效果是一样的。
您好!那两条查询我运行了,确实没错。minus并不能确定两查询结果相同吧
Table dropped
SQL>
SQL> create table t_a
2 (a number);
Table created
SQL> insert into t_a select level from dual connect by level<=3;
3 rows inserted
SQL> select * from t_a;
A
----------
1
2
3
SQL> select * from t_a natural join t_a;
select * from t_a natural join t_a
ORA-00918: column ambiguously defined
SQL> select * from t_a t natural join t_a s;
A
----------
1
2
3
SQL>
create table naturaltable(a number);insert into naturaltable values (1);
insert into naturaltable values (2);
insert into naturaltable values (3);select * from naturaltable natural join naturaltable;--出错select * from naturaltable t natural join naturaltable s;--正确
create table naturaltable(a number);insert into naturaltable values (1);
insert into naturaltable values (2);
insert into naturaltable values (3);select * from naturaltable natural join naturaltable;--出错,没有明确定义列select * from naturaltable t natural join naturaltable s;--正确
我用的是oracle10g,显示结果是我那样的,你的版本是?
您好!我用的11.2,查询:
SQL> select * from t_a natural join t_a; A
----------
1
1
1
2
2
2
3
3
39 rows selected.
select * from t_a a,t_a b where a.a=a.a
执行报错,提示ORA-00918:未明确定义列
select * from test t1 natural test test t2
执行结果同楼主执行的结果。
我的oracle版本是 9.2.0.1.0