Por que essa consulta funciona?

A consulta é um SQL sintaticamente correto, mesmo que table_bnão tenha uma namecoluna. O motivo é a resolução do escopo.

Quando a consulta é analisada, primeiro é verificado se table_bhá uma namecoluna. Uma vez que não, então table_aé verificado. Isso lançaria um erro apenas se nenhuma das tabelas tivesse uma namecoluna.

Por fim, a consulta é executada como:

select a.* 
from table_a  a
where a.name in (select a.name 
                 from table_b  b
                );

Quanto aos resultados que a consulta daria, para cada linha de table_a, a subconsulta (select name from table_b)- ou (select a.name from table_b b)- é uma tabela com uma única coluna com o mesmo a.namevalor e tantas linhas quanto table_b. Portanto, se table_btiver 1 ou mais linhas, a consulta será executada como:

select a.* 
from table_a  a
where a.name in (a.name, a.name, ..., a.name) ;

ou:

select a.* 
from table_a  a
where a.name = a.name ;

ou:

select a.* 
from table_a  a
where a.name is not null ;

Se table_bestiver vazio, a consulta não retornará nenhuma linha (obrigado @ughai por apontar essa possibilidade).

Isso (o fato de você não receber um erro) é provavelmente o melhor motivo pelo qual todas as referências de coluna devem ser prefixadas com o nome/alias da tabela. Se a consulta foi:

select a.* from table_a where a.name in (select b.name from table_b); 

você teria o erro imediatamente. Quando os prefixos da tabela são omitidos, não é difícil que tais erros aconteçam, principalmente em consultas mais complexas e, mais importante ainda, que passem despercebidos.

Leia também nos documentos do Oracle: Resolução de nomes em instruções SQL estáticas o exemplo semelhante B-6 em captura interna e as recomendações nos parágrafos Evitando captura interna em instruções SELECT e DML :

Qualifique cada referência de coluna na instrução com o alias de tabela apropriado.

Porque

O Oracle executa uma subconsulta correlacionada quando uma subconsulta aninhada faz referência a uma coluna de uma tabela referenciada a uma instrução pai um nível acima da subconsulta. http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries007.htm#SQLRF52357

Isso significa que, para determinar se a subconsulta está correlacionada, o Oracle deve tentar resolver os nomes na subconsulta, incluindo o contexto da instrução externa também. E para sem prefixo nameé a única resolução possível.

Não há namecampo em, table_bentão o Oracle pega o de table_a. Eu tentei, EXPLAIN PLANmas isso me deu apenas que existe um arquivo TABLE ACCESS FULL. Presumo que isso gerará algum tipo de produto cartesiano entre as duas tabelas que resultará em uma lista de todos os nomes table_aretornados pela subconsulta.