Tudo sobre joins

Melhorando o Desempenho de Consultas no PostgreSQL

Amy McCarty

Instructor

Visão geral do curso

• Estrutura da consulta, incluindo joins, subconsultas e tabelas temporárias
• Limitando e agregando dados
• Propriedades de armazenamento e ferramentas de otimização
• Planejamento e execução da consulta

Planejador de consultas

Consulta

• Instruções SQL

Plano de (execução da) consulta

• Etapas reais

Planejador de consultas

 

O que são joins?

• Combinam várias tabelas

 

O que são joins?

• Combinam várias tabelas

Por que usar joins?

• Consultar tabelas de referência
• Combinar dados

Como?

• Inner e outer

 

Sales ID	Order Dt	Amt	Cust No
01	2019-02-02	145.30	911

 

ID	Name	Customer Since
911	Jim Smith	2019-01-01

 

Sales ID	Order Dt	Amt	Name
01	2019-02-02	145.30	Jim Smith

Inner joins

SELECT *
FROM athletes a
INNER JOIN countries c
ON a.country = c.country

 

 

 

Athlete	Country	Country1	Name	Pop (mil)
Jack	AUT	AUT	Austria	9
Aditya	IND	IND	India	1,339
Mikhail	RUS	RUS	Russia	145

Inner joins

SELECT *
FROM athletes a
INNER JOIN countries c
ON a.country = c.country

 

 

 

Athlete	Country	Country1	Name	Pop (mil)
Jack	AUT	AUT	Austria	9
Aditya	IND	IND	India	1,339
Mikhail	RUS	RUS	Russia	145

USING em inner joins

SELECT *
FROM athletes
INNER JOIN countries
USING (country)

 

 

 

Athlete	Country	Name	Pop (mil)
Jack	AUT	Austria	9
Aditya	IND	India	1,339
Mikhail	RUS	Russia	145

USING em inner joins

SELECT *
FROM athletes
INNER JOIN countries
USING (country)

 

 

 

Athlete	Country	Name	Pop (mil)
Jack	AUT	Austria	9
Aditya	IND	India	1,339
Mikhail	RUS	Russia	145

Left outer join

SELECT *
FROM athletes a
LEFT JOIN countries c
ON a.country = c.country

 

 

Athlete	Country	Country1	Name	Pop (mil)
Jack	AUT	AUT	Austria	9
Aditya	IND	IND	India	1,339
Mikhail	RUS	RUS	Russia	145
Javier	MEX

Left outer join

SELECT *
FROM athletes a
LEFT JOIN countries c
ON a.country = c.country

 

 

Athlete	Country	Country1	Name	Pop (mil)
Jack	AUT	AUT	Austria	9
Aditya	IND	IND	India	1,339
Mikhail	RUS	RUS	Russia	145
Javier	MEX

Right outer join

SELECT *
FROM athletes a
RIGHT JOIN countries c
ON a.country = c.country

 

 

Athlete	Country	Country1	Name	Pop (mil)
Jack	AUT	AUT	Austria	9
Aditya	IND	IND	India	1,339
Mikhail	RUS	RUS	Russia	145
		BRA	Brazil	209

Right outer join

SELECT *
FROM athletes a
RIGHT JOIN countries c
ON a.country = c.country

 

 

Athlete	Country	Country1	Name	Pop (mil)
Jack	AUT	AUT	Austria	9
Aditya	IND	IND	India	1,339
Mikhail	RUS	RUS	Russia	145
		BRA	Brazil	209

Full outer join

SELECT *
FROM athletes a
FULL OUTER JOIN countries c
ON a.country = c.country

• Plano (de execução) da consulta

• Restrições ao planejador de consultas

Athlete Nme	Country	Country1	Name	Pop (mil)
Jack	AUT	AUT	Austria	9
Aditya	IND	IND	India	1,339
Mikhail	RUS	RUS	Russia	145
Javier	MEX
		BRA	Brazil	209

Vamos praticar!

Melhorando o Desempenho de Consultas no PostgreSQL