SQL_for_Data_Analytics/06. JOIN's.sql at main · DeboraSouza277/SQL_for_Data_Analytics · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

-- INNER JOIN
-- LEFT JOIN
-- RIGHT JOIN
-- CROSS JOIN
-- MULTIPLOS JOIN
-- UNION
-- UNION ALL


-- INNER JOIN >> compara cada linha da tabela A com as linhas da tabela B para encontrar todos os pares de linhas


SELECT*FROM DimProduct

SELECT
	 DimProduct.ProductKey,
	 DimProduct.ProductName,
	 DimProduct.ProductSubcategoryKey,
	 DimProductSubcategory.ProductSubcategoryName --Buscar o nome da Subcategoria que está em outra tabela
FROM
	DimProduct
 INNER JOIN
	DimProductSubcategory
	ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey

-- LEFT JOIN >>Retorna todos os registros da tabela esquerda (tabela A) e os registros correspondentes da tabela direita (tabela B).
SELECT
	 DimProduct.ProductKey,
	 DimProduct.ProductName,
	 DimProduct.ProductSubcategoryKey,
	 DimProductSubcategory.ProductSubcategoryName --Buscar o nome da Subcategoria que está em outra tabela
FROM
	DimProduct
 LEFT JOIN
	DimProductSubcategory
	ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey

-- RIGHT JOIN >> Retorna todos os registros da tabel direita (tabela B ) e os registros correspondentes ŕs linhas da tabela esquerda (tabela A).

SELECT
	 DimProduct.ProductKey,
	 DimProduct.ProductName,
	 DimProduct.ProductSubcategoryKey,
	 DimProductSubcategory.ProductSubcategoryName --Buscar o nome da Subcategoria que está em outra tabela
FROM
	DimProduct
RIGHT JOIN
	DimProductSubcategory
	ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey

-- CROSS JOIN >> Produto Cartesiano

SELECT
 DimProduct.ProductName,
 DimProductSubcategory.ProductSubcategoryName
FROM DimProduct CROSS JOIN DimProductSubcategory


--MULTIPLOS JOIN'S (Resultando em uma tabela A com informaçăo da tabela B e tabela C)

SELECT * FROM DimProduct --tabela A
SELECT * FROM DimProductSubcategory  --tabela B
SELECT * FROM DimProductCategory  --tabela C


SELECT
	 DimProduct.ProductKey,
	 DimProduct.ProductName,
	 DimProduct.ProductSubcategoryKey,
	 DimProductSubcategory.ProductSubcategoryName,
	 DimProductCategory.ProductCategoryName
FROM
	DimProduct              --tabela A
 LEFT JOIN
	DimProductSubcategory   --tabela B
	ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey
	 LEFT JOIN
		DimProductCategory  --tabela C
			ON DimProductSubcategory.ProductCategoryKey=DimProductCategory.ProductCategoryKey