استفاده و کاربرد Subquery

مثال 1:

فرض کنید می‌خواهیم لیست 5 کارمندی که بیشترین سابقه کار را در شرکت دارند، به همراه نام دپارتمانشان، نمایش دهیم.

راه حل:

1. استفاده از Subquery:

SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
ORDER BY e.Seniority DESC
LIMIT 5;

توضیح:

• این پرس و جو از دو جدول Employees و Departments استفاده می‌کند.
• Subquery در این مثال وجود ندارد.
• این پرس و جو اطلاعات 5 کارمند با بیشترین سابقه کار (Seniority) را به ترتیب نزولی (DESC) نمایش می‌دهد.

2. استفاده از Subquery (بهبود یافته):

SELECT EmployeeID, Name, DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE Seniority IN (
  SELECT Seniority
  FROM Employees
  ORDER BY Seniority DESC
  LIMIT 5
);

توضیح:

• در این مثال از Subquery برای انتخاب 5 سابقه کار (Seniority) بالاتر استفاده شده است.
• Subquery ابتدا اجرا می‌شود و 5 سابقه کار بالاتر را به عنوان خروجی برمی‌گرداند.
• پرس و جوی اصلی فقط کارمندانی را انتخاب می‌کند که سابقه کار آن‌ها در لیست 5 سابقه کار بالاتر (خروجی Subquery) باشد.

مزایای استفاده از Subquery در این مثال:

• خوانایی: Subquery باعث خوانایی بیشتر پرس و جو می‌شود.
• قابلیت تعمیم: به راحتی می‌توان تعداد کارمندان با سابقه کار بالاتر را با تغییر مقدار LIMIT در Subquery تغییر داد.
• کارایی: Subquery می‌تواند بهینه ‌تر از روش قبلی باشد، زیرا فقط به 5 رکورد با سابقه کار بالاتر نیاز دارد.

کاربرد Subquery:

Subqueryها کاربردهای گسترده‌ای در SQL دارند، از جمله:

• فیلتر کردن داده‌ها: Subqueryها برای فیلتر کردن داده‌ها بر اساس نتایج یک پرس و جوی دیگر به کار می‌روند.
• مقایسه داده‌ها: Subqueryها برای مقایسه نتایج یک پرس و جو با نتایج یک پرس و جوی دیگر به کار می‌روند.
• استخراج داده‌های وابسته: Subqueryها برای استخراج داده‌های وابسته از جداول دیگر به کار می‌روند.
• محاسبات پیچیده: Subqueryها برای انجام محاسبات پیچیده بر روی داده‌ها به کار می‌روند.

نکات:

• Subqueryها می‌توانند تو در تو (Nested) باشند، یعنی یک Subquery می‌تواند درون Subquery دیگری قرار گیرد.
• استفاده از Subquery می‌تواند خوانایی پرس و جو را کاهش دهد. در برخی موارد ممکن است بتوان از JOIN برای دستیابی به نتیجه مشابه استفاده کرد.
• برای بهبود کارایی پرس و جوهای دارای Subquery، استفاده از ایندکس (Indexes) مناسب توصیه می‌شود.

مثال 2:

فرض کنید می‌خواهیم لیست تمام کارمندانی را نمایش دهیم که در هر دو دپارتمان “فروش” و “بازاریابی” عضو هستند.

راه‌حل:

برای حل این مسئله می‌توانیم از یک Subquery در شرط WHERE پرس و جوی اصلی استفاده کنیم.

SELECT EmployeeID, Name, Department
FROM Employees
WHERE EmployeeID IN (
  SELECT EmployeeID
  FROM DepartmentMembers
  WHERE DepartmentName = 'Sales'
)
AND EmployeeID IN (
  SELECT EmployeeID
  FROM DepartmentMembers
  WHERE DepartmentName = 'Marketing'
);

توضیح:

1. پرس و جوی اصلی:

   • ستون‌های EmployeeID, Name و Department از جدول Employees را انتخاب می‌کند.
   • شرط WHERE تضمین می‌کند که EmployeeID در هر دو Subquery زیر وجود داشته باشد.

2. Subquery اول:

   • EmployeeID را از جدول DepartmentMembers برای دپارتمان “فروش” انتخاب می‌کند.

3. Subquery دوم:

   • EmployeeID را از جدول DepartmentMembers برای دپارتمان “بازاریابی” انتخاب می‌کند.

4. شرط AND:

   • تضمین می‌کند که EmployeeID در هر دو Subquery وجود داشته باشد، یعنی کارمند در هر دو دپارتمان عضو باشد.

کاربرد:

این مثال نشان می‌دهد که چگونه می‌توان از Subquery برای فیلتر کردن داده‌ها بر اساس نتایج یک پرس و جوی دیگر استفاده کرد. Subqueryها در سناریوهای مختلفی از جمله موارد زیر کاربرد دارند:

• فیلتر کردن داده‌ها بر اساس مقادیر پویا:
  • برای مثال، می‌توانید از Subquery برای فیلتر کردن داده‌ها بر اساس مقدار یک پارامتر ورودی استفاده کنید.
• مقایسه با نتایج دیگر پرس و جوها:
  • برای مثال، می‌توانید از Subquery برای یافتن رکوردهایی که در یک جدول وجود دارند اما در جدول دیگر وجود ندارند استفاده کنید.
• استخراج داده‌های وابسته از جداول مختلف:
  • برای مثال، می‌توانید از Subquery برای استخراج اطلاعات مربوط به یک کارمند از چندین جدول مرتبط استفاده کنید.

نکات:

• Subqueryها می‌توانند تو در تو (Nested) باشند، یعنی یک Subquery می‌تواند درون Subquery دیگری قرار گیرد.
• استفاده از Subquery می‌تواند خوانایی پرس و جو را کاهش دهد. در برخی موارد ممکن است بتوان از JOIN برای دستیابی به نتیجه مشابه استفاده کرد.
• برای بهبود کارایی پرس و جوهای دارای Subquery، استفاده از ایندکس (Indexes) مناسب توصیه می‌شود.

مثال‌های دیگر:

• یافتن کارمندانی که حقوقشان از میانگین حقوق دپارتمانشان بیشتر است:

SELECT EmployeeID, Name, Salary, Department
FROM Employees
WHERE Salary > (
  SELECT AVG(Salary)
  FROM Employees
  WHERE Department = 'Sales'
);

• یافتن محصولاتی که در هیچ سفارشی وجود ندارند:

SELECT ProductID, ProductName
FROM Products
WHERE ProductID NOT IN (
  SELECT ProductID
  FROM OrderItems
);

با استفاده از Subqueryها می‌توانید پرس و جوهای پیچیده‌ای را در SQL ایجاد کنید و به اطلاعات مورد نیاز خود از پایگاه داده دسترسی پیدا کنید.