دستور LEFT JOIN در sql

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

در یک کوئری، جدولی که پس از FROM می آید جدول سمت چپ است و جدولی که پس از LEFT JOIN نوشته می شود جدول سمت راست محسوب می شود. شرط اتصال در بخش ON مشخص می کند کدام ردیف ها با هم ارتباط دارند. بنابراین، جایگاه جدول ها و شرط اتصال هر دو در نتیجه نهایی اثر می گذارند.

برای مثال، ممکن است جدولی از مشتریان داشته باشید و بخواهید سفارش های هر مشتری را مشاهده کنید. اگر از INNER JOIN استفاده شود، مشتریانی که هنوز سفارشی ندارند از نتیجه حذف می شوند؛ اما با دستور LEFT JOIN، همه مشتریان نمایش داده می شوند و برای مشتری بدون سفارش، ستون های سفارش به صورت NULL برمی گردند.

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

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

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

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

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

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

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

برای پیدا کردن ردیف های بدون تطبیق، از شرطی مانند WHERE Orders.P_Id IS NULL استفاده کنید. در SQL برای بررسی مقدار تهی باید از IS NULL یا IS NOT NULL استفاده شود؛ مقایسه با = NULL روش درستی برای این کار نیست.

مثال کاربردی: برای تهیه فهرست افرادی که هیچ سفارشی ثبت نکرده اند، ابتدا افراد را با سفارش ها LEFT JOIN کنید و سپس ردیف هایی را نگه دارید که شناسه سفارش آن ها NULL است.

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

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

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

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

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

مثال کاربردی: اگر به جای فهرست سفارش ها، تعداد سفارش های هر مشتری را می خواهید، از تابع تجمیعی مانند COUNT و عبارت GROUP BY استفاده کنید. افزودن بی هدف DISTINCT ممکن است تکرار واقعی داده ها را پنهان کند، بدون آن که علت اصلی را برطرف کند.

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

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

در بعضی پایگاه های داده، دستور LEFT JOIN با نام دستور LEFT OUTER JOIN نوشته می شود. واژه OUTER اختیاری است و در این کاربرد، نتیجه مورد انتظار با LEFT JOIN تفاوتی ندارد.

بهتر است به جای انتخاب همه ستون ها با *، نام ستون های مورد نیاز را بنویسید. این کار باعث می شود خروجی روشن تر باشد، از ابهام میان ستون های هم نام جلوگیری شود و نگهداری کوئری در آینده ساده تر شود.

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

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

اگر فردی در جدول Persons وجود داشته باشد اما هیچ ردیف متناظری در Orders نداشته باشد، نام او همچنان در خروجی دیده می شود و مقدار Orders.OrderNo برابر NULL خواهد بود. اگر فرد چند سفارش داشته باشد، نام او به تعداد سفارش های مرتبط در چند ردیف تکرار می شود.

برای یافتن افرادی که هیچ سفارشی ندارند، می توانید خروجی را با شرط Orders.P_Id IS NULL محدود کنید. در این حالت، ستون شناسه جدول راست که برای اتصال استفاده شده بررسی می شود تا ردیف هایی که تطبیق نداشته اند باقی بمانند.

SELECT Persons.LastName, Persons.FirstName
FROM Persons
LEFT JOIN Orders
  ON Persons.P_Id = Orders.P_Id
WHERE Orders.P_Id IS NULL
ORDER BY Persons.LastName;

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

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

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

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

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

دستور LEFT JOIN در SQL زمانی انتخاب مناسبی است که می خواهید همه ردیف های یک جدول اصلی را نگه دارید و اطلاعات جدول دیگری را در صورت وجود رابطه به آن اضافه کنید. ردیف های بدون تطبیق نیز در نتیجه باقی می مانند و ستون های جدول راست برای آن ها مقدار NULL خواهند داشت.

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