Joins के बारे में सब कुछ

PostgreSQL में क्वेरी प्रदर्शन सुधारना

Amy McCarty

Instructor

Course overview

Query संरचना: joins, subqueries, और temporary tables
डेटा को limit और aggregate करना
Database storage गुण और optimization टूल्स
Query planning और execution

Query planner

Query

SQL निर्देश

A restaurant waiter

Query (execution) plan

वास्तविक चरण

Multiple chefs around a big cooking pot

Query planner

Waiter bringing food

Waiter bringing drinks

Joins क्या हैं?

कई tables को मिलाएँ

Joins क्या हैं?

कई tables को मिलाएँ

Joins क्यों?

Lookup tables
डेटा मिलाएँ

कैसे?

Inner और 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

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

अभ्यास करते हैं!

PostgreSQL में क्वेरी प्रदर्शन सुधारना