今天快下班的时候，有个开发的同事问我一个问题，说他在客户端执行一条sql语句，里面包含子查询，如果单独执行子查询，会报"invalid identifier"的错误，但是整个sql语句一致性就没有错误，而且数据的结果还是正确的，碰到这种问题，想必都是信心满满，越是奇怪越想探个究竟。
为了能够简单说明这个问题，我使用如下下面的语句来模拟一下。
select *from test1_customer where customer_id in (select customer_id from test2_customer where cycle_code>100);
执行这个语句没有错误。
81 rows selected.
但是执行子查询中的语句却报出了ORA-00904的错误。
select customer_id from test2_customer where cycle_code>100
*
ERROR at line 1:
ORA-00904: "CYCLE_CODE": invalid identifier
查看表test2_customer的字段，确实没有发现cycle_code这个字段，但是查询竟然还是能够执行。
原因只有一个，那个字段就是从别的表中引用的。只有test1_customer
建表的语句如下：
create table test1_customer as select object_id customer_id,object_name customer_name, object_id cycle_code from user_objects;
create table test2_customer as select object_id customer_id,object_name customer_name, object_id bill_cycle from user_objects;
为了能够简单说明这个问题，我使用如下下面的语句来模拟一下。
select *from test1_customer where customer_id in (select customer_id from test2_customer where cycle_code>100);
执行这个语句没有错误。
81 rows selected.
但是执行子查询中的语句却报出了ORA-00904的错误。
select customer_id from test2_customer where cycle_code>100
*
ERROR at line 1:
ORA-00904: "CYCLE_CODE": invalid identifier
查看表test2_customer的字段，确实没有发现cycle_code这个字段，但是查询竟然还是能够执行。
原因只有一个，那个字段就是从别的表中引用的。只有test1_customer
建表的语句如下：
create table test1_customer as select object_id customer_id,object_name customer_name, object_id cycle_code from user_objects;
create table test2_customer as select object_id customer_id,object_name customer_name, object_id bill_cycle from user_objects;
在子查询中执行select customer_id from test2_customer where cycle_code>100，字段cycle_code因为在test2_customer中不存在，于是会自动去引用test1_customer的字段值，刚好匹配到了，就输出了结果。
select *from test1_customer where customer_id in ( select customer_id from test2_customer where cycle_code>100);
这个问题如果在复杂的场景中还是很难排查的，可能就因为一点点的小问题会导致数据的问题。
所以从这个问题可以反思我们在写sql语句的时候还是需要一些基本的规范，这样就不会导致一些模糊的定义，不明不白的问题。
当引用了多个表的时候最好还是给表起个简单的别名，这样在分析sql语句的时候也比较直观和方便。
上面的查询可以简单的修改为：
select *from test1_customer t1 where t1.customer_id in (select t2.customer_id from test2_customer t2 where t2.bill_cycle>100);
如果有问题的话，也能够很快定位倒底是哪里出了问题。
SQL> select *from test1_customer t1 where t1.customer_id in (select t2.customer_id from test2_customer t2 where t2.cycle_code>100);
select *from test1_customer t1 where t1.customer_id in (select t2.customer_id from test2_customer t2 where t2.cycle_code>100)
*
ERROR at line 1:
ORA-00904: "T2"."CYCLE_CODE": invalid identifier
引申一下，在创建表，索引，序列的时候也都可以通过规范的命名规则，这样自己也很方便查看。
比如
ACCOUNT_PK就代表是一个主键索引，
ACCOUNT_1UQ就是一个唯一性索引
ACCO_COMPANY_CODE_NN 就代表字段COMPANY_CODE是一个not null 约束
select *from test1_customer where customer_id in ( select customer_id from test2_customer where cycle_code>100);
这个问题如果在复杂的场景中还是很难排查的，可能就因为一点点的小问题会导致数据的问题。
所以从这个问题可以反思我们在写sql语句的时候还是需要一些基本的规范，这样就不会导致一些模糊的定义，不明不白的问题。
当引用了多个表的时候最好还是给表起个简单的别名，这样在分析sql语句的时候也比较直观和方便。
上面的查询可以简单的修改为：
select *from test1_customer t1 where t1.customer_id in (select t2.customer_id from test2_customer t2 where t2.bill_cycle>100);
如果有问题的话，也能够很快定位倒底是哪里出了问题。
SQL> select *from test1_customer t1 where t1.customer_id in (select t2.customer_id from test2_customer t2 where t2.cycle_code>100);
select *from test1_customer t1 where t1.customer_id in (select t2.customer_id from test2_customer t2 where t2.cycle_code>100)
*
ERROR at line 1:
ORA-00904: "T2"."CYCLE_CODE": invalid identifier
引申一下，在创建表，索引，序列的时候也都可以通过规范的命名规则，这样自己也很方便查看。
比如
ACCOUNT_PK就代表是一个主键索引，
ACCOUNT_1UQ就是一个唯一性索引
ACCO_COMPANY_CODE_NN 就代表字段COMPANY_CODE是一个not null 约束
