Delete statement - Sub Query should throw error

Question

I have created two tables, T1 and T2 with one column each, abc and xyz respectively. I have inserted 2 rows (numeric values 1 and 2) in each table.

When I run the command "select abc from t2", it throws an error saying that column abc does not exist in the table T2. However, when I run the command "delete from t1 where abc in (SELECT abc from t2);", 2 rows are deleted.

Shouldn't the delete fail as I have used the same statement which failed in the sub-query?

create table t1 (abc number); --Table created

create table t2 (xyz number); --Table created

insert into t1 values (1); --One row inserted

insert into t1 values (2); --One row inserted

insert into t2 values (1); --One row inserted

insert into t2 values (2); --One row inserted

SELECT abc from t2; --ORA-00904 -> Because column abc does not exist in t2

delete from t1 where abc in (SELECT abc from t2); --2 rows deleted

Answer 1

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.

Answer 2

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