پرس‌وجوهای چندجدولی در SQL

این فصل سه موضوع اصلی را بررسی می‌کند: Joinها، نام مستعار جدول‌ها و Unionها.

Joinها

نام کامل ستون (Full Column Name) از نام جدول، یک نقطه و نام ستون ــ بدون فاصلهٔ بین آن‌ها ــ تشکیل می‌شود. در پرس‌وجوهای چندجدولی، هرگاه یک نام ستون در بیش از یک جدول ظاهر شود باید از نام کامل ستون استفاده شود.

نام‌های کامل ستون‌ها برای پایگاه دادهٔ تمرینی
TABLE	COLUMN	FULL COLUMN NAME
PERSONS	PERSON	PERSONS.PERSON
PERSONS	NAME	PERSONS.NAME
PERSONS	BDATE	PERSONS.BDATE
PERSONS	GENDER	PERSONS.GENDER
PERSONS	COUNTRY	PERSONS.COUNTRY
PERSONS	JOB	PERSONS.JOB
COUNTRIES	COUNTRY	COUNTRIES.COUNTRY
COUNTRIES	POP	COUNTRIES.POP
COUNTRIES	AREA	COUNTRIES.AREA
COUNTRIES	GNP	COUNTRIES.GNP
COUNTRIES	LANGUAGE	COUNTRIES.LANGUAGE
COUNTRIES	LITERACY	COUNTRIES.LITERACY
ARMIES	COUNTRY	ARMIES.COUNTRY
ARMIES	BUDGET	ARMIES.BUDGET
ARMIES	TROOPS	ARMIES.TROOPS
ARMIES	TANKS	ARMIES.TANKS
ARMIES	SHIPS	ARMIES.SHIPS
ARMIES	PLANES	ARMIES.PLANES
JOBS	JOB	JOBS.JOB
JOBS	TITLE	JOBS.TITLE
RELIGIONS	COUNTRY	RELIGIONS.COUNTRY
RELIGIONS	RELIGION	RELIGIONS.RELIGION
RELIGIONS	PERCENT	RELIGIONS.PERCENT

قالب نام کامل ستون چنین است: tablename.columnname.

ستون‌های دو یا چند جدول را می‌توان با روشی به نام Joining در یک جدول نتیجه ترکیب کرد. اصول Join در سه مرحلهٔ زیر نشان داده می‌شود. از دو جدول شروع می‌کنیم که دست‌کم یک ستون مشترک دارند.

PERSONS
PERSON	NAME	JOB
1	Einstein	S
2	Dickinson	W
3	Dickinson	W

JOBS
JOB	TITLE
S	Scientist
W	Writer
E	Entertainer

۱) همهٔ ردیف‌های جدول دوم را به هر ردیف جدول اول پیوست می‌کنیم

ترکیب اولیهٔ ردیف‌ها
PERSON	NAME	JOB	JOB	TITLE
1	Einstein	S	S	Scientist
1	Einstein	S	W	Writer
1	Einstein	S	E	Entertainer
2	Dickinson	W	S	Scientist
2	Dickinson	W	W	Writer
...	...	...	...	...

۲) سپس فقط ردیف‌هایی را نگه می‌داریم که ستون‌های مشترک مقادیر برابر دارند

ردیف‌های منطبق
PERSON	NAME	JOB	JOB	TITLE
1	Einstein	S	S	Scientist
2	Dickinson	W	W	Writer
3	Dickinson	W	W	Writer

۳) در پایان ستون زائد را حذف می‌کنیم؛ نتیجه چنین است

نتیجهٔ Join
PERSON	NAME	JOB	TITLE
1	Einstein	S	Scientist
2	Dickinson	W	Writer
3	Dickinson	W	Writer

برای Join کردن دو یا چند جدول با SQL: ۱) همهٔ جدول‌های موردنیاز را در بند FROM فهرست کنید؛ ۲) مقایسه‌های مناسب را در بند WHERE وارد کنید؛ و ۳) ستون‌هایی را که باید در نتیجه دیده شوند در بند SELECT مشخص کنید. نمونه:

