دستور FULL JOIN در sql

FULL JOIN که با نام FULL OUTER JOIN نیز شناخته می شود، ردیف های هر دو جدول را در خروجی نگه می دارد. شرط اتصال تعیین می کند کدام ردیف ها با هم تطبیق دارند؛ ردیف هایی که طرف متناظر ندارند هم حذف نمی شوند.

این رفتار برای گزارش هایی مفید است که باید تمام داده های دو منبع را کنار هم بررسی کنند؛ از جمله شناسایی مواردی که فقط در یکی از جدول ها ثبت شده اند. اگر هدف نمایش همه مشتریان و همه سفارش ها باشد، این اتصال می تواند مشتریان بدون سفارش و سفارش های بدون مشتری متناظر را نیز آشکار کند.

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

INNER JOIN فقط ردیف هایی را برمی گرداند که در هر دو جدول، بر اساس شرط اتصال، تطبیق دارند. در مقابل، FULL JOIN علاوه بر ردیف های مشترک، ردیف های بدون تطبیق را نیز از هر دو جدول نگه می دارد. به همین دلیل، نتیجه FULL JOIN معمولاً کامل تر است، اما ممکن است سطرهای بیشتری داشته باشد.

به زبان ساده، اگر اطلاعات افراد در یک جدول و سفارش ها در جدول دیگر باشند، INNER JOIN فقط افرادی را نمایش می دهد که سفارش متناظر دارند. FULL JOIN می تواند افراد بدون سفارش و سفارش هایی را که به فردی در جدول افراد وصل نشده اند نیز نشان دهد. این تفاوت در انتخاب دستور مناسب برای گزارش اهمیت زیادی دارد.

برای نمونه، اگر مسئول گزارش بخواهد فقط سفارش های دارای مشتری معتبر را ببیند، INNER JOIN گزینه طبیعی تری است. اما اگر قصد بررسی سفارش های ناقص و مشتریان بدون خرید را داشته باشد، استفاده از FULL JOIN اطلاعات بیشتری در اختیارش می گذارد.

بخش ON مشخص می کند کدام ستون ها برای تطبیق رکوردها با هم مقایسه شوند. معمولاً این شرط بین کلید اصلی یک جدول و کلید خارجی جدول دیگر قرار می گیرد؛ برای مثال، شناسه فرد در جدول افراد با شناسه فرد در جدول سفارش ها مقایسه می شود.

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

برای مثال، اگر مقدار Persons.P_Id با Orders.P_Id ارتباط داشته باشد، شرط اتصال باید همین رابطه را بیان کند. در یک مجموعه داده کوچک می توان خروجی را دستی بررسی کرد؛ در داده های بزرگ، شمارش ردیف ها و نمونه گیری از نتایج به ارزیابی صحت اتصال کمک می کند.

وقتی FULL JOIN برای یک ردیف، رکورد متناظری در یکی از جدول ها پیدا نمی کند، ستون های متعلق به سمت بدون تطبیق با NULL پر می شوند. این مقدار به معنی صفر، رشته خالی یا مقدار نادرست نیست؛ بلکه نشان می دهد داده متناظر در نتیجه وجود ندارد یا مقدار آن مشخص نشده است.

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

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

همه سامانه های مدیریت پایگاه داده از نگارش مستقیم FULL OUTER JOIN پشتیبانی نمی کنند. پیش از استفاده، مستندات نسخه و موتور پایگاه داده خود را بررسی کنید؛ زیرا ممکن است برای رسیدن به نتیجه مشابه به راهکار جایگزین نیاز داشته باشید.

اگر دستور در محیط شما پشتیبانی نشود، یکی از روش های رایج ترکیب نتیجه LEFT JOIN و RIGHT JOIN با UNION است. در این روش باید مراقب تکراری شدن ردیف های مشترک باشید. استفاده از UNION معمولاً ردیف های تکراری را حذف می کند، درحالی که UNION ALL آن ها را نگه می دارد.

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

