Como usar consultas sql aninhados 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 tabelas, mas agora você está preocupado com apenas três deles: tabela de produtos, a tabela de COMP_USED, e mesa do componente. A tabela de produtos contém uma lista de todos os seus produtos padronizados.

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

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

Um componente pode ser utilizado em vários produtos, e um produto pode conter múltiplos 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 destas linhas de referência será apenas um componente (a relação de 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 uma relação problemático n para n em duas relações relativamente simples de 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 único valor 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 EM (subconsulta) -

A expressão no 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 a ser processado são adicionados à tabela de resultados. A subconsulta pode fazer referência a mesma tabela referenciada pela consulta externa, ou pode referenciar uma tabela diferente.

Video: Curso SQL. Cláusula Order By. Ordenando registros. Vídeo 4

No exemplo a seguir, o banco de dados de Trend é usado 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 oferecer 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.

Video: 12 - T-SQL - DISTINCT- Consultas com valores distintos, sem repetição - SQL Server

Se a comparação for bem sucedida, o valor da Modelo coluna para essa linha é adicionado 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

agora você 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 destes 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.

Felizmente, 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 do jeito que deveriam é executar o interior SELECIONAR por si só e depois verificar que 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 gestão que Zetec realmente quer saber é quais produtos para vender.

Video: Introduction to Nested Select Statements

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 resultado é 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. Apenas 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