تفاوت INNER JOIN و LEFT JOIN (و گیری که COUNT با آن می‌خورید)

INNER JOIN فقط ردیف‌هایی را برمی‌گرداند که در هر دو جدول طرف دارند، و LEFT JOIN همه‌ی ردیف‌های جدول چپ را نگه می‌دارد حتی وقتی طرف مقابل چیزی ندارد. تفاوتشان درست همان‌جا خودش را نشان می‌دهد که دنبال «کاربرانی که سفارش نداشتند» می‌گردید: INNER JOIN همان‌هایی را که می‌خواهید ببینید بی‌سروصدا حذف می‌کند.

یک مثال با داده‌ی واقعی

سه کاربر داریم؛ یکی‌شان (مریم) هیچ سفارشی نداده:

sqlSELECT users.name, orders.amount
FROM users INNER JOIN orders ON orders.user_id = users.id;

name  amount
----  ------
سارا  150000
سارا  90000
رضا   300000

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

LEFT JOIN همان کاربر را با NULL نگه می‌دارد

sqlSELECT users.name, orders.amount
FROM users LEFT JOIN orders ON orders.user_id = users.id;

name  amount
----  ------
سارا  150000
سارا  90000
رضا   300000
مریم  NULL

حالا مریم هست، با amount خالی. این خالی‌بودن، دقیقاً همان اطلاعاتی است که در نسخه‌ی INNER JOIN گم شده بود.

پیدا کردن «چه کسی هیچ‌کدام را ندارد»

sqlSELECT users.name
FROM users LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.id IS NULL;

این الگو (LEFT JOIN به‌علاوه‌ی WHERE ... IS NULL روی کلید جدول راست) رایج‌ترین راه پرسیدن «چه چیزی وجود ندارد» است. NOT EXISTS هم دقیقاً همین مجموعه را برمی‌گرداند و معمولاً خواناتر است، پس هرکدام را که تیمتان راحت‌تر می‌خواند انتخاب کنید. کاری که با INNER JOIN نمی‌شود کرد همین است: ردیفی که از خروجی حذف شده را دیگر نمی‌شود فیلتر کرد.

گیری که COUNT با آن می‌خورد

sqlSELECT users.name, COUNT(*) AS order_count
FROM users LEFT JOIN orders ON orders.user_id = users.id
GROUP BY users.id;

name  order_count
----  -----------
سارا  2
رضا   1
مریم  1        -- غلط! مریم صفر سفارش دارد

LEFT JOIN برای مریم یک ردیف با NULL می‌سازد و COUNT(*) همان یک ردیف را می‌شمارد، پس نتیجه ۱ درمی‌آید نه ۰. راه درست این است که ستونی از جدول راست را بشمارید، نه * را:

sqlSELECT users.name, COUNT(orders.id) AS order_count
FROM users LEFT JOIN orders ON orders.user_id = users.id
GROUP BY users.id;
-- مریم: 0

COUNT(orders.id) مقدارهای NULL را نمی‌شمارد، پس مریم درست صفر می‌شود. نوشتن * به‌جای orders.id همان اشتباه کوچکی است که پشت خیلی از گزارش‌های غلط نشسته است.

یک گیر دیگر: شرط در WHERE، LEFT JOIN را دوباره INNER می‌کند

sqlSELECT users.name, orders.amount
FROM users LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.amount > 100000;

name  amount
----  ------
سارا  150000
رضا   300000

مریم دوباره غیب شد، چون NULL > 100000 نه درست است و نه غلط، بلکه نامعلوم می‌ماند، و WHERE هر ردیفی را که شرطش نامعلوم باشد کنار می‌گذارد. LEFT JOIN نوشته بودید، ولی نتیجه دقیقاً همان INNER JOIN شد. شرط را از WHERE به ON منتقل کنید:

sqlSELECT users.name, orders.amount
FROM users LEFT JOIN orders ON orders.user_id = users.id AND orders.amount > 100000;

حالا مریم با NULL می‌ماند، چون این شرط بخشی از خودِ پیوستن است و نه فیلتری که بعد از ساخته‌شدن نتیجه روی آن اجرا شود. هر شرطی را که فقط به ستون‌های جدول راست مربوط است در ON بنویسید و نه در WHERE، وگرنه LEFT JOIN عملاً به INNER JOIN تبدیل می‌شود.

اگر می‌خواهید SQL را از صفر و با کوئری‌های واقعی یاد بگیرید، دوره‌ی آموزش SQL و پایگاه داده از صفر دقیقاً همین گیرهای رایج را تمرین می‌کند.