FULL JOIN می تواند خروجی گسترده ای تولید کند؛ به همین دلیل بهتر است فقط ستون های مورد نیاز را در بخش SELECT بیاورید. استفاده از ستاره ممکن است ستون های تکراری یا اطلاعات غیرضروری را وارد نتیجه کند و خواندن گزارش را دشوارتر سازد.

پس از اتصال، با شرط های بخش WHERE می توانید نتیجه را فیلتر کنید، اما دقت داشته باشید که فیلتر روی ستون های سمت بدون تطبیق ممکن است ردیف های دارای NULL را حذف کند. اگر می خواهید همه ردیف های بدون تطبیق حفظ شوند، شرط را با دقت طراحی کنید و نتیجه را با هدف مورد نظر مقایسه کنید.

برای مثال، اگر گزارش باید همه افراد و همه سفارش ها را نشان دهد، فیلتر کردن ساده بر اساس ستونی از جدول سفارش ها می تواند افراد بدون سفارش را از خروجی حذف کند. در چنین شرایطی، جایگاه و منطق شرط ها را جداگانه ارزیابی کنید.

ساختار پایه این دستور از نام ستون های مورد نیاز، جدول اول، نوع اتصال، جدول دوم و شرط ON تشکیل می شود. در صورت نیاز، می توانید بعد از آن از WHERE برای فیلتر و از ORDER BY برای مرتب سازی نتیجه استفاده کنید.

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
  ON table_name1.column_name = table_name2.column_name;

در بسیاری از سامانه ها می توانید برای خوانایی بیشتر، به جای FULL JOIN از نگارش کامل FULL OUTER JOIN استفاده کنید. هر دو شکل برای بیان اتصال کامل به کار می روند، اما پشتیبانی دقیق آن ها به سامانه پایگاه داده بستگی دارد.

در مثال زیر، نام افراد و شماره سفارش ها در کنار هم نمایش داده می شوند. اتصال با استفاده از ستون P_Id انجام می گیرد. اگر فردی سفارش نداشته باشد یا سفارشی به فردی در جدول افراد وصل نباشد، رکورد مربوط به هر سمت همچنان در نتیجه باقی می ماند.

SELECT Persons.LastName,
       Persons.FirstName,
       Orders.OrderNo
FROM Persons
FULL JOIN Orders
  ON Persons.P_Id = Orders.P_Id
ORDER BY Persons.LastName;

در این پرس وجو، Persons و Orders نام جدول ها هستند. شرط ON کلیدهای ارتباطی را مشخص می کند و ORDER BY نتیجه را بر اساس نام خانوادگی مرتب می سازد. اگر مقدار نام خانوادگی برای بعضی ردیف ها موجود نباشد، جایگاه آن ها در ترتیب خروجی ممکن است به تنظیمات سامانه وابسته باشد.

نتیجه را به سه گروه اصلی تقسیم کنید: رکوردهایی که در هر دو جدول تطبیق دارند، رکوردهایی که فقط در جدول اول هستند و رکوردهایی که فقط در جدول دوم دیده می شوند. این دسته بندی نشان می دهد اتصال چگونه داده ها را کنار هم قرار داده است.

برای کنترل خروجی، می توانید شناسه های هر دو جدول را در نتیجه بیاورید. اگر شناسه سمت اول مقدار دارد و شناسه سمت دوم NULL است، معمولاً رکورد متناظر در جدول دوم پیدا نشده است. اگر وضعیت برعکس باشد، رکوردی در جدول دوم وجود دارد که در جدول اول همتای مناسبی ندارد.

این بررسی برای پاک سازی داده ها نیز کاربرد دارد. برای مثال، می توان شناسه های اشتباه، ورود ناقص اطلاعات یا تفاوت در قالب مقادیر کلیدی را شناسایی کرد. با این حال، NULL همیشه به معنی خطا نیست؛ گاهی نبود سفارش برای یک فرد کاملاً طبیعی است.

