تصویر شاخص نوشته: JOIN در SQL چیست؟ فرق INNER JOIN و LEFT JOIN با مثال اجرایی

JOIN در SQL چیست؟ فرق INNER JOIN و LEFT JOIN با مثال اجرایی

۱۳ آبان ۱۴۰۵ مبانی داده

JOIN در SQL سطرهای دو جدول را با یک ستون مشترک کنار هم می‌گذارد. INNER JOIN فقط سطرهایی را نگه می‌دارد که در هر دو جدول جفت دارند. LEFT JOIN همهٔ سطرهای جدول چپ را نگه می‌دارد و جایی که جفت نیست، مقدار NULL می‌گذارد.

چرا داده در یک جدول نیست

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

مثال اجرایی

این کد را با پایتون اجرا کن. نصب جداگانه‌ای نمی‌خواهد، چون sqlite3 همراه پایتون است:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE enrollments (student_id INTEGER, course TEXT);
INSERT INTO students VALUES (1, 'Sara'), (2, 'Omid'), (3, 'Nika');
INSERT INTO enrollments VALUES
  (1, 'SQL'), (1, 'Python'), (2, 'SQL'), (9, 'CSS');
""")

query = """
SELECT s.name, e.course
FROM students s
JOIN enrollments e ON e.student_id = s.id
ORDER BY s.id, e.course
"""
print(db.execute(query).fetchall())

به داده نگاه کن. Nika ثبت‌نامی ندارد و ثبت‌نام CSS به شناسهٔ 9 اشاره می‌کند که چنین دانش‌آموزی نداریم. این دو سطر همهٔ فرق‌ها را نشان می‌دهند.

خروجی:

[('Sara', 'Python'), ('Sara', 'SQL'), ('Omid', 'SQL')]

ON شرط جفت‌شدن را می‌گوید. s و e نام کوتاه جدول‌ها هستند. JOIN تنها همان INNER JOIN است.

Sara دو بار آمد. اگر یک سطر دو جفت داشته باشد، JOIN دو سطر می‌سازد.

چهار نوع JOIN

نوع چه چیزی را نگه می‌دارد در مثال ما
INNER JOIN فقط سطرهای جفت‌دار Nika و CSS حذف می‌شوند
LEFT JOIN همهٔ جدول چپ Nika با NULL می‌ماند
RIGHT JOIN همهٔ جدول راست CSS با NULL می‌ماند
FULL JOIN همهٔ هر دو جدول هر دو می‌مانند

LEFT JOIN را با تغییر یک کلمه ببین:

SELECT s.name, e.course
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
ORDER BY s.id, e.course;
[('Sara', 'Python'), ('Sara', 'SQL'), ('Omid', 'SQL'), ('Nika', None)]

در پایتون NULL به شکل None چاپ می‌شود. RIGHT JOIN و FULL JOIN در نسخه‌های تازهٔ SQLite کار می‌کنند، ولی در نسخه‌های قدیمی نبودند. اگر خطا گرفتی، نسخه را با sqlite3.sqlite_version ببین. هر RIGHT JOIN را هم می‌شود با جابه‌جاکردن دو جدول به LEFT JOIN تبدیل کرد.

کاربرد مهم: پیدا کردن آنچه نیست

می‌خواهی بدانی کدام دانش‌آموز هیچ ثبت‌نامی ندارد. با INNER JOIN نمی‌شود، چون همان سطر حذف می‌شود. با LEFT JOIN و IS NULL می‌شود:

SELECT s.name
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
WHERE e.student_id IS NULL;

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

دام اول: شرط در جای غلط

می‌خواهی همهٔ دانش‌آموزان را ببینی و کنارشان، اگر SQL برداشته‌اند، نام درس را. طبیعی است که بنویسی:

SELECT s.name, e.course
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
WHERE e.course = 'SQL'
ORDER BY s.id;
[('Sara', 'SQL'), ('Omid', 'SQL')]

Nika ناپدید شد، بدون هیچ خطایی. دلیلش این است که WHERE بعد از JOIN اعمال می‌شود. سطر Nika مقدار NULL دارد و NULL = 'SQL' درست نیست، پس حذف می‌شود. نتیجه عملاً یک INNER JOIN است. راه درست، بردن شرط به ON است:

SELECT s.name, e.course
FROM students s
LEFT JOIN enrollments e
  ON e.student_id = s.id AND e.course = 'SQL'
ORDER BY s.id;
[('Sara', 'SQL'), ('Omid', 'SQL'), ('Nika', None)]

قاعده: در LEFT JOIN شرط روی جدول راست جایش در ON است، مگر اینکه عمداً بخواهی سطرهای بدون جفت را حذف کنی.

دام دوم: COUNT(*) بعد از LEFT JOIN

تعداد ثبت‌نام هر نفر را می‌خواهی. دو کوئری تقریباً یکسان را مقایسه کن:

SELECT s.name, COUNT(*)
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
GROUP BY s.id ORDER BY s.id;
-- [('Sara', 2), ('Omid', 1), ('Nika', 1)]

SELECT s.name, COUNT(e.course)
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
GROUP BY s.id ORDER BY s.id;
-- [('Sara', 2), ('Omid', 1), ('Nika', 0)]

COUNT(*) سطرها را می‌شمارد و Nika یک سطر با NULL دارد. COUNT(e.course) فقط مقدارهای غیر NULL را می‌شمارد، پس جواب درست 0 است.

وارسی سادهٔ هر JOIN

پیش از اعتماد به نتیجه، تعداد سطرها را بشمار و دلیل کم یا زیادشدنش را پیدا کن. اگر هیچ شرط اتصالی ننویسی، هر سطر با همهٔ سطرهای جدول دیگر جفت می‌شود: در مثال ما 3 × 4 = 12 سطر. این همان CROSS JOIN است.

اگر می‌خواهی SQL را با پرسش‌های واقعی یاد بگیری، خودآموز آوند این‌ها را در Google Colab درس می‌دهد، و خودآموز دانلودی آوند نسخهٔ دانلودی همان دوره است.

اگر می‌خواهی این موضوع را کامل یاد بگیری

خودآموز آوند

داده: SQL، مدل‌سازی و تحلیل برای تصمیم

آموزش SQL از صفر: از یک فایل CSV تا پایگاه داده‌ای که سؤال‌های واقعی را جواب می‌دهد. JOIN و تجمیع و تابع‌های پنجره‌ای، مدل‌سازی اسکیمایی که با تغییر نمی‌پوسد، کارایی‌ای که با EXPLAIN QUERY PLAN اندازه می‌گیری — و ترم آخر، تبدیل یک عدد به تصمیمی که کسی رویش کار می‌کند. همه‌چیز در Google Colab با sqlite3، بدون سرور و بدون نصب.

  • ۶ ترم
  • ۵۱ فصل
  • همهٔ فصل‌ها رایگان

برای خواندن بدون اینترنت

خودآموز دانلودی آوند

PDF، EPUB، MOBI · ۹۸۴ صفحه

۱۹۹٬۰۰۰ تومان

دیدن نسخهٔ دانلودی