Funções e Exemplos de JOIN em SQL

Funções Parte 1 INNER JOIN LEFT outer JOIN RIGHT outer JOIN LEFT Exclusivo ou LEFT WHERE RIGHT Exclusivo ou RIGHT WHERE FULL outer JOIN FULL Exclusivo ou FULL WHERE DQL Tipos de Join Para ilustrarmos ainda mais o conceito de junção entre mais de duas tabelas veja o seguinte DER Exemplos de Fixação SELECT PCodigo PNome AS Nome do Produto PEstoque FNome AS Nome do Fornecedor FContato FTelefone CDescricao AS Cor FROM Produto AS P JOIN Cor AS C ON CidCor PidCor JOIN Fornecedor AS F ON FidFornecedor PidFornecedor Gerando a extração com JOIN entre as tabelas Exemplos de Fixação Quando aplicamos as cláusulas LEFT JOIN ou RIGHT JOIN percebam os resultados SELECT PCodigo PNome AS Nome do Produto PEstoque FNome AS Nome do Fornecedor FContato FTelefone CDescricao AS Cor FROM Produto AS P JOIN Fornecedor AS F ON PidFornecedor FidFornecedor RIGHT JOIN Cor AS C ON CidCor PidCor Exemplos de Fixação Trocando o RIGHT pelo LEFT o resultado é alterado SELECT PCodigo PNome AS Nome do Produto PEstoque FNome AS Nome do Fornecedor FContato FTelefone CDescricao AS Cor FROM Produto AS P JOIN Fornecedor AS F ON PidFornecedor FidFornecedor LEFT JOIN Cor AS C ON CidCor PidCor Exemplos de Fixação Se invertemos a ordem das junções também teremos diferenças SELECT PCodigo PNome AS Nome do Produto PEstoque FNome AS Nome do Fornecedor FContato FTelefone CDescricao AS Cor FROM Produto AS P LEFT JOIN Fornecedor AS F ON PidFornecedor FidFornecedor JOIN Cor AS C ON CidCor PidCor Exemplos de Fixação Novamente trocando LEFT por RIGHT obtemos resultados diferentes SELECT PCodigo PNome AS Nome do Produto PEstoque FNome AS Nome do Fornecedor FContato FTelefone CDescricao AS Cor FROM Produto AS P RIGHT JOIN Fornecedor AS F ON PidFornecedor FidFornecedor JOIN Cor AS C ON CidCor PidCor Exemplos de Fixação Com RIGHT JOIN e LEFT JOIN SELECT PCodigo PNome AS Nome do Produto PEstoque FNome AS Nome do Fornecedor FContato FTelefone CDescricao AS Cor FROM Produto AS P RIGHT JOIN Fornecedor AS F ON PidFornecedor FidFornecedor LEFT JOIN Cor AS C ON CidCor PidCor Exemplos de Fixação Utilizando as mesmas mas trocando as posições SELECT PCodigo PNome AS Nome do Produto PEstoque FNome AS Nome do Fornecedor FContato FTelefone CDescricao AS Cor FROM Produto AS P LEFT JOIN Fornecedor AS F ON PidFornecedor FidFornecedor RIGHT JOIN Cor AS C ON CidCor PidCor Exemplos de Fixação Por último com FULL JOIN SELECT PCodigo PNome AS Nome do Produto PEstoque FNome AS Nome do Fornecedor FContato FTelefone CDescricao AS Cor FROM Produto AS P FULL JOIN Fornecedor AS F ON PidFornecedor FidFornecedor FULL JOIN Cor AS C ON CidCor PidCor Exemplos de Fixação Funções de agregação são funções que estão embutidas builtin no banco de dados e utilizamos quando precisamos calcular valores contabilizar número de registros ou retornar os maiores e menores valores dentro de uma coluna Possuem as seguintes características Retornam valores escalares Retornam a coluna sem nome Ignoram colunas NULL exceção a COUNT Podem ser usadas nas cláusulas SELECT HAVING e ORDER BY Funções de Agregação São frequentemente utilizadas com a cláusula GROUP BY mas não são restritas ao uso sem este comando O exemplo abaixo mostra funções de agregação sem o uso do GROUP BY SELECT AVGunitprice AS avgprice MINqtyAS minqty MAXdiscount AS maxdiscount FROM SalesOrderDetails avgprice minqty maxdiscount 262185 1 0250 Funções de Agregação Iremos focar nas funções de agregação de uso comum mas é importante saber que existem outras categorias de funções agregadas como estatísticas e outras STDEV STDEVP VAR VARP SUM MIN MAX AVG COUNT COUNTBIG Uso Comum Estatísticas Funções de Agregação Podemos combinar a cláusula DISTINCT com funções de agregação para sumarizar somente valores ÚNICOS A agregação com a cláusula DISTINCT elimina valores duplicados não linhas exceto se utilizarmos SELECT DISTINCT Compare os resultados parciais do exemplo abaixo com e sem o uso da cláusula DISTINCT SELECT empid YEARorderdate AS orderyear COUNTcustid AS allcusts COUNTDISTINCT custid AS uniquecusts FROM SalesOrders GROUP BY empid YEARorderdate empid orderyear allcusts uniquecusts 1 2006 26 22 1 2007 55 40 1 2008 42 32 2 2006 16 15 Funções de Agregação A maioria das funções de agregação simplesmente ignoram o NULL e não geram nenhum erro As funções a seguir ignoram o NULL AVGcoluna COUNT coluna A função COUNT usada com é uma EXCEÇÃO à regra acima contabilizando TODAS AS LINHAS COUNT Esse tipo de comportamento das funções agregadas perante o NULL pode produzir resultados INCORRETOS como é o caso abaixo utilizando a função AVG SELECT AVGc2 AS AvgWithNULLs AVGISNULLc20 AS AvgWithNULLReplace FROM dbot2 Funções de Agregação GROUP BY cria grupos no retorno das linhas de acordo com a combinação das colunas escritas na cláusula GROUP BY O GROUP BY retira os detalhes das linhas fazendo um cálculo com a função de agregação selecionada para a coluna escolhida SELECT selectlist FROM tablesource WHERE searchcondition GROUP BY groupbylist SELECT empid SUMfreight AS fht FROM SalesOrders GROUP BY empid SELECT empid COUNT AS cnt FROM SalesOrders GROUP BY empid Cláusula GROUP BY Se a consulta query usa GROUP BY todas as fases subsequentes irão operar nos grupos HAVING SELECT e ORDER BY precisam NECESSARIAMENTE retornar apenas um valor por grupo Ordem Lógica Fase Comentário 5 SELECT 1 FROM 2 WHERE 3 GROUP BY Cria Grupos 4 HAVING Opera na Filtragem dos Grupos 6 ORDER BY Cláusula GROUP BY Todas as colunas que aparecerem no SELECT HAVING e ORDER BY precisam OBRIGATORIAMENTE estar ou no GROUP BY ou numa Função de Agregação orderid empid custid 10643 6 1 10692 4 1 10926 4 2 10625 3 2 10365 3 3 orderid empid custid 10643 6 1 10692 4 1 10926 4 2 10625 3 2 empid COUNT 6 1 4 2 3 1 GROUP BY empid WHERE custid IN12 SELECT orderid empid custid FROM SalesOrders SELECT output Cláusula GROUP BY Funções de agregação são comumente usadas em cláusula SELECT sumarizando as colunas colocadas no GROUP BY Funções de agregação podem referir qualquer coluna não apenas as que estiverem escritas no GROUP BY SELECT productid MAXqty AS largestorder FROM SalesOrderDetails GROUP BY productid SELECT custid COUNT AS cnt FROM SalesOrders GROUP BY custid Cláusula GROUP BY HAVING filtra os dados obtidos através do GROUP BY HAVING fornece condição de pesquisa que precisa ser satisfeita para cada grupo HAVING é processado após a execução do GROUP BY SELECT custid COUNT AS count FROM SalesOrders GROUP BY custid HAVING COUNT 10 Cláusula HAVING Leitura do arquivo PDF disponibilizado na plataforma Atividades extraclasse