Tecnologias e Linguagens para Banco de Dados II. Pesquisando multiplas tabelas com joins

(1)

Tecnologias e Linguagens

para Banco de Dados II

Aula 6

4329: Relacionando dados de multiplas tabelas

Pesquisando multiplas tabelas com joins

Pesquisando multiplas

tabelas com joins

INNER JOIN

OUTER JOIN

CROSS JOIN (Produto Cartesiano)

UNION

INNER JOIN

(2)

4

INNER JOIN

Usa um operador de comparação para relacionar os

dados de duas tabelas

Pode ser definido na clausula FROM ou na clausula

WHERE

Porém evite relacionar tabelas através da clausula WHEREPorém evite relacionar tabelas através da clausula WHERE

Valores NULL não podem ser comparados

Sintaxe:

FROM <tabela_a> [AS a] INNER JOIN <tabela_b> [AS b] FROM <tabela_a> [AS a] INNER JOIN <tabela_b> [AS b]

ON <tabela_a>.<campo1> = <tabela_b>.<campo1>

5

Exemplo1: INNER JOIN

USE AdventureWorks;

GO

SELECT e.EmployeeID, e.ManagerID,

c.FirstName + ' ' + UPPER(c.LastName) AS

Name, e.Title

FROM HumanResources.Employee AS e

INNER JOIN Person.Contact AS c ON

e.ContactID = c.ContactID;

GO

Exemplo2: INNER JOIN

USE AdventureWorks;

GO

SELECT s.Name AS StoreName, so.SalesOrderNumber,

so.OrderDate, so.TotalDue

FROM Sales.Store AS s

INNER JOIN Sales.SalesOrderHeader AS so ON

s.CustomerID = so.CustomerID

ORDER BY s.Name, so.OrderDate;

GO

(3)

7 7

Relacionando Multiplas

Tabelas

USE AdventureWorks; USE AdventureWorks; GO GO

SELECT p.Name 'Descição do Produto', v.Name AS 'Fornecedor'

FROM Production.Product AS p JOIN Purchasing.ProductVendor

AS pv AS pv ON p.ProductID = pv.ProductID ON p.ProductID = pv.ProductID JOIN Purchasing.Vendor AS v JOIN Purchasing.Vendor AS v ON pv.VendorID = v.VendorID ON pv.VendorID = v.VendorID WHERE ProductSubcategoryID = 15; WHERE ProductSubcategoryID = 15; GO GO 8 8

OUTER JOIN

Retorna todos os registros de (ao menos) uma das tabelas Retorna todos os registros de (ao menos) uma das tabelas relacionadas

relacionadas

LEFT OUTER JOINLEFT OUTER JOIN

Seleciona TODOS os registros da tabela a ESQUERDA e preenche comSeleciona TODOS os registros da tabela a ESQUERDA e preenche com NULL os campos da tabela a direita não relacionados

NULL os campos da tabela a direita não relacionados

RIGHT OUTER JOINRIGHT OUTER JOIN

Seleciona TODOS os registros da tabela a DIREITA e preenche com Seleciona TODOS os registros da tabela a DIREITA e preenche com NULL os campos da tabela a ESQUERDA não relacionados

NULL os campos da tabela a ESQUERDA não relacionados

FULL OUTER JOINFULL OUTER JOIN

Seleciona TODOS os registros de TODAS AS TABELAS e preenche Seleciona TODOS os registros de TODAS AS TABELAS e preenche com NULL os campos das tabelas que não se relacionam

com NULL os campos das tabelas que não se relacionam

LEFT OUTER JOIN

Sales.SalesPerson

Sales.SalesPerson Sales.SalesTerritorySales.SalesTerritory

Production.Product

(4)

10

LEFT OUTER JOIN

Exemplo1

USE AdventureWorks;

GO

SELECT st.Name AS Territory, sp.SalesPersonID

FROM Sales.SalesPerson AS sp

LEFT OUTER JOIN Sales.SalesTerritory AS st

ON st.TerritoryID = sp.TerritoryID;

GO

11 11

LEFT OUTER JOIN

Exemplo2

USE AdventureWorks;

GO

SELECT p.Name, pr.ProductReviewID

FROM Production.Product AS p

LEFT OUTER JOIN Production.ProductReview AS pr

