All about joins

Improving Query Performance in PostgreSQL

Amy McCarty

Instructor

Course overview

Query structure, including joins, subqueries, and temporary tables
Limiting and aggregating data
Database storage properties and optimization tools
Query planning and execution

Query planner

Query

SQL instructions

A restaurant waiter

Query (execution) plan

Actual steps

Multiple chefs around a big cooking pot

Query planner

Waiter bringing food

Waiter bringing drinks

What are joins?

Combine multiple tables

What are joins?

Combine multiple tables

Why use joins?

Look up tables
Combine data

How?

Inner and 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

An athlete table with four records for athlete and country. Jack - AUT. Aditya -IND. Mikhail - RUS. Javier - MEX. A country table with four records for country, name, population in millions. AUT - Austria - 9. IND - India - 1,339. RUS - Russia - 145. BRA- Brazil - 209

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

An athlete table with four records for athlete and country. Jack - AUT. Aditya -IND. Mikhail - RUS. Javier - MEX. The last record is bold. A country table with four records for country, name, population in millions. AUT - Austria - 9. IND - India - 1,339. RUS - Russia - 145. BRA- Brazil - 209. The last record is bold.

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 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 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

Query (execution) plan

Multiple chefs around a big cooking pot

Constrains query planner

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

Let's practice!

Improving Query Performance in PostgreSQL