PERSONS
PERSON	NAME	JOB
1	Einstein	S
2	Dickinson	W
3	Dickinson	W

JOBS
JOB	TITLE
S	Scientist
W	Writer
E	Entertainer

SELECT PERSON, NAME, PERSONS.JOB, TITLE
FROM PERSONS, JOBS
WHERE PERSONS.JOB = JOBS.JOB

PERSON	NAME	JOB	TITLE
1	Einstein	S	Scientist
2	Dickinson	W	Writer
3	Dickinson	W	Writer

چند جدول در بند FROM مشخص می‌شوند.
مقایسه‌های Join در بند WHERE وارد می‌شوند.
در صورت نیاز از نام کامل ستون استفاده می‌شود.

اکنون بند SELECT اجازهٔ نام کامل ستون را می‌دهد، FROM از فهرست جدول پشتیبانی می‌کند و WHERE شامل مقایسهٔ ستون‌باستون است.

خلاصهٔ نحو Join
CLAUSE	PARAMETERS	EXAMPLE
SELECT	*	*
SELECT	[ DISTINCT ] col [ AS alias ] list	NAME, PERSONS.JOB, TITLE
SELECT	expr [ AS alias ] list	POP / AREA AS DENSITY
SELECT	func [ AS alias ] list	MIN(POP) AS LOWEST
FROM	table list	PERSONS, JOBS
WHERE	comparisons	PERSONS.JOB = JOBS.JOB
GROUP BY	col list	JOB, COUNTRY
HAVING	comparisons with funcs	COUNT(*) > 30
ORDER BY	col [ DESC ] list	LANGUAGE DESC, COUNTRY
ORDER BY	pos [ DESC ] list	1 DESC, 3
ORDER BY	expr [ DESC ] list	POP / AREA DESC
ORDER BY	func [ DESC ] list	COUNT(*) DESC

تمرین‌ها

نام و زبان مادری هر دانشمندی را که در قرن بیستم به دنیا آمده است فهرست کنید. نتیجه ۶ ردیف دارد.
کشور، GNP و بودجهٔ نظامی همهٔ کشورهایی را که بیش از ۱۰۰ میلیون نفر جمعیت دارند فهرست کنید. نتیجه ۸ ردیف دارد.
پرس‌وجوی قبلی را بازبینی کنید تا درصد GNP هزینه‌شده برای امور نظامی را نیز شامل شود.

امتیاز اضافه

تقریباً چند عضو از ارتش آلمان Protestant هستند؟

نام مستعار جدول

کلیدواژهٔ AS را می‌توان در بند FROM برای تعریف نام مستعار جدول (Table Alias) ــ نامی که کاربر برای جدول تعیین می‌کند ــ استفاده کرد. Alias را می‌توان برای هر جدول تعریف کرد، اما بیشتر در پرس‌وجوهای چندجدولی برای کوتاه‌کردن نام‌های کامل ستون استفاده می‌شود.

SELECT PERSON, NAME, PERSONS.JOB, TITLE
FROM PERSONS, JOBS
WHERE PERSONS.JOB = JOBS.JOB

SELECT PERSON, NAME, P.JOB, TITLE
FROM PERSONS AS P, JOBS AS J
WHERE P.JOB = J.JOB

Alias جدول در بند FROM تعریف می‌شود.
Alias جدول را می‌توان در هر بندی استفاده کرد.
پس از تعریف Alias، دیگر نمی‌توان از نام اصلی همان جدول استفاده کرد.
در برخی گویش‌ها AS اختیاری است.

خلاصهٔ نحو با Alias جدول
CLAUSE	PARAMETERS	EXAMPLE
SELECT	*	*
SELECT	[ DISTINCT ] col [ AS alias ] list	NAME, PERSONS.JOB, TITLE
SELECT	expr [ AS alias ] list	POP / AREA AS DENSITY
SELECT	func [ AS alias ] list	MIN(POP) AS LOWEST
FROM	table [ AS alias ] list	PERSONS AS P, JOBS AS J
WHERE	comparisons	P.JOB = J.JOB
GROUP BY	col list	JOB, COUNTRY
HAVING	comparisons with funcs	COUNT(*) > 30
ORDER BY	col [ DESC ] list	LANGUAGE DESC, COUNTRY
ORDER BY	pos [ DESC ] list	1 DESC, 3
ORDER BY	expr [ DESC ] list	POP / AREA DESC
ORDER BY	func [ DESC ] list	COUNT(*) DESC

