زیرکوئری در SQL اکسس با IN، EXISTS و کوئری همبسته

بعضی پرسش‌های داده‌ای با یک کوئری داخل کوئری دیگر روشن‌تر بیان می‌شوند: مشتریانی که سفارش داشته‌اند، محصولاتی که هرگز فروخته نشده‌اند، سفارش‌های دارای ردیف پُرتعداد یا رکوردهایی که از یک معیار مرتبط عبور می‌کنند. زیرکوئری در SQL اکسس این پرسش‌ها را مستقیم حل می‌کند.

این درس بر IN، EXISTS، NOT EXISTS و زیرکوئری همبسته تمرکز دارد. همچنین توضیح می‌دهد چه زمانی join خواناتر است، چرا وجود Null می‌تواند NOT IN را غیرمنتظره کند و ایندکس فیلد همبستگی چه اثری بر کارایی دارد.

مثال‌ها از زیرکوئری scalar در فهرست SELECT دوری می‌کنند و الگوهای مناسب فیلتر در Access را نشان می‌دهند. هر دستور ابتدا به‌صورت SELECT ذخیره‌شده آزمایش و سپس با پارامتر صریح از VBA باز می‌شود.

جایگاه این درس در مسیر آموزش SQL اکسس

زیرکوئری پس از مبحث JOIN قرار می‌گیرد، زیرا پرسش رابطه‌ای را با مدل ذهنی متفاوت حل می‌کند. join سطرها را در یک خروجی ترکیب می‌کند، اما EXISTS معمولاً سطر بیرونی را نگه می‌دارد و فقط وجود رکورد داخلی را می‌سنجد.

فرم مناسب را بر اساس هدف انتخاب کنید. IN برای مجموعه‌ای از مقدارهای قابل مقایسه، EXISTS برای بررسی صرف وجود، NOT EXISTS برای یافتن رابطه مفقود و زیرکوئری همبسته برای آزمونی مناسب است که با هر سطر بیرونی تغییر می‌کند.

پیش‌نیازها و مدل داده نمونه

در مثال‌ها از جدول‌های Customers، Orders، OrderDetails، Products و Categories استفاده می‌شود. CustomerID کلید اصلی مشتری، OrderID کلید اصلی سفارش و ProductID کلید اصلی محصول است. فیلدهای کلید خارجی باید با کلید اصلی متناظر نوع داده سازگار داشته باشند و در پایگاه‌های بزرگ برای join و فیلترهای پرتکرار ایندکس شوند.

پیش از اجرای مثال‌ها از فایل اکسس نسخه پشتیبان تهیه کنید و نام جدول‌ها و فیلدها را با پایگاه خود تطبیق دهید. اگر نامی فاصله دارد آن را داخل کروشه قرار دهید. ابتدا یک SELECT ساده با تعداد رکورد محدود بسازید و سپس clauseها، پارامترها یا عبارت‌های پیشرفته را مرحله‌به‌مرحله اضافه کنید.

ساختار اصلی و مدل ذهنی

زیرکوئری یک SELECT داخل دستور دیگر است. IN مقدار را با فهرست خروجی مقایسه می‌کند، EXISTS فقط وجود حداقل یک رکورد مرتبط را می‌سنجد، NOT EXISTS نبود رابطه را پیدا می‌کند و زیرکوئری همبسته به سطر جاری کوئری بیرونی ارجاع می‌دهد.

SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerID IN
    (SELECT CustomerID
     FROM Orders
     WHERE OrderDate >= #2026-01-01#);

دستور را بر اساس جریان منطقی داده تحلیل کنید: ابتدا سطرهای منبع مشخص می‌شوند، سپس فیلتر سطح رکورد اجرا می‌شود، داده در صورت نیاز ترکیب یا گروه‌بندی می‌شود، ستون‌های خروجی محاسبه می‌شوند، شرط سطح نتیجه اعمال می‌شود و در پایان مرتب‌سازی صورت می‌گیرد. این مدل نشان می‌دهد چرا جابه‌جایی یک شرط می‌تواند نتیجه را تغییر دهد.

کاربردهای مناسب

• مشتری دارای حداقل یک سفارش در بازه
• محصولی که هرگز در OrderDetails نیامده است
• سفارش دارای ردیف بیشتر از حد تعداد
• کارمند دارای وضعیت مرتبط در جدول دیگر
• یافتن رکورد بدون رابطه با NOT EXISTS

نمونه کاربردی با قواعد واقعی

قاعده نمونه پیشرفته، سفارش‌هایی را انتخاب می‌کند که حداقل یک ردیف با تعداد بیشتر از پارامتر دارند و در عین حال هیچ رکوردی برای برگشت کالا در جدول Returns ندارند.

PARAMETERS [pMinimumQuantity] Long;
SELECT O.OrderID,
       O.CustomerID,
       O.OrderDate
FROM Orders AS O
WHERE EXISTS
    (SELECT *
     FROM OrderDetails AS OD
     WHERE OD.OrderID = O.OrderID
       AND OD.Quantity >= [pMinimumQuantity])
  AND NOT EXISTS
    (SELECT *
     FROM Returns AS R
     WHERE R.OrderID = O.OrderID)
ORDER BY O.OrderDate DESC;

شرط OD.OrderID = O.OrderID زیرکوئری را به سفارش جاری متصل می‌کند. NOT EXISTS دوم همین الگو را برای Returns اجرا می‌کند. چون فقط وجود مهم است، ستون‌های زیرکوئری به نتیجه نهایی منتقل نمی‌شوند.

اجرای کوئری از VBA

برای اتوماسیون قابل نگهداری، SQL را به‌صورت یک QueryDef نام‌دار ذخیره و از DAO اجرا کنید. پارامتر صریح از اتصال متن به رشته SQL بهتر است، زیرا Access نوع داده را پیش از اجرا می‌شناسد. روال زیر الگوی عمومی را نشان می‌دهد؛ نام QueryDef و پارامترها را با کوئری ذخیره‌شده همین درس هماهنگ کنید.

Option Explicit

Public Sub ShowQualifiedOrders()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset

    On Error GoTo CleanFail

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryQualifiedOrdersBySubquery")
    qdf.Parameters("pMinimumQuantity") = 10
    Set rs = qdf.OpenRecordset(dbOpenSnapshot)

    Do While Not rs.EOF
        Debug.Print rs!OrderID, rs!CustomerID, rs!OrderDate
        rs.MoveNext
    Loop

CleanExit:
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Exit Sub

CleanFail:
    Debug.Print Err.Number, Err.Description
    Resume CleanExit
End Sub

در این نمونه Recordset از نوع snapshot برای خواندن نتیجه باز می‌شود. برای INSERT، UPDATE یا DELETE باید از روش اجرای action query استفاده و RecordsAffected را کنترل کنید. بخش پاک‌سازی نیز باید اشیا را در حالت موفق و خطا ببندد تا اتصال یا Recordset باز باقی نماند.

روش کنترل نتیجه

1. کوئری منبع را بدون clause پیشرفته اجرا و تعداد سطرهای اولیه را ثبت کنید.
2. یک حالت کوچک را دستی محاسبه و با خروجی SQL مقایسه کنید.
3. پارامتری وارد کنید که نتیجه خالی بدهد و رفتار کد فراخوان را بررسی کنید.
4. یک مقدار Null یا رکورد تکراری آزمایشی اضافه و انطباق نتیجه با قاعده مستند را کنترل کنید.
5. تعداد رکوردهای QueryDef ذخیره‌شده را با Recordset بازشده در VBA مقایسه کنید.
6. نام و نوع ستون‌های نهایی مورد نیاز گزارش یا export را ثبت کنید.

خطاهای رایج و روش اصلاح

کارایی، ایمنی و نگهداری

برای اجرای مطمئن زیرکوئری در SQL اکسس بهتر است ابتدا یک نسخه آزمایشی از پایگاه داده بسازید و چند رکورد با مقادیر قابل تشخیص در آن قرار دهید. کوئری را پیش از اتصال به فرم، گزارش یا کد VBA مستقیماً در محیط Query Design اجرا کنید. این روش خطای طراحی SQL را از خطای اتوماسیون جدا می‌کند و بررسی نتیجه را ساده‌تر می‌سازد.

زبان SQL اکسس به SQL استاندارد نزدیک است، اما در نشانه‌گذاری تاریخ، wildcardها، پارامترها و موتور عبارت‌ها تفاوت‌هایی دارد. هنگام کار با زیرکوئری در SQL اکسس ابتدا دستور را در خود Access آزمایش کنید. پس از تأیید نتیجه، همان منطق را به QueryDef ذخیره‌شده یا رشته SQL در VBA منتقل کنید تا خطاهای نقل‌قول و نوع پارامتر زودتر آشکار شوند.

برای فیلدهای محاسباتی alias روشن انتخاب کنید. نام مستعار باید معنای خروجی را نشان دهد، نه اینکه فقط عبارت را تکرار کند. alias مناسب اتصال گزارش، خواندن Recordset در VBA و نگهداری پروژه را آسان می‌کند. اگر نام فیلد فاصله دارد یا با واژه رزروشده تداخل پیدا می‌کند، آن را داخل کروشه قرار دهید.

رفتار مقدار Null را از ابتدا مشخص کنید. توابع تجمیعی، شرط‌ها، joinها و عبارت‌های محاسباتی همگی Null را یکسان مدیریت نمی‌کنند. ابتدا تعیین کنید Null در منطق کسب‌وکار به معنی مقدار ناشناخته، نامرتبط یا صفر است. تابع Nz را فقط زمانی به‌کار ببرید که جایگزینی Null با مقدار پیش‌فرض واقعاً درست باشد.

اعتبارسنجی ورودی را از اجرای کوئری جدا کنید. تاریخ‌ها، شناسه‌ها و حدود عددی باید پیش از تخصیص به پارامتر بررسی شوند. متن واردشده توسط کاربر را در صورت وجود پارامتر به SQL نچسبانید. پارامترها علاوه بر کاهش خطاهای قالب تاریخ و جداکننده اعشاری، مشکل apostrophe در متن را نیز کنترل می‌کنند.

کارایی را با داده‌ای نزدیک به حجم واقعی اندازه بگیرید. کوئری‌ای که روی بیست رکورد سریع است ممکن است روی صدها هزار رکورد کند شود. فیلدهای پرتکرار در join و فیلتر انتخابی را ایندکس کنید، روی فیلد ایندکس‌شده تابع غیرضروری اعمال نکنید و فقط ستون‌هایی را برگردانید که مرحله بعد واقعاً لازم دارد.

برنامه آزمون باید داده معمولی، مقدار مرزی، Null، رکورد تکراری و نتیجه خالی را پوشش دهد. بررسی کنید اگر هیچ رکوردی پیدا نشد، اگر یک گروه فقط یک عضو داشت یا اگر دو سطر مقدار مرتب‌سازی یکسان داشتند، خروجی همچنان قابل پیش‌بینی باشد. نتیجه مورد انتظار را پیش از بازنویسی کوئری ثبت کنید.

کوئری ذخیره‌شده بخشی از مستندات پروژه است. برای QueryDef نام توصیفی انتخاب کنید، در ماژول VBA یک توضیح کوتاه درباره ورودی و خروجی بنویسید و هر کوئری را بر یک مسئولیت متمرکز نگه دارید. چنین کوئری‌ای مستقیماً قابل آزمایش است و می‌تواند میان فرم، گزارش و کد مشترک باشد.

اگر خروجی کوئری برای گزارش مهم یا عملیات تغییر داده استفاده می‌شود، پارامترها و تعداد رکوردهای برگردانده‌شده را ثبت کنید. لازم نیست داده حساس در log ذخیره شود. زمان اجرا، نام کوئری، خلاصه پارامترها و تعداد سطرها معمولاً برای عیب‌یابی کافی است.

زیرکوئری در SQL اکسس را بخشی از یک زنجیره پردازش داده ببینید. ورودی، شکل خروجی و مصرف‌کننده نتیجه باید روشن باشد. اینکه خروجی برای نمودار، گزارش، export به Excel یا مرحله ویرایش داده استفاده می‌شود، روی aliasها، ترتیب، سیاست Null و میزان جزئیات اثر مستقیم دارد.

تمرین مرحله‌به‌مرحله

تمرین را در سه نسخه بسازید. در نسخه اول فقط سطرهای خام را با join و فیلتر ضروری برگردانید. در نسخه دوم تکنیک اصلی این درس را اضافه و نتیجه را با داده کم کنترل کنید. در نسخه سوم پارامتر، alias، مرتب‌سازی و روال VBA را بیفزایید. هر نسخه را موقتاً با نام جدا ذخیره کنید تا علت هر تغییر در خروجی مشخص باشد.

در تمرین دوم فقط یک قاعده کسب‌وکار را تغییر دهید؛ برای نمونه بازه گزارش، حذف سفارش‌های لغوشده، انتخاب دسته متفاوت یا تغییر خروجی از جزئیات به خلاصه. کوئری مناسب باید اجازه دهد این تغییر در یک پارامتر، شرط یا عبارت محاسباتی انجام شود و به بازنویسی چند بخش نامرتبط نیاز نداشته باشد.

در پایان کوئری را از دید توسعه‌دهنده دیگری بازبینی کنید. آیا بدون بازکردن فرم‌ها می‌توان جدول‌های ورودی، پارامترها، ستون‌های خروجی و سطح هر سطر را تشخیص داد؟ aliasهای مبهم را اصلاح، ستون‌های بلااستفاده را حذف و در ماژول VBA یک توضیح کوتاه درباره فرض‌ها ثبت کنید.

درس‌های مرتبط این مجموعه

این درس پس از مباحث SELECT، WHERE، JOIN و ویرایش داده قرار می‌گیرد. برای مرور عملیات تغییر رکورد می‌توانید مقاله ویرایش و حذف داده‌ها در SQL اکسس با VBA را بخوانید. پیشنهادهای لینک داخلی، درس‌های بعدی این مجموعه را نیز تا زمان انتشار با وضعیت planned نگه می‌دارند.

جمع‌بندی

پس از این درس می‌توانید پرسش عضویت یا نبود رابطه را به IN و EXISTS تبدیل کنید، ارجاع همبسته را تشخیص دهید، دام Null را کنترل و کوئری ذخیره‌شده را از VBA اجرا کنید.

روش قابل اعتماد، ساخت تدریجی است: SQL را با داده کنترل‌شده ثابت کنید، نوع پارامترها را صریح بنویسید، رفتار Null و داده تکراری را بیازمایید و سپس اتوماسیون را اضافه کنید. با این ترتیب، تغییر جدول‌ها، فرم‌ها یا نیاز گزارش‌گیری هزینه کمتری خواهد داشت.