sql server alias tablas

SQL: alcance de alias de tabla

Question

Acabo de aprender (ayer) a usar "existe" en lugar de "en".

 BAD
 select * from table where nameid in ( 
          select nameid from othertable where otherdesc =  ''SomeDesc'' )      
 GOOD
 select * from table t where exists ( 
          select nameid from othertable o where t.nameid = o.nameid and otherdesc =  ''SomeDesc'' )      

Y tengo algunas preguntas sobre esto:

1) La explicación que entendí fue: "La razón por la que esto es mejor es porque solo se devolverán los valores coincidentes en lugar de crear una lista masiva de posibles resultados" . ¿Eso significa que, aunque la primera subconsulta podría devolver 900 resultados, la segunda solo arrojará 1 (sí o no)?

2) En el pasado, tuve el RDBMS quejándose: "solo las primeras 1000 filas podrían ser recuperadas", ¿este segundo enfoque resolvería ese problema?

3) ¿Cuál es el alcance del alias en la segunda subconsulta? ... ¿el alias solo vive en el paréntesis?

por ejemplo

 select * from table t where exists ( 
          select nameid from othertable o where t.nameid = o.nameid and otherdesc =  ''SomeDesc'' )      
 AND 
          select nameid from othertable o where t.nameid = o.nameid and otherdesc =  ''SomeOtherDesc'' )      

Es decir, si utilizo el mismo alias (o para la tabla othertable) ¿En el segundo "existir" presentará algún problema con el primero existe? o son totalmente independientes?

¿Es esto algo Oracle solo relacionado o es válido para la mayoría de RDBMS?

Muchas gracias

Answer 1

Es específico para cada DBMS y depende del optimizador de consultas. Algunos optimizadores detectan la cláusula IN y la traducen.

En todos los DBMS que he probado, alias solo es válido dentro de ()

Por cierto, puede reescribir la consulta como:

select t.* 
from table t 
join othertable o on t.nameid = o.nameid 
    and o.otherdesc in (''SomeDesc'',''SomeOtherDesc'');

Y, para responder sus preguntas:

1. Sí
2. Sí
3. Sí

Answer 2

Estás pisando un territorio complicado, conocido como ''subconsultas correlacionadas''. Como no tenemos información detallada sobre sus tablas y las estructuras clave, algunas de las respuestas solo pueden ser ''quizás''.

En su consulta IN inicial, la notación sería válida independientemente de si OtherTable contiene una columna NameID (y, de hecho, si OtherDesc existe como columna en Table u OtherTable, que no está claro en ninguno de sus ejemplos, pero presumiblemente es una columna de OtherTable). Este comportamiento es lo que hace que una subconsulta correlacionada se convierta en una subconsulta correlacionada. También es una fuente rutinaria de angustia para las personas cuando se topan con ella por primera vez, invariablemente por accidente. Dado que el estándar SQL exige que el comportamiento de interpretar un nombre en la subconsulta se refiera a una columna en la consulta externa si no hay una columna con el nombre relevante en las tablas mencionadas en la subconsulta, pero hay una columna con el nombre relevante en las tablas mencionadas en la consulta externa (principal), ningún producto que quiera reclamar conformidad con (este bit de) el estándar SQL hará algo diferente.

La respuesta a su Q1 es "depende", pero dadas las suposiciones plausibles (NameID existe como una columna en ambas tablas; OtherDesc solo existe en OtherTable), los resultados deben ser los mismos en términos del conjunto de datos devueltos, pero pueden no serlo. equivalente en términos de rendimiento.

La respuesta a su Q2 es que en el pasado, estaba utilizando un DBMS inferior, si no es defectuoso. Si admitía EXISTS, entonces el DBMS aún podría quejarse sobre la cardinalidad del resultado.

La respuesta a su Q3 tal como se aplicó a la primera consulta EXISTS es "t está disponible como un alias a lo largo de la instrucción, pero o solo está disponible como un alias dentro de los paréntesis". Tal como se aplica a su segundo cuadro de ejemplo, con Y conectando dos sub-selecciones (al segundo de las cuales le falta el paréntesis abierto cuando lo estoy viendo), entonces "t está disponible como un alias en todo el enunciado y se refiere al mismo tabla, pero hay dos alias diferentes etiquetados ''o'', uno para cada subconsulta ". Tenga en cuenta que la consulta puede no devolver datos si OtherDesc es único para un valor de NameID dado en OtherTable; de lo contrario, requiere dos filas en OtherTable con el mismo NameID y los dos valores de OtherDesc para cada fila en Table con ese valor NameID.

Answer 3

Personalmente usaría una unión, en lugar de una subconsulta para esto.

SELECT t.*
FROM yourTable t
    INNER JOIN otherTable ot
        ON (t.nameid = ot.nameid AND ot.otherdesc = ''SomeDesc'')

Answer 4

Es difícil generalizar que EXISTE siempre es mejor que IN. Lógicamente, si ese es el caso, entonces la comunidad SQL habría reemplazado IN con EXISTS ... Además, tenga en cuenta que IN y EXISTS no son iguales, los resultados pueden ser diferentes cuando utiliza los dos ...

Con IN, generalmente es una exploración de tabla completa de la tabla interna una vez sin eliminar NULL (por lo que si tiene NULL en la tabla interna, IN no eliminará NULLS de forma predeterminada) ... Mientras EXISTS elimine NULL y en caso de subconsulta correlacionada, ejecuta una consulta interna para cada fila desde la consulta externa.

Suponiendo que no hay NULLS y es una consulta simple (sin correlación), EXIST podría funcionar mejor si la fila que está buscando no es la última fila. Si resulta ser la última fila, EXISTS puede necesitar escanear hasta el final como IN ... un rendimiento similar ...

Pero IN y EXISTS no son intercambiables ...

Answer 5

1. Específico de Oracle: cuando escribe una consulta utilizando la cláusula IN, le dice al optimizador basado en reglas que desea que la consulta interna dirija la consulta externa. Cuando escribe EXISTS en una cláusula where, le está diciendo al optimizador que desea que la consulta externa se ejecute primero, usando cada valor para obtener un valor de la consulta interna. Consulte "Diferencia entre IN y EXISTS en subconsultas" .
2. Probablemente.
3. Alias ​​declaró dentro de las vidas de subconsulta dentro de subconsulta. Por cierto, no creo que su ejemplo con 2 subconsultas AND sea SQL válido. ¿Querías decir UNION en lugar de AND?