ON p.ProductID = pr.ProductID

ORDER BY pr.ProductReviewID DESC;

GO

RIGHT OUTER JOIN

Sales.SalesTerritory

(5)

13

RIGHT OUTER JOIN

Exemplo

USE AdventureWorks;

GO

SELECT st.Name AS Territory, sp.SalesPersonID

FROM Sales.SalesTerritory AS st

RIGHT OUTER JOIN Sales.SalesPerson AS sp

ON st.TerritoryID = sp.TerritoryID;

GO

14 14

FULL OUTER JOIN

Sales.SalesTerritory

FULL OUTER JOIN

Exemplo

USE AdventureWorks;

GO

SELECT p.Name, sod.SalesOrderID

FROM Production.Product p

FULL OUTER JOIN Sales.SalesOrderDetail

sod

ON p.ProductID = sod.ProductID;

GO

(6)

16 16

CROSS JOIN

17 17

CROSS JOIN

Gera registros que contém:

Cada registro da tabela a esquerda combinado comCada registro da tabela a esquerda combinado com

Cada registro da tabela a direitaCada registro da tabela a direita

Produto Cartesiano

Por ser um resultado normalmente muito grande de

registros retornados o SQL Server

Cria um arquivo temporário em TempDBCria um arquivo temporário em TempDB

Perde performancePerde performance

Pode ser possível combinar campos e gerar um

resultado único (Massa de dados de teste e exemplo)

CROSS JOIN

Exemplo

USE AdventureWorks;

GO

SELECT e.EmployeeID, d.Name AS Department

FROM HumanResources.Employee AS e

CROSS JOIN HumanResources.Department AS d

ORDER BY e.EmployeeID, d.Name;

GO

(7)

19 19

UNION

Sintaxe:

declaracao_select UNION [ALL] declaracao_select

Existe um DISTINCT implícito na declaração

Colunas da primeira declaração select devem ser

iguais às da segunda declaração select

ALL

Permite retornar todas as linhas da segunda

consulta, o que melhora a performance da consulta

20

UNION

Declarações select serão avaliadas da esquerda para a

direita, parênteses redefinem a ordem

Conversão de dados implícita garante o sucesso da

consulta

Regras de uso do UNION:

Mesmo número de colunas com tipos de dados compatíveisMesmo número de colunas com tipos de dados compatíveis

ORDER BY ou COMPUTE são clausulas aplicadas após a ORDER BY ou COMPUTE são clausulas aplicadas após a

ultima declaração UNION no resultado final

GROUP BY e HAVING devem ser aplicados a cada GROUP BY e HAVING devem ser aplicados a cada

declaração select declaração select

Exemplo1: UNION

USE AdventureWorks; USE AdventureWorks; GO GO SELECT EmployeeID AS ID SELECT EmployeeID AS ID FROM HumanResources.Employee FROM HumanResources.Employee UNION UNION SELECT DepartmentID SELECT DepartmentID FROM HumanResources.Department FROM HumanResources.Department ORDER BY EmployeeID; ORDER BY EmployeeID; GO GO

Adicione o comando ALL ao lado de UNION e observe o resultadoAdicione o comando ALL ao lado de UNION e observe o resultado

Execute a primeira declaração select (comente as linhas de 6 a 8Execute a primeira declaração select (comente as linhas de 6 a 8))

(8)

22 22

Exemplo2: UNION

SELECT EmployeeID AS ID, Title AS Name

FROM HumanResources.Employee

UNION

SELECT AddressID, AddressLine1

FROM Person.Address

UNION

SELECT ContactID, FirstName

FROM Person.Contact

UNION

SELECT ContactTypeID, Name

FROM Person.ContactType

UNION

SELECT ProductID, Name

FROM Production.Product FROM Production.Product 23 23

Exemplo3: UNION

SELECT ProductID, ListPrice AS Price

FROM Production.Product

UNION ALL

(SELECT ProductID, UnitPrice

FROM Purchasing.PurchaseOrderDetail

UNION

SELECT ProductID, UnitPrice

FROM Sales.SalesOrderDetail);

GO

Alterne a posição dos parênteses e do comando ALL para obter Alterne a posição dos parênteses e do comando ALL para obter resultados diferentes.