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

[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语句多表连接的情况下。