Imagine a query
SELECT Col_A FROM TABLE_A WHERE Col_A IN (SELECT Col_A FROM TABLE_B)
Except, TABLE_B does not have a Col_A column; Only TABLE_A has it. I tried this in Oracle 12 and I'm not sure how far back in versions it goes but it looks like is returns a valid result of showing all of Col_A data from TABLE_A. As a developer I'm expecting something like this to throw an error since the inner query does not even access the TABLE_A again so to speak. Can someone explain why or where we would use a query situation like the one above because I almost feel like it should be a bug in the system.
It is not a bug. As you did not qualify the column, Oracle interprets it as the current column value from table A -- which is within scope (cf. a correlative sub query). So the subquery just produces as many duplicates of that column value as there are rows in table B.
The in condition will either become true or false based on that. It will always be false when table B is empty. It will also be false in cases where Col_A is null. But in all other cases it will be true. So you'll get as many results as there are rows in table A that have a non-null value in Col_A, except when table B is empty, in which case you get no results.