Delete statement - Sub Query should throw error
If you use the table names as alias to make sure table t2 column is getting selected, you will get the error i.e.
delete from t1 where abc in (SELECT t2.abc from t2); --ORA-00904
Your original query is not failing because it's using abc
column of table t1
since table t1
is visible in the subquery.
Your Delete statement is working because of the abc column name which u have used in Where condition. sub query is executing based on the where condition column, becz we d't use the table alias name.
if u see these queries
select * from t1 where abc in (SELECT abc from t2); -- it 'll give 2 rows
select * from t1 where abc in (SELECT 1 from t2); -- it 'll give 1 row
select * from t1 where abc in (SELECT 2 from t2); -- it 'll retrieve 2nd row
select * from t1 where abc in (SELECT 3 from t2); -- w't get d data
select * from t1 where abc in (SELECT hg from t2); -- Invalid Identifier