[2020528]写sql语句不要忘记给字段加上表别名.txt

--//许多开发写sql语句经常对于一些字段前面不加表别名,有时候主要问题在于开发压力太大,不注意这些细节.
--//昨天看链接https://www.anbob.com/archives/5798.html,我觉得应该让开发养成习惯,给字段前加上表或者表别名.

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

create table t1 as select rownum id ,'test'||rownum name from dual connect by level<=5;
create table t2 as select rownum idx ,'test'||rownum name from dual ;

--//分析略。

2.测试:
SCOTT@test01p> select * from t1 where id in (select id from t2);
        ID NAME
---------- --------------------
         1 test1
         2 test2
         3 test3
         4 test4
         5 test5

SCOTT@test01p> @ tpt/hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
3260755764 511sg7v15qbtn            1  c25b2f34

SCOTT@test01p> @ expand_sql_text.sql  511sg7v15qbtn
SELECT "A1"."ID" "ID","A1"."NAME" "NAME" FROM "SCOTT"."T1" "A1" WHERE "A1"."ID"=ANY (SELECT "A1"."ID" "ID" FROM "SCOTT"."T2" "A2")
PL/SQL procedure successfully completed.
--//表T2并没有id字段。子查询变成了(SELECT "A1"."ID" "ID" FROM "SCOTT"."T2" "A2")。

SCOTT@test01p> select * from t1 where  name  in (select name from t2);
        ID NAME
---------- --------------------
         1 test1

SCOTT@test01p> @ tpt/hash
HASH_VALUE SQL_ID        CHILD_NUMBER HASH_HEX
---------- ------------- ------------ ---------
2780930533 a9vgd1kkw38g5            0  a5c1a1e5
        
SCOTT@test01p> @ expand_sql_text.sql  a9vgd1kkw38g5
SELECT "A1"."ID" "ID","A1"."NAME" "NAME" FROM "SCOTT"."T1" "A1" WHERE "A1"."NAME"=ANY (SELECT "A2"."NAME" "NAME" FROM "SCOTT"."T2" "A2")
PL/SQL procedure successfully completed.

--//如果改成删除就产生5条记录。
SCOTT@test01p> delete from  t1 where id in (select id from t2);
5 rows deleted.

SCOTT@test01p> rollback ;
Rollback complete.

--//所以应该让开发养成良好编写sql语句的习惯,给字段加上表名或者表别名.单表问题不大,多表情况下要特别注意.

3.继续测试：
--//如果写成如下：
SCOTT@test01p> select * from t1 where t1.id in (select t2.id from t2);
select * from t1 where t1.id in (select t2.id from t2)
                                        *
ERROR at line 1:
ORA-00904: "T2"."ID": invalid identifier

--//很容易定位错误。许多开发都没有养成好的习惯特别在复杂sql语句多表连接的情况下。