تمرین‌ها

با استفاده از Alias جدول، کشور، جمعیت و تعداد نیروهای هر کشور با بیش از ۱۰۰ میلیون نفر جمعیت را نمایش دهید. نتیجه ۸ ردیف دارد.
همهٔ زبان‌های کشورهایی را فهرست کنید که بیش از ۹۰ درصد Muslim هستند. از Alias جدول استفاده کنید و ردیف‌های تکراری را حذف کنید. چهار زبان از این نوع وجود دارد.

امتیاز اضافه

نام، عنوان شغلی و زبان مادری همهٔ افراد پایگاه داده را که قبل از ۱ ژانویهٔ ۱۴۰۰ متولد شده‌اند فهرست کنید. ۱۰ نفر وجود دارد.

Unionها

Union ردیف‌های دو پرس‌وجوی مشابه را در یک نتیجهٔ واحد و بدون ردیف تکراری ترکیب می‌کند. کلیدواژهٔ UNION بین پرس‌وجوها قرار می‌گیرد. اگر ORDER BY استفاده شود، باید فقط در پرس‌وجوی نهایی ظاهر شود و موقعیت ستون‌ها را مشخص کند.

SELECT COUNTRY
FROM COUNTRIES
WHERE POP > 200000000

UNION

SELECT COUNTRY
FROM RELIGIONS
WHERE RELIGION = 'ATHEISM'
AND PERCENT > 30

ORDER BY 1

COUNTRY
China
Czech Rep...
India
Indonesia
Russia
USA

تعداد و نوع ستون‌های دو پرس‌وجو باید با هم تطبیق داشته باشد.
کلیدواژهٔ UNION بین پرس‌وجوها قرار می‌گیرد.
ORDER BY در صورت استفاده باید آخرین بند باشد.
ORDER BY باید موقعیت ستون را مشخص کند.
ردیف‌های تکراری در نتیجه قرار نمی‌گیرند.

خلاصهٔ نحو با UNION
CLAUSE	PARAMETERS	EXAMPLE
SELECT	*	*
SELECT	[ DISTINCT ] col [ AS alias ] list	NAME, PERSONS.JOB, TITLE
SELECT	expr [ AS alias ] list	POP / AREA AS DENSITY
SELECT	func [ AS alias ] list	MIN(POP) AS LOWEST
FROM	table [ AS alias ] list	PERSONS, JOBS
WHERE	comparisons	PERSONS.JOB = JOBS.JOB
GROUP BY	col list	JOB, COUNTRY
HAVING	comparisons with funcs	COUNT(*) > 30
UNION	query	SELECT ...
ORDER BY	col [ DESC ] list	LANGUAGE DESC, COUNTRY
ORDER BY	pos [ DESC ] list	1 DESC, 3
ORDER BY	expr [ DESC ] list	POP / AREA DESC
ORDER BY	func [ DESC ] list	COUNT(*) DESC

تمرین‌ها

هشت کشوری را که بیش از ۴۰٪ Protestant هستند فهرست کنید؛ سپس چهار کشوری را که German صحبت می‌کنند فهرست کنید. این دو را Union کنید. نتیجهٔ نهایی چند ردیف دارد؟
همهٔ کشورهایی را که یک دانشمند تولید کرده‌اند با همهٔ کشورهایی که بودجهٔ نظامی بیش از ۱۰ میلیارد دارند Union کنید. نتیجه را بر اساس نام کشور مرتب کنید. ۱۲ ردیف وجود دارد.

امتیاز اضافه

نام شخص، نام کشور و نام زبان را در مواردی فهرست کنید که حرف دوم آن‌ها z است.

پرس‌وجوهای چندجدولی SQL؛ Join، نام مستعار جدول و UNION