Como usar Nested consultas SQL que retornam conjuntos de linhas

Para ilustrar como uma consulta SQL aninhado retorna um conjunto de linhas, imagine que você trabalha para um integrador de sistemas de equipamentos de informática. Sua empresa, Zetec Corporation, monta sistemas a partir de componentes que você compra, e depois vende-los para empresas e agências governamentais. Você manter o controle de seu negócio com um banco de dados relacional.

O banco de dados consiste em muitas mesas, mas agora você está preocupado com apenas três deles: a tabela de produtos, a tabela de COMP_USED, ea tabela do componente. A tabela de produtos contém uma lista de todos os seus produtos padronizados.

Coluna	Digitar	restrições
Modelo	CHAR (6)	CHAVE PRIMÁRIA
ProdName	CHAR (35)
ProdDesc	CHAR (31)
ListPrice	NUMERIC (9,2)

A tabela COMPONENTE lista os componentes que entram em seus produtos.

Coluna	Digitar	restrições
CompID	CHAR (6)	CHAVE PRIMÁRIA
CompType	CHAR (10)
CompDesc	CHAR (31)

As faixas de mesa COMP_USED quais componentes vão para cada produto.

Coluna	Digitar	restrições
Modelo	CHAR (6)	FOREIGN KEY (Para PRODUCT)
CompID	CHAR (6)	FOREIGN KEY (Por componente)

Um componente pode ser utilizado em vários produtos, e os produtos podem conter vários componentes (a relação muitos-para-muitos). Esta situação pode causar problemas de integridade. Para contornar os problemas, criar a tabela de ligação COMP_USED relacionar componente para PRODUTO. Um componente pode aparecer em muitas linhas na tabela COMP_USED, mas cada uma dessas linhas será referência apenas um componente (um relacionamento um-para-muitos).

Da mesma forma, um produto pode aparecer em muitas linhas em COMP_USED, mas cada referências de linha apenas um produto (um outro relacionamento um-para-muitos). Adicionando a tabela de ligação transforma um relacionamento problemático de muitos-para-muitos em duas relações relativamente simples um-para-muitos. Este processo de reduzir a complexidade de relações é um exemplo de normalização.

Subqueries introduzidas pela palavra-chave IN

Uma forma de uma consulta aninhada compara um valor único, com o conjunto de valores devolvidos por uma SELECIONAR declaração. Ele usa o DENTRO predicado com a seguinte sintaxe:

SELECIONAR column_listA PARTIR DE mesaONDE expressão IN (subconsulta) -

A expressão na ONDE cláusula é avaliada como um valor. Se esse valor é DENTRO a lista retornada pela subconsulta, então o ONDE cláusula retorna um valor True. As colunas especificadas da linha da tabela que está sendo processado são adicionados à tabela de resultados. A subconsulta pode fazer referência a mesma tabela referenciada pela consulta externa, ou pode fazer referência a uma tabela diferente.

No exemplo a seguir, o banco de dados de Trend é utilizado para demonstrar este tipo de consulta. Suponha que há uma escassez de monitores de computador na indústria de computadores, de modo que quando você correr para fora de monitores, você não pode mais fornecer produtos que os incluem. Você quer saber quais produtos são afetados. Olhando com gratidão em seu próprio monitor, digite a seguinte consulta:

SELECIONAR ModelFROM COMP_USEDWHERE CompID IN (SELECT CompIDFROM COMPONENTWHERE CompType = 'Monitor') -

SQL processa a consulta mais interna primeiro, então ele processa a tabela COMPONENT, retornando o valor de CompID para cada linha onde CompType é 'Monitor'. O resultado é uma lista do identidade números de todos os monitores. A consulta externa, em seguida, compara o valor de CompID em cada linha na tabela de COMP_USED contra a lista.

Se a comparação for bem sucedida, o valor do Modelo coluna para essa linha é adicionada ao exterior SELECIONARtabela de resultados 's. O resultado é uma lista de todos os modelos de produtos que incluem um monitor. O exemplo a seguir mostra o que acontece quando você executar a consulta:

Modelo ----- CX3000CX3010CX3020MB3030MX3020MX3030

Você já sabe quais os produtos que em breve estará fora de estoque. É hora de ir para a força de vendas e dizer-lhes para abrandar na promoção desses produtos.

Quando você usa esta forma de consulta aninhada, a subconsulta deve especificar uma única coluna e tipo de dados que coluna deve corresponder ao tipo de dados do argumento que precede a DENTRO palavra-chave.

Com sorte, você se lembra o princípio KISS. Manter as coisas simples É importante quando você está lidando com o software de qualquer tipo, mas é especialmente importante quando se trata de software de banco de dados. Declarações que incluem aninhados SELECIONARs pode ser difícil de acertar.

Uma maneira de levá-los a trabalhar da maneira que deveriam é executar o interior SELECIONAR por si só em primeiro lugar e, em seguida, verificar se o resultado que obtém é o resultado que você espera. Quando você tem certeza que o interior SELECIONAR é funcionando corretamente, você pode colocá-lo na parte exterior da declaração e ter uma melhor chance de que a coisa toda vai funcionar como anunciado.

Subqueries introduzidas pela palavra-chave NOT IN

Assim como você pode introduzir uma subconsulta com o DENTRO palavra-chave, você pode fazer o oposto e apresentá-lo com o NÃO EM palavras-chave. Na verdade, agora é um grande momento para a gestão Zetec para fazer tal consulta. gestão Zetec descobriu que produtos não vender. Essa é uma informação valiosa, mas a gerência que Zetec realmente quer saber é quais produtos para vender.

Gestão quer enfatizar a venda de produtos que não conter monitores. A consulta aninhada com uma subconsulta introduzida pela NÃO EM palavras-chave fornece as informações solicitadas:

SELECIONAR ModelFROM COMP_USEDWHERE CompID NOT IN (SELECT CompIDFROM COMPONENTWHERE CompType = 'Monitor')) -

Esta consulta produz o seguinte resultado:

Modelo ----- PX3040PB3050PX3040PB3050

No exemplo, o número de linhas não cria um problema porque a tabela de resultados é curto. No mundo real, tal tabela de resultados pode ter centenas ou milhares de linhas. Para evitar confusão, é melhor eliminar as duplicatas. Você pode fazê-lo facilmente, adicionando o DISTINCT palavra-chave para a consulta. Somente as linhas que são distintos de todas as linhas recuperadas anteriormente são adicionados à tabela de resultados:

SELECT DISTINCT ModelFROM COMP_USEDWHERE CompID NOT IN (SELECT CompIDFROM COMPONENTWHERE CompType = 'Monitor')) -

Como esperado, o resultado é o seguinte:

Modelo ----- PX3040PB3050