SQL - Relación entre una subconsulta y una tabla externa
Problema
Necesito entender mejor las reglas sobre cuándo puedo hacer referencia a una tabla externa en una subconsulta y cuándo (y por qué) es una solicitud inapropiada. Descubrí una duplicación en una consulta SQL de Oracle que estoy tratando de refactorizar, pero tengo problemas cuando intento convertir mi tabla de referencia en una subconsulta agrupada.
La siguiente declaración funciona apropiadamente:
SELECT t1.*
FROM table1 t1,
INNER JOIN table2 t2
on t1.id = t2.id
and t2.date = (SELECT max(date)
FROM table2
WHERE id = t1.id) --This subquery has access to t1
Desafortunadamente, table2 a veces tiene registros duplicados, así que necesito agregar t2 antes de unirlo a t1. Sin embargo, cuando trato de envolverlo en una subconsulta para realizar esta operación, de repente el motor SQL ya no puede reconocer la tabla externa.
SELECT t1.*
FROM table1 t1,
INNER JOIN (SELECT *
FROM table2 t2
WHERE t1.id = t2.id --This loses access to t1
and t2.date = (SELECT max(date)
FROM table2
WHERE id = t1.id)) sub on t1.id = sub.id
--Subquery loses access to t1
Sé que estas son consultas fundamentalmente diferentes. Le pido al compilador que las junte, pero no veo por qué funcionaría una, pero no la otra.
Sé que puedo duplicar las referencias de la tabla en mi subconsulta y efectivamente separar mi subconsulta de la tabla externa, pero esa parece una manera realmente fea de realizar esta tarea (con toda la duplicación de código y procesamiento).
Referencias útiles
Encontré esta fantástica descripción del orden en que se ejecutan las cláusulas en SQL Server: (INNER JOIN ON vs WHERE cláusula). Estoy usando Oracle pero creo que esto sería estándar en todos los ámbitos. Hay un orden claro para la cláusula de evaluación (con DESDE es la primera), por lo que creo que cualquier cláusula que aparezca más abajo en la lista tendrá acceso a toda la información procesada previamente. ¿Puedo asumir que mi segunda consulta de alguna manera cambia ese ordenamiento para que mi subconsulta se esté evaluando demasiado pronto?
Además, encontré una pregunta similar hecha (Referenciar las tablas de consultas externas en una subconsulta ) pero si bien la información fue buena, nunca explicaron realmente por qué no podía hacer lo que está haciendo y solo le dieron soluciones alternativas a su problema. He intentado sus soluciones alternativas pero me está causando otros problemas. Es decir, esa subconsulta con la referencia de fecha es fundamental para toda la operación, por lo que no puedo deshacerme de ella.
Preguntas
Quiero entender lo que he hecho aquí ... ¿Por qué mi subconsulta inicial puede ver la tabla externa pero no después de haber incluido toda la declaración en una subconsulta?
Dicho esto, si lo que estoy tratando de hacer no se puede hacer, ¿cuál es la mejor manera de refactorizar la primera consulta para eliminar la duplicación? ¿Debo hacer referencia a table1 dos veces (con toda la duplicación que requiere)? ¿O hay (probablemente) una mejor manera de abordar este problema?
¡Gracias por adelantado!
------EDITAR------
Como algunos han supuesto, estas consultas anteriores no son realmente la consulta que estoy refactorizando, sino un ejemplo del problema que estoy encontrando. La consulta con la que estoy trabajando es mucho más complicada, por lo que no me atrevo a publicarla aquí porque tengo miedo de que la gente se desvíe del tema.
------ACTUALIZAR------
Así que hice esto con un desarrollador y tenía una posible explicación de por qué mi subconsulta está perdiendo el acceso a t1. Como estoy envolviendo esta subconsulta entre paréntesis, él piensa que esta subconsulta se está evaluando antes de que se evalúe mi tabla t1. Esto explicaría definitivamente el error 'ORA-00904: "t1". "Id": identificador no válido' que he estado recibiendo. También sugeriría que, al igual que el orden aritmético de operaciones, agregar parens a una declaración le da prioridad dentro de ciertas evaluaciones de cláusulas. Todavía me encantaría que un experto me apoyara si está de acuerdo / en desacuerdo, es una explicación lógica de lo que estoy viendo aquí.