آموزش postgres - بررسی self-join

در این مطلب در مورد این یاد می‌گیریم که چجوری با استفاده از self-join یک جدول رو با خودش مقایسه کنیم.

قبل از هر چیز توصیه میکنیم که اگر با joinها آشنایی ندارید، نخست آموزش انواع join رو مطالعه بفرمایید.

در واقع self-join یک join معمولی است که یک جدول را با خودش join می‌کند. بیشتر اوقات از این join برای اجرای کوئری‌هایی استفاده می‌کنیم که نتایج حالت خوشه‌ای و یا ارث‌بری دارند و یا حتی زمان‌هایی که میخواهیم اطلاعات یک سطر از یک جدول رو با بقیه‌ی سطر‌های همون جدول مقایسه کنیم.

در self-join تمام ارجاعات ما به یک جدول است اما برای ایجاد تفاوت باید هر بار برای جدول اسمی جدید در نظر بگیریم (table aliases)، به نحوه‌ی نوشتن توجه کنید:

SELECT select_list 
FROM table_name t1 
INNER JOIN table_name t2
ON join_predicate;

در دستور بالا جدول table_name را، که موقتا آنرا t1 نام گذاری کردیم، به همان جدول table_name که این‌بار موقتا آنرا t2 نام گذاری کردیم inner join زدیم. توجه کنید که ما می‌توانیم دستور بالا را به کمک left join و right join هم بنویسیم.

SELECT select_list 
FROM table_name t1
 LEFT JOIN table_name t2
 ON join_predicate;

اجازه بدید که آموزش رو با مثال پیش ببریم،

قصد داریم تا یک ساختار خوشه‌ای رو با استفاده از self-join پیاده سازی کنیم. فرض کنید که ساختار زیر رو برای یک سازمان داریم:

برای پیاده سازی این دستور ابتدا یک جدول employee می‌سازیم و داده‌های بالا را در آن ذخیره می‌کنیم.

CREATE TABLE employee (
	employee_id INT PRIMARY KEY,
	first_name VARCHAR (255) NOT NULL,
	last_name VARCHAR (255) NOT NULL,
	manager_id INT,
	FOREIGN KEY (manager_id) 
	REFERENCES employee (employee_id) 
	ON DELETE CASCADE
);
INSERT INTO employee (
	employee_id,
	first_name,
	last_name,
	manager_id
)
VALUES
	(1, 'Windy', 'Hays', NULL),
	(2, 'Ava', 'Christensen', 1),
	(3, 'Hassan', 'Conner', 1),
	(4, 'Anna', 'Reeves', 2),
	(5, 'Sau', 'Norman', 2),
	(6, 'Kelsie', 'Hays', 3),
	(7, 'Tory', 'Goff', 3),
	(8, 'Salley', 'Lester', 3);

توجه کنید که در جدول بالا ستون manager_id در واقع اشاره می‌کنید به ستون employee_id از همین جدول و به عبارت ساده بیان کننده این است که، مدیرِ شخصِ Salley، کارمندِ شماره‌ی ۳ است که Hassan نام دارد. زمانی که ستون manager_id خالی باشد به این معنا است که این شخص به خودش مقام بالا است و کسی manager او نیست.

حال قصد داریم با یک query مشخص کنیم که در داده‌های این جدول چه کسی مدیر فرد دیگری است:

SELECT
    e.first_name || ' ' || e.last_name employee,
    m.first_name || ' ' || m.last_name manager
FROM
    employee e
INNER JOIN employee m ON m.employee_id = e.manager_id
ORDER BY manager;

در دستور بالا دو بار به جدول employee ارجاع دادیم که با استفاده از table aliases (تعیین نام موقت برای جدول) یک بار آنرا جدول e و یک بار دیگر آنرا جدول m نام گذاری کردیم. در دستور select هم از تعیین نام موقت یا column aliases استفاده کردیم. (برای کسب اطلاعات بیشتر در این خصوص،‌ آموزش نام گذاری موقت در دیتابیس رو ببینید).

در join بالا تعیین کردیم که داده‌های ستون employee_id از جدول m رو با داده‌های ستون manager_id از جدول e مقایسه شوند و در صورتی که برابر باشند در نتایج ما قرار گیرند.

به خروجی دستور توجه کنید:

اگر توجه کنید متوجه می‌شوید که top manager یا افرادی که manager_id آنها null هست در این لیست وجود ندارند.

برای حل این مسئله نیز می‌تونیم کوئری رو با استفاده از LEFT JOIN به شکل زیر تغییر بدیم.

SELECT
    e.first_name || ' ' || e.last_name employee,
    m .first_name || ' ' || m .last_name manager
FROM
    employee e
LEFT JOIN employee m ON m .employee_id = e.manager_id
ORDER BY manager;

خروجی دستور بالا به این شکل خواهد بود:

بپردازیم به یک مثال دیگر که در آن قصد داریم سطر‌های یک جدول رو با سطر‌های دیگر از همون جدول مقایسه کنیم:

جدول زیر رو در نظر بگیرید که برای نگهداری اطلاعات فیلم‌ها ازش استفاده می‌کنیم.

در جدول بالا یک ستون به اسم length داریم که مدت زمان فیلم رو توی خودش نگه میداره و ما قصد داریم فیلم‌هایی که مدت زمان یکسانی رو دارند رو لیست کنیم. برای این کار از دستور زیر کمک می‌گیریم:

SELECT
    f1.title,
    f2.title,
    f1.length
FROM
    film f1
INNER JOIN film f2 
    ON f1.film_id <> f2.film_id AND 
       f1.length = f2.length;

در دستور بالا جدول film رو با خودش inner join زدیم و شرط گذاشتیم که فیلم‌هایی که طول یکسانی دارند لیست شوند در با f1.film_id <> f2.film_id مطمئن شدیم که یک فیلم با خودش مقایسه نشود.

خروجی دستور بالا به شکل زیر خواهد بود:

امیدوارم مطالب براتون مفید واقع شده باشه و لایک و کامنت رو فراموش نکنید.

پایان/