Cómo utilizar consultas SQL anidadas que devuelven conjuntos de filas

Para ilustrar cómo una consulta SQL anidada devuelve un conjunto de filas, imagine que usted trabaja para un integrador de sistemas de equipos de cómputo. Su empresa, Zetec Corporation, ensambla sistemas a partir de componentes que usted compra, y luego los vende a las empresas y agencias gubernamentales. Hacer un seguimiento de su negocio con una base de datos relacional.

La base de datos se compone de muchas mesas, pero en este momento usted está preocupado con sólo tres de ellos: la tabla de productos, la tabla COMP_USED, y la mesa COMPONENTE. La tabla PRODUCTO contiene una lista de todos sus productos estándar.

La tabla COMPONENTE enumera los componentes que entran en sus productos.

Las pistas de mesa COMP_USED componentes que entran en cada producto.

Un componente puede ser utilizado en varios productos, y un producto puede contener múltiples componentes (una relación de muchos a muchos). Esta situación puede causar problemas de integridad. Para evitar los problemas, crear la tabla de vinculación COMP_USED relacionar COMPONENTE a PRODUCT. Un componente puede aparecer en muchas filas de la tabla COMP_USED, pero cada una de esas filas hará referencia sólo un componente (una relación de uno a muchos).

Del mismo modo, un producto puede aparecer en muchas filas en COMP_USED, pero cada referencias de fila sólo un producto (otra relación de uno a muchos). Adición de la mesa de enlace transforma una relación problemática de muchos a muchos en dos relativamente simples uno-a-muchos. Este proceso de reducción de la complejidad de las relaciones es un ejemplo de la normalización.

Las subconsultas introducidas por la palabra clave IN

Una forma de una consulta anidada compara un valor único con el conjunto de valores devueltos por una SELECT comunicado. Se utiliza el EN predicar con la siguiente sintaxis:

SELECT column_listDE mesaDONDE expresión IN (subconsulta) -

La expresión en el DÓNDE cláusula se evalúa como un valor. Si ese valor es EN la lista devuelta por la subconsulta, entonces el DÓNDE cláusula devuelve un valor True. Las columnas especificadas en la fila de la tabla están procesando se añaden a la tabla de resultados. La subconsulta puede hacer referencia a la misma tabla referenciada por la consulta externa, o puede hacer referencia a una tabla diferente.

En el siguiente ejemplo, la base de datos de Zetec se utiliza para demostrar este tipo de consulta. Supongamos que hay una escasez de monitores de ordenador en la industria informática, para que cuando te quedas sin monitores, que ya no puede entregar los productos que los incluyen. ¿Quieres saber qué productos se ven afectados. Al mirar con gratitud a su propio monitor, introduzca la siguiente consulta:

SELECT ModelFROM COMP_USEDWHERE compid IN (SELECT CompIDFROM COMPONENTWHERE COMPTYPE = "Monitor") -

SQL procesa la consulta interna en primer lugar, por lo que los procesos de la tabla COMPONENTE, devolviendo el valor de Compid para cada fila donde COMPTYPE es "Monitor". El resultado es una lista de las Identificación los números de todos los monitores. La consulta externa compara entonces el valor de Compid en cada fila en la tabla COMP_USED contra la lista.

Si la comparación tiene éxito, el valor de la Modelo columna para esa fila se añade a la externa SELECT's tabla de resultados. El resultado es una lista de todos los modelos de productos que incluyen un monitor. El siguiente ejemplo muestra lo que ocurre cuando se ejecuta la consulta:

Modelo ----- CX3000CX3010CX3020MB3030MX3020MX3030

Ahora ya sabe qué productos pronto estará fuera de stock. Es hora de ir a la fuerza de ventas y decirles a disminuir en la promoción de estos productos.

Cuando se utiliza esta forma de consulta anidada, la subconsulta debe especificar una sola columna, y el tipo de datos de esta columna debe coincidir con el tipo de datos del argumento anterior a la EN palabra clave.

Con suerte, usted recuerda el principio KISS. Mantener las cosas simples Es importante cuando usted está tratando con el software de cualquier tipo, pero es especialmente importante cuando se trata de software de base de datos. Las declaraciones que incluyen anidadas SELECTs puede ser difícil de hacerlo bien.

Una manera de hacerlos funcionar como deberían es ejecutar el interior SELECT por sí mismo primero y luego verifique que el resultado que se obtiene es el resultado que usted espera. Cuando esté seguro de que el interior SELECT funciona correctamente, puede encerrarlo en la parte exterior de la declaración y tener una mejor oportunidad de que todo funciona como se anuncia.

Las subconsultas introducidas por la palabra clave NOT IN

Así como usted puede introducir una subconsulta con la EN palabra clave, puede hacer lo contrario e introducirlo con el NO EN palabras clave. De hecho, ahora es un buen momento para la gestión Zetec para hacer una consulta tal. Gestión Zetec se enteró de lo productos que no venden. Esa es una información valiosa, pero la dirección lo Zetec realmente quiere saber es qué productos a vender.

Gestión quiere hacer hincapié en la venta de productos que ¿no contener monitores. Una consulta anidada con una subconsulta introducida por la NO EN palabras clave proporciona la información solicitada:

SELECT ModelFROM COMP_USEDWHERE compid NO EN (SELECCIONAR CompIDFROM COMPONENTWHERE COMPTYPE = "Monitor")) -

Esta consulta produce el siguiente resultado:

Modelo ----- PX3040PB3050PX3040PB3050

En el ejemplo, el número de filas no crea un problema porque la tabla de resultados es corto. En el mundo real, una mesa de tal resultado puede tener cientos o miles de filas. Para evitar confusiones, es mejor para eliminar los duplicados. Puede hacerlo fácilmente mediante la adición de la DISTINCT palabra clave para la consulta. Sólo las filas que son distintos de todas las filas recuperados previamente se añaden a la tabla de resultados:

SELECT DISTINCT ModelFROM COMP_USEDWHERE compid NO IN (SELECT CompIDFROM COMPONENTWHERE COMPTYPE = "Monitor")) -

Como era de esperar, el resultado es el siguiente:

Modelo ----- PX3040PB3050