مهم ترین مزیت FULL JOIN این است که هیچ یک از دو مجموعه را صرفاً به دلیل نبود ردیف متناظر کنار نمی گذارد. این ویژگی برای مقایسه فهرست ها، بررسی همگام سازی اطلاعات و تهیه گزارش جامع مفید است.

برای مثال، تیم پشتیبانی می تواند فهرست حساب های کاربری را با فهرست فعالیت ها مقایسه کند و هم حساب های بدون فعالیت و هم فعالیت های بدون حساب معتبر را ببیند. این دید دوطرفه، بررسی را کامل تر از اتصالی می کند که فقط یکی از جدول ها را مبنا قرار می دهد.

چون ردیف های بدون تطبیق در خروجی باقی می مانند، یافتن شناسه های جاافتاده یا ارتباط های ناقص ساده تر می شود. این نتیجه برای کنترل داده ها و پیدا کردن اختلاف بین دو سامانه مفید است.

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

از آن جا که FULL JOIN ردیف های بدون تطبیق را هم نگه می دارد، نتیجه می تواند از INNER JOIN بزرگ تر باشد. در جدول های حجیم، این موضوع ممکن است خواندن گزارش و تحلیل آن را دشوارتر کند و اجرای پرس وجو به منابع بیشتری نیاز داشته باشد.

راهکار مناسب این است که ستون های غیرضروری را حذف کنید، شرط اتصال را به درستی بنویسید و برای گزارش های نهایی فیلترهای هدفمند در نظر بگیرید. اگر فقط به یک سمت از داده ها نیاز دارید، شاید LEFT JOIN یا RIGHT JOIN انتخاب ساده تری باشد.

اگر پایگاه داده شما از FULL JOIN پشتیبانی نمی کند، گاهی می توان با ترکیب یک LEFT JOIN و یک RIGHT JOIN به نتیجه مشابه رسید. برای جلوگیری از دوباره شمردن ردیف های مشترک، از UNION استفاده می شود؛ البته رفتار و کارایی راهکار را باید در موتور مورد استفاده آزمایش کنید.

SELECT a.id, a.name, b.value
FROM table_a AS a
LEFT JOIN table_b AS b
  ON a.id = b.id

UNION

SELECT a.id, a.name, b.value
FROM table_a AS a
RIGHT JOIN table_b AS b
  ON a.id = b.id;

در این نمونه، بخش نخست همه ردیف های جدول table_a و ردیف های متناظر جدول table_b را می آورد. بخش دوم ردیف های جدول table_b را نیز حفظ می کند. دستور UNION نتیجه ها را ترکیب می کند و ردیف های کاملاً تکراری را حذف می کند؛ بنابراین، در صورت تفاوت داده ها یا نیاز به حفظ تکرارها، باید نتیجه را با دقت بررسی کنید.

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

اشتباه دیگر این است که وجود NULL را همیشه نشانه خرابی بدانیم. در اتصال کامل، NULL در ستون های یک سمت اغلب تنها نشان می دهد رکورد متناظر در آن جدول پیدا نشده است. برای قضاوت درست باید قواعد کسب وکار و معنای داده ها را نیز در نظر بگیرید.

همچنین، افزودن شرط محدودکننده در WHERE بدون توجه به اثر آن بر ردیف های فاقد تطبیق ممکن است بخشی از نتیجه مورد انتظار را حذف کند. در چنین مواردی، خروجی هر مرحله از پرس وجو را جداگانه بررسی کنید و از شرطی استفاده کنید که هدف گزارش را حفظ می کند.

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

برای استفاده درست، شرط ON را با توجه به رابطه واقعی جدول ها انتخاب کنید، مقدارهای NULL را با دقت تفسیر کنید و پشتیبانی پایگاه داده خود را بسنجید. اگر فقط ردیف های مشترک را می خواهید، INNER JOIN مناسب تر است؛ اگر به همه ردیف های دو سمت نیاز دارید، FULL JOIN می تواند انتخاب موثری باشد.