خانه/ ارتباطات و JOINها/ SELF JOIN

SELF JOIN: وصل کردن یک جدول به خودش

مبتدی ۱۲ دقیقه مطالعه

SELF JOIN نوع جدید JOIN نیست — همان INNER JOIN یا LEFT JOIN است، فقط با یک تفاوت: هر دو طرف JOIN، یک جدولاند. این الگو دقیقاً وقتی به‌کار می‌آید که یک جدول، رابطه‌ای با خودش داشته باشد — رایج‌ترین مثال: کارمندی که مدیرش هم خودش یک کارمند دیگر در همان جدول است.

جدول جدید: employees با ستون خودارجاع

در این درس از یک جدول جدید استفاده می‌کنیم:

id	full_name	manager_id
1	دکتر رضایی	NULL (مدیرعامل، مدیر ندارد)
2	مهندس کریمی	1
3	مهندس احمدی	1
4	خانم صادقی	2

ستون manager_id یک کلید خارجی است که به همان جدول employees اشاره می‌کند — یعنی مقدارش، همان id یک کارمند دیگر در همین جدول است.

چالش: چطور نام مدیر را کنار نام کارمند نشان دهیم؟

مشکل این‌جاست: اگر بنویسی SELECT full_name, manager_id FROM employees، فقط عدد شناسه‌ی مدیر را می‌بینی، نه نامش. برای گرفتن نام مدیر، باید همین جدول را یک‌بار دیگر — با نام مستعار دیگری — به خودش JOIN بزنیم:

sql

SELECT
  e.full_name AS Employee,
  m.full_name AS Manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

این‌جا e نقش «کارمند» را بازی می‌کند و m نقش «مدیر» — با این‌که هر دو از یک جدول employees می‌آیند. توجه کن از LEFT JOIN استفاده کردیم نه INNER JOIN، چون «دکتر رضایی» مدیری ندارد (manager_idاش NULL است) و نباید کاملاً از نتیجه حذف شود. بیا ببینیم:

امتحانش کن — نمایش هر کارمند کنار مدیرش

«دکتر رضایی» با Manager = NULL نمایش داده شده (چون مدیر ندارد)، و بقیه هرکدام کنار نام مدیر واقعی‌شان دیده می‌شوند — حتی «خانم صادقی» که مدیرش («مهندس کریمی») خودش هم زیردست «دکتر رضایی» است.

چرا نام مستعار این‌جا اجباری است

در یک SELF JOIN، نوشتن employees.full_name دوبار در یک پرس‌وجو، برای SQL Server کاملاً مبهم است — نمی‌داند منظورت کدام نمونه از جدول است. به همین دلیل، نام مستعار (e و m) در SELF JOIN صرفاً یک ترجیح سبک نیست، بلکه اجباری است.

یک کاربرد دیگر: پیدا کردن همکاران هم‌مدیر

SELF JOIN را می‌توان برای پیدا کردن کارمندانی که مدیر مشترک دارند هم استفاده کرد:

sql

SELECT a.full_name AS Person1, b.full_name AS Person2
FROM employees a
INNER JOIN employees b
  ON a.manager_id = b.manager_id
  AND a.id <> b.id;
-- a.id <> b.id لازم است، وگرنه هر کارمند با خودش هم جفت می‌شود

بیا این را هم امتحان کنیم:

امتحانش کن — پیدا کردن هم‌تیمی‌ها (مدیر مشترک)

نکته: اگر شرط a.id <> b.id را حذف کنی، هر کارمند به‌عنوان «هم‌تیمی خودش» هم در نتیجه ظاهر می‌شود — چون manager_id هر کارمند با manager_id خودش هم که طبیعتاً برابر است. حذف نکردن این شرط، یکی از رایج‌ترین اشتباهات در نوشتن SELF JOIN است.

جمع‌بندی این درس (و کل فصل ۴)

SELF JOIN نوع جدیدی از JOIN نیست؛ فقط یک جدول را با نام‌های مستعار متفاوت، به خودش وصل می‌کند.
رایج‌ترین کاربرد آن، رابطه‌های سلسله‌مراتبی مثل کارمند-مدیر است.
نام مستعار در SELF JOIN اجباری است، چون بدون آن SQL Server نمی‌داند کدام نمونه از جدول مدنظرت است.
وقتی می‌خواهی جفت‌های متفاوت پیدا کنی (نه هر ردیف با خودش)، شرط a.id <> b.id را فراموش نکن.

با این درس، فصل ۴ (ارتباطات و JOINها) — شاید مهم‌ترین فصل این دوره تا این‌جا — کامل شد! از فصل بعدی، وارد پرس‌وجوهای پیشرفته‌تر می‌شویم: زیرپرس‌وجوها، EXISTS، CTE و توابع پنجره‌ای.