Daniel Paulodptsalvador@hotmail.com

Consultas com Subqueries

São consultas aninhadas em instruções SELECT, INSERT, DELETE ou UPDATE.

O limite máximo de aninhamento de uma subquery é de 32 níveis.

Principais características- As SUBQUERIES podem ser escalares (um valor conforme tipo de dados definido)- É possível obter 1 ou mais colunas por SUBQUERY- Deve estar entre parênteses- Em instruções SELECT, UPDATE, INSERT e DELETE uma SUBQUERY é utilizada nos mesmos locais das

expressões- Alguns tipos de dados não podem ser utilizados nas lista de seleção: NTEXT, TEXT e IMAGE

- Operadores (=, < , > , < > , > = , <=, !> ou !< ) devem ser associados a SUBQUERY que retornam apenas 1 valor com 1 coluna

- Para utilização de listas de valores é necessário utilizar IN ou EXISTS- A utilização da cláusula ORDER BY só é possível caso a cláusula TOP

seja utilizada

-- Clientes que compraram em Janeiro de 2007 SELECT * FROM CLIENTESWHERE EXISTS (SELECT * FROM PEDIDOS WHERE CODCLI = CLIENTES.CODCLI AND DATA_EMISSAO BETWEEN '2007.1.1' AND '2007.1.31') -- OUSELECT * FROM CLIENTESWHERE CODCLI IN (SELECT CODCLI FROM PEDIDOS WHERE DATA_EMISSAO BETWEEN '2007.1.1' AND '2007.1.31')

-- Clientes que NÃO compraram em Janeiro de 2007 SELECT * FROM CLIENTESWHERE NOT EXISTS (SELECT * FROM PEDIDOS WHERE CODCLI = CLIENTES.CODCLI AND DATA_EMISSAO BETWEEN '2007.1.1' AND '2007.1.31') -- OUSELECT * FROM CLIENTESWHERE CODCLI NOT IN (SELECT CODCLI FROM PEDIDOS WHERE DATA_EMISSAO BETWEEN '2007.1.1' AND '2007.1.31')

Subqueries introduzidas com IN e NOT IN-- Lista de empregados cujo cargo tenha salário inicial -- inferior a 5000SELECT * FROM EMPREGADOSWHERE COD_CARGO IN ( SELECT COD_CARGO FROM TABELACAR WHERE SALARIO_INIC <

5000)

--Lista de departamentos onde não existe nenhum funcionárioSELECT * FROM TABELADEPWHERE COD_DEPTO NOT IN ( SELECT COD_DEPTO FROM EMPREGADOS WHERE COD_DEPTO IS NOT NULL) --OU SELECT E.CODFUN , E.NOME, E.COD_DEPTO, E.COD_CARGO, D.COD_DEPTO, D.DEPTOFROM EMPREGADOS E RIGHT JOIN TABELADEP D ON E.COD_DEPTO = D.COD_DEPTO WHERE E.COD_DEPTO IS NULL

-- Lista de cargos em que não existe nenhum funcinário -- cadastradoSELECT * FROM TABELACARWHERE COD_CARGO NOT IN ( SELECT COD_CARGO FROM EMPREGADOS WHERE COD_CARGO IS NOT NULL) --OU SELECT C.COD_CARGO , C.CARGO FROM EMPREGADOS E RIGHT JOIN TABELACAR C ON E.COD_CARGO = C.COD_CARGO WHERE E.COD_CARGO IS NULL

Subqueries introduzidas com sinal de =-- Funcionário(s) que ganha(m) menosSELECT * FROM EMPREGADOSWHERE SALARIO = (SELECT MIN(SALARIO) FROM Empregados)-- o mesmo queSELECT TOP 1 WITH TIES * FROM EMPREGADOSWHERE SALARIO IS NOT NULLORDER BY SALARIO

-- Funcionário mais novo na empresaSELECT * FROM EMPREGADOSWHERE DATA_ADMISSAO = (SELECT MAX(DATA_ADMISSAO) FROM EMPREGADOS)-- O mesmo queSELECT TOP 1 WITH TIES * FROM EMPREGADOSORDER BY DATA_ADMISSAO DESC

Subqueries correlacionadasSão aquelas que possuem referência a uma ou mais colunas da query externa

-- Grava no campo SALARIO de cada funcionário o-- valor de salário inicial contido na tabela de cargos UPDATE EMPREGADOS SET SALARIO = (SELECT SALARIO_INIC FROM TABELACAR WHERE COD_CARGO = EMPREGADOS.COD_CARGO)

Subqueries correlacionadas com EXISTSAo utilizarmos a cláusula EXISTS não é retornado dados e sim TRUE ou FALSE- Antes de EXISTS não deve haver coluna, constante ou expressão- Ao utilizar EXISTS, sua lista de seleção será normalmente um asterisco-- Clientes que NÃO compraram em Janeiro de 2007 SELECT * FROM CLIENTESWHERE NOT EXISTS (SELECT * FROM PEDIDOS WHERE CODCLI = CLIENTES.CODCLI AND DATA_EMISSAO BETWEEN '2007.1.1' AND '2007.1.31')

Diferenças entre subqueries e associações

- Associações são mais rápidas, porém não lidam com lista de valores- As subqueries facilitam na visualização e organização da query

-- Calcula o total vendido por cada vendedor em Janeiro/2007-- e a porcentagem das suas vendas em relação ao total -- vendido no mêsSELECT P.CODVEN, V.NOME, SUM(P.VLR_TOTAL) AS TOT_VENDIDO, 100 * SUM(P.VLR_TOTAL) / (SELECT SUM(VLR_TOTAL) FROM PEDIDOS WHERE DATA_EMISSAO BETWEEN '2007.1.1' AND '2007.1.31') AS PORCENTAGEMFROM PEDIDOS P JOIN VENDEDORES V ON P.CODVEN = V.CODVEN WHERE P.DATA_EMISSAO BETWEEN '2007.1.1' AND '2007.1.31'GROUP BY P.CODVEN, V.NOME

Tabelas temporárias

- São armazenadas no banco tempdb- Utilizar o símbolo # para tabela temporária associadas a conexão atual- Quando é utilizado o símbolo ## todos os outros usuários poderão visualizar- Tabelas criadas localmente serão excluídas ao encerrar a conexão- Tabelas globais serão excluídas quando todo os usuários conectados a elas encerram suas seções

Exemplo página 210

Laboratório página 256 à 263