بسیاری از پایگاه‌های داده اطلاعات را به دو جدول مختلف بر اساس رابطه بین داده‌ها تقسیم می‌کنند. حتی در چنین حالت‌هایی ممکن است گاهی اوقات لازم باشد که اطلاعات از بیش از یک جدول استخراج شوند. روش معمول برای دسترسی به داده‌ها از چند جدول SQL، ترکیب جداول با عبارت JOIN است. یک عبارت JOIN بر اساس الگوهای جبری، دو جدول مجزا را مطابق ردیف‌های موجود در آنها، با یکدیگر ترکیب می‌کند. معمولاً برای این منظور از رابطه بین جفت ستونی که از بین دو جدول، به یکدیگر ارجاع دارند، استفاده می‌شود.
در این اموزش نحوه ساخت مجموعه‌ای از کوئری‌های SQL همراه با یک عبارت JOIN نشان داده می‌شود. همچنین انواع مختلف عبارت JOIN، نحوه ترکیب داده‌ها از جداول مختلف و همین‌طور نحوه مختصر کردن عنوان ستون‌ها برای ساده‌‌نویسی عملگرهای JOIN را بررسی خواهیم کرد.
پیش‌نیازها
برای دنبال‌کردن مراحل این آموزش نیاز به کامپیوتری دارید که برخی از انواع سیستم‌های مدیریت دیتابیس منطقی (RDBMS) را بر اساس SQL داشته باشد. دستورالعمل‌ها و مثال‌های موجود در این آموزش با استفاده از محیط زیر به‌دست آمده‌اند.
- یک سرور که سیستم‌عامل اوبونتو 04 در آن اجرا باشد. همچنین از یک کاربر غیر روت با دسترسی‌های مدیریتی و یک فایروال با برنامه UFW استفاده کنید.
- MySQL در سرور نصب شده و اقدامات ایمنی لازم در مورد آن انجام شده باشد.
نکته: به خاطر داشته باشید که بسیاری از RDBMS ها از نسخه SQL منحصر به فرد خودشان استفاده می‌کنند. اگرچه بسیاری از فرمان‌های موجود در این آموزش در بسیاری از RDBMS ها کارآیی دارند، ولی قالب یا خروجی ممکن است در سیستمی به غیر از MySQL تفاوت داشته باشد.
همچنین به یک پایگاه با برخی جداول که در آنها داده‌های نمونه تمرینی وجود داشته باشد، برای استفاده از عملگرهای JOIN احتیاج خواهید داشت. در اولین بخش از این آموزش، به این موضوع خواهیم پرداخت.
اتصال به MySQL و تنظیم یک پایگاه داده نمونه
اگر سیستم پایگاه داده SQL شما بر روی یک سرور ریموت اجرا می‌شود، از طریق SSH به این سرور متصل شوید.
ssh sammy@your_server_ip
سپس ورودی سرور MySQL را باز کنید و عنوان Sammy را با نام حساب کاربری MySQL خودتان جایگزین نمایید.
mysql -u sammy -p
یک پایگاه داده با نام joinsDB ایجاد کنید.
CREATE DATABASE joinsDB;
در صورتی که ساخت پایگاه داده با موفقیت همراه باشد، با خروجی‌ای شبیه به زیر روبرو خواهید شد.
Query OK, 1 row affected (0.01 sec)
برای انتخاب پایگاه داده joinsDB، عبارت USE زیر را تایپ نمایید.
USE joinsDB;
خروجی
Database changed
پس از انتخاب joinsDB، یک سری جدول درون آن ایجاد کنید. برای مثال، در اینجا فرض کنید که در مدیریت یک شرکت، قصد دارید که اطلاعات مربوط به خط تولید، کارمندان تیم فروش و فروش کارخانه خود را در یک پایگاه داده SQL رهگیری کنید. در انی طرح، با سه جدول کار خود را شروع می‌کنید که اولین آنها، اطلاعات مربوط به محصولات را در خود ذخیره کرده است. این جدول نیاز به سه ستون خواهد داشت.
- productID: کد شناسایی مربوط به هر محصول. این ستون به عنوان کلید اولیه جدول عمل خواهد کرد. به این معنا که هر کدام از مقادیر یک شناسه منحصر به فرد برای هر ردیف خواهد بود. از آنجایی که هر کدام از مقادیر موجود در کلید اولیه می‌بایست منحصر به باشند، برای این ستون یک محدودیت UNIQUE اِعمال می‌شود.
- productName: نام هر کدام از محصولات به صورت داده varchar با حداکثر ۲۰ کاراکتر.
- price: قیمت هر کدام از محصولات به صورت داده اعشاری. هر کدام از مقادیر موجود در این ستون به حداکثر ۴ رقم و همین‌طور دو رقم اعشار محدود می‌شوند. بنابراین بازه مقادیر مورد تأیید در این ستون از -99.99 تا 99 خواهد بود.
جدول محصولات
بر این اساس، یک جدول با نام products ایجاد کنید که این سه ستون را در خود داشته باشد.
CREATE TABLE products ( productID int UNIQUE, productName varchar(20), price decimal (4,2), PRIMARY KEY (productID) );
جدول تیم فروش
دومین جدول اطلاعات مربوط به کارمند تیم فروش شرکت شما را در خود ذخیره خواهد کرد. این جدول نیاز به سه ستون خواهد داشت.
- empID: همانند ستون productID، این ستون نیز حاوی یک عدد منحصر به فرد برای هر کدام از کارمندان تیم فروش است. این کارمندان قبلاً به صورت داده int تعریف شده‌اند. به همین ترتیب، این ستون نیز دارای محدودیت UNIQUE است و به عنوان کلید اولیه جدول تیم عمل خواهد کرد.
- empName : نام هر کدام از بازاریاب‌های شرکت که به صورت داده varchar تعریف شده‌اند و حداکثر ۲۰ کاراکتر دارند.
- productSpecialty: هرکدام از اعضای تیم فروش دارای یک محصول به عنوان تخصص خودش است. البته آنها می‌توانند هر محصول تولیدی از شرکت شما را بفروشند، ولی تمرکز اصلی آنها بر روی محصولی است که متخصص آن هستند. برای مشخص کردن این موضوع در جدول، کد محصول موردنظر یا productID را در جلوی هر کدام از کارمندان وارد می‌کنید.
شاید بخواهید برای اطمینان از داشتن مقادیر شناسه محصول در ستون productSpecialty، یک محدودیت foreign key برای ارجاع ستون موردنظر در جدول products داشته باشید. محدودیت foreign key ابزاری برای تعریف رابطه بین دو جدول برا اساس داده‌های دو ستون از آنهاست. در عبارت CREATE TABLE زیر، محدودیت FOREIGN KEY مستلزم این است که هر مقداری که به ستون productSpecialty در جدول تیم فروش اضافه می‌شود، حتماً باید در ستون productID جدول محصولات وجود داشته باشد.
بر این اساس، یک جدول با نام team با سه ستون به صورت زیر ایجاد می‌کنیم.
CREATE TABLE team ( empID int UNIQUE, empName varchar(20), productSpecialty int, PRIMARY KEY (empID), FOREIGN KEY (productSpecialty) REFERENCES products (productID) );
جدول فروش
آخرین جدولی که ایجاد می‌کنید، دربرگیرنده آمار ثبت فروش شرکت شماست. این جدول دارای۴ ستون خواهد بود.
- saleID: مشابه ستون‌های productID و empID در جدوال قبلی، این ستون حاوی یک عدد شناسه منحصر به فرد برای هر کدام از فروش‌ها که به صورت داده int تعریف شده‌اند، خواهد بود. این ستون نیز محدودیت UNIQUE خواهد داشت و بنابر این، به عنوان یک کلید اولیه برای جدول فروش عمل خواهد کرد.
- quantity: تعداد واحد محصولی که به فروش رسیده‌اند و به صورت داده intتعریف شده‌اند.
- productID: عدد شناسه محصول به فروش رفته، تعریف به صورت داده int
- salesperson: عدد شناسه کارمندی که فروش را انجام داده است.
همانند ستون productSpecialty برای جدول تیم فروش، محدودیت FOREIGN KEY برای دو ستون productID و salesperson بدیهی خواهد بود. در نتیجه، شما مطمئن می‌شوید که هر کدام از مقادیری که در این ستون‌ها وارد می‌کنید، از قبل در ستون productID جدول محصولات و ستون empID در جدول تیم فروش وجود دارند.
بر این اساس یک جدول به نام sales با ۴ ستون ایجاد می‌کنیم.
CREATE TABLE sales ( saleID int UNIQUE, quantity int, productID int, salesperson int, PRIMARY KEY (saleID), FOREIGN KEY (productID) REFERENCES products (productID), FOREIGN KEY (salesperson) REFERENCES team (empID) );
بارگذاری جدول‌ها
در ادامه، جدول products را با برخی داده‌های نمونه و اجرا عملگر INSERT INTO به صورت زیر بارگذاری کنید.
INSERT INTO products VALUES (1, 'widget', 18.99), (2, 'gizmo', 14.49), (3, 'thingamajig', 39.99), (4, 'doodad', 11.50), (5, 'whatzit', 29.99);
سپس جدول team را با برخی داده‌های نمونه بارگذاری کنید.
INSERT INTO team VALUES (1, 'Florence', 1), (2, 'Mary', 4), (3, 'Diana', 3), (4, 'Betty', 2);
به همین ترتیب برای جدول sales داریم:
INSERT INTO sales VALUES (1, 7, 1, 1), (2, 10, 5, 4), (3, 8, 2, 4), (4, 1, 3, 3), (5, 5, 1, 3);
نهایتاً تصور کنید که شرکت شما تعدادی فروش را بدون واسطه هر کدام از اعضای تیم فروش انجام داده است. برای ثبت این فروش‌ها، عملیات زیر برای اضافه کردن سه ردیف به جدول فروش انجام می‌شود. این داده‌ها شامل مقدار برای ستون salesperson نخواهند بود.
INSERT INTO sales (saleID, quantity, productID) VALUES (6, 1, 5), (7, 3, 1), (8, 4, 5);
در نتیجه، برای ادامه این آموزش آماده خواهید بود و نحوه متصل کردن جداول در SQL را خواهید آموخت.
درک قالب عملیات JOIN
عبارت JOIN می‌تواند در مجموعه‌ای از دستورهای SQL، از جمله UPDATE و DELETE استفاده شود. برای به تصویر کشیدن کارآیی این عبارت در اینجا، از درخواست‌های SELECT کمک می‌گیریم.
مثال زیر قالب کلی دستور SELECT را همراه با عبارت JOIN نشان می‌دهد.
SELECT table1.column1, table2.column2 FROM table1 JOIN table2 ON search_condition;
این قالب با دستور SELECT شروع می‌شود که بازگشت به دو ستون از دو جدول مجزا را در خود دارد. به خاطر داشته ابشید که عبارت JOIN محتوای بیش از یک جدول را درنظر می‌گیرد و به صورت کامل، به ستون جدول رفرنس‌دهی می‌شود. شما می‌توانید به این طریق، در هر عملیاتی به ستون‌ها ارجاع بدهید. در نظر داشته باشید که این موضوع از نظر فنی، تنها زمانی لازم است که دو ستون در جدول‌ها دارای نام یکسان باشند. البته به این طریق و در هنگام کار با جداول مختلف، درک و خواندن عملیات JOIN راحت‌تر می‌شود.
پس از دستور SELECT، دستور FROM می‌آید. در هر کوئری، عبارت FROM جایی است که شما سری داده‌ها را برای جستجو و یافتن داده‌های موردنظرتان تعریف می‌کنید. تنها تفاوت در اینجا این است که عبارت FROM حاوی دو جدول است که با کلیدواژه JOIN از هم جدا شده‌اند. روش مفید برای نوشتن کوئری‌ها، به خاطر سپردن ستون انتخابی (SELECT) برای یافتن از (FROM) جدول موردنظر است.
در ادامه از عبارت ON استفاده می‌شود که نحوه اتصال کوئری به دو جدول را با تعریف شرط جستجو تعیین می‌کند. یک شرط جستجو شامل مجموعه‌ای از یک یا چند predicate یا عبارت است که می‌تواند ارزیابی درستی یا نادرستی یک شرط خاص و برگشت دادن جواب‌های “true”، “false” یا “unknown” را انجام دهد. می‌توانید عبارت JOIN را به عنوان ابزاری برای ترکیب ردیف‌ها در هر دو جدول درنظر بگیرید. سپس ردیفی که شرط جستجو را در عبارت ON برآورده کرده، برگشت داده می‌شود.
در یک عبارت ON، یک شرط جستجوی منطقی آن است که دو ستون مرتبط با هم را از نظر یکسان‌بودن مقادیر با یکدیرگ مقایسه کنیم. به عنوان نمونه‌ای از این داده‌های منطبق بر یکدیگر، کوئری زیر را با استفاده از داده‌هایی که در قبل ایجاد کردیم، اجرا کنید. این دستور باعث می‌شود که جداول products و team با شرط جستجوی تست مقادیر ستون‌ها productID و productSpecialty به یکدیگر متصل شوند. سپس نام هر کدام از اعضای تیم فروش، نام محصولی که در آن تخصص دارند و قیمت این محصولات نیز برگشت داده می‌شود.
SELECT team.empName, products.productName, products.price FROM products JOIN team ON products.productID = team.productSpecialty;
در اینجا سری نتایج این کوئری را می‌بینید.
+----------+-------------+-------+ | empName | productName | price | +----------+-------------+-------+ | Florence | widget | 18.99 | | Mary | doodad | 11.50 | | Diana | thingamajig | 39.99 | | Betty | gizmo | 14.49 | +----------+-------------+-------+ 4 rows in set (0.00 sec)
برای مشاهده نحوه ترکیب این جداول در SQL و رسیدن به این سری نتایج، بیایید نگاهی دقیق‌تر به این فرآیند داشته باشیم. البته گفت که چیزی که در ادامه می‌آید، دقیقاً ترکیب دو جدول در یک سیستم مدیریت پایگاه داده نیست و بیشتر برای درک عملیات JOIN آورده می‌شود.
در ابتدا، کوئری تمام سطرها و ستون‌های موجود در جدول اول عبارت FROM، یعنی products را چاپ می‌کند.
JOIN Process Example +-----------+-------------+-------+ | productID | productName | price | +-----------+-------------+-------+ | 1 | widget | 18.99 | | 2 | gizmo | 14.49 | | 3 | thingamajig | 39.99 | | 4 | doodad | 11.50 | | 5 | whatzit | 29.99 | +-----------+-------------+-------+
سپس هر کدام از این سطرها را بررسی می‌کند و آنها را با سطر متناظر در جدول team که مقدار productSpecialty با productID یکسان است، مطابقت می‌دهد.
JOIN Process Example +-----------+-------------+-------+-------+----------+------------------+ | productID | productName | price | empID | empName | productSpecialty | +-----------+-------------+-------+-------+----------+------------------+ | 1 | widget | 18.99 | 1 | Florence | 1 | | 2 | gizmo | 14.49 | 4 | Betty | 2 | | 3 | thingamajig | 39.99 | 3 | Diana | 3 | | 4 | doodad | 11.50 | 2 | Mary | 4 | | 5 | whatzit | 29.99 | | | | +-----------+-------------+-------+-------+----------+------------------+
سپس هر کدام از سطرها را که مطابقت نداشته باشد، حذف می‌کند و دوباره ستون‌ها را بر اساس ترتیب‌شان در عبارت SELECT تنظیم می‌کند. در این حالت، ستون‌هایی که مشخص نشده‌اند، حذف شده و سطرها نیز دوباره تنظیم می‌شوند. نتیجه نهایی به صورت زیر خواهد بود.
JOIN Process Example +----------+-------------+-------+ | empName | productName | price | +----------+-------------+-------+ | Florence | widget | 18.99 | | Mary | doodad | 11.50 | | Diana | thingamajig | 39.99 | | Betty | gizmo | 14.49 | +----------+-------------+-------+ 4 rows in set (0.00 sec)
استفاده از «مقادیر برابر» یکی از رایج‌ترین راه‌ها برای اتصال جداول در SQL است. در همین از برخی عملگرهای دیگر SQL مانند >، <، LIKE، NOT LIKE و یا حتی BETWEEN نیز می‌تواند در شرط جستجوی عبارت ON استفاده کرد. البته درنظر داشته باشید که استنفاده از شرط های پیچیده می‌تواند پیش‌بینی داده‌های ظاهر شده در نتیجه را با مشکل روبرو کند.
در بسیاری از موارد، می‌توانید اتصال جداول با هر مجموعه ستونی که استاندارد داده موسوم به “JOIN eligible” را در SQL داشته باشند، انجام دهید. این بدان معناست که از نظر کلی، امکان اتصال یک ستون با داده عددی با هر ستون دیگری که حاوی داده عددی باشد، وجود دارد. به همین ترتیب، امکان اتصال ستونی که دارای مقادیر کاراکتری باشد با ستون دیگر با داده‌ای از همین نوع وجود خواهد داشت. همان‌طور که قبلاً نیز اشاره شد، ستون‌هایی که برای اتصال جداول انتخاب می‌کنید، آنهایی هستند که نمایانگر ارتباط بین جداول هستند.
بسیاری از ابزارهای SQL به شما اجازه می‌دهند که با استفاده از کلیدوازه USING به جای ON، اتصلا ستون‌ها را انجام دهید. قالب کار برای چنین عملیاتی به صورت زیر خواهد بود.
SELECT table1.column1, table2.column2 FROM table1 JOIN table2 USING (related_column);
در این مثال، عبارت USING معادل ON table1.related_column = table2.related_column; است.
به دلیل اینکه هر دو جدول فروش و محصولات دارای ستونی با نام productID هستند، می‌توانید این ستون‌ها را با استفاده از کلیدواژه USING به هم متصل کنید. فرمان زیر چنین کاری انجام می‌دهد و باعث بازگشت کد فروش هر کدام از فروش‌ها، مقدار فروش محصولات، نام هر محصول فروخته شده و قیمت آن خواهد شد. همچنین سری نتایج بر اساس مقدار saleID یا کد فروش، به صورت صعودی مرتب خواهند شد.
SELECT sales.saleID, sales.quantity, products.productName, products.price FROM sales JOIN products USING (productID) ORDER BY saleID;
خروجی
+--------+----------+-------------+-------+ | saleID | quantity | productName | price | +--------+----------+-------------+-------+ | 1 | 7 | widget | 18.99 | | 2 | 10 | whatzit | 29.99 | | 3 | 8 | gizmo | 14.49 | | 4 | 1 | thingamajig | 39.99 | | 5 | 5 | widget | 18.99 | | 6 | 1 | whatzit | 29.99 | | 7 | 3 | widget | 18.99 | | 8 | 4 | whatzit | 29.99 | +--------+----------+-------------+-------+ 8 rows in set (0.00 sec)
هنگامی که جداول به یکدیگر اتصال پیدا می‌کنند، سیستم پایگاه داده ممکن است ردیف‌ها را به گونه‌ای مرتب کند که به سادگی قابل‌پیش‌بینی نباشند. بر این اساس، اضافه‌کردن یک عبارت ORDER BY به این صورت می‌تواند باعث شفافیت بیشتر سری نتایج شود.
اتصال بیش از دو جدول
گاهی پیش می‌آید که نیاز به ترکیب داده‌ها از بیشتر از ۲ جدول خواهید داشت. چنین کاری با تعبیه عبارت JOIN درون عبارت‌های دیگر JOIN میسّر خواهد بود. قالب زیر نمونه‌ای از اتصال سه جدول SQL است.
SELECT table1.column1, table2.column2, table3.column3 FROM table1 JOIN table2 ON table1.related_column = table2.related_column JOIN table3 ON table3.related_column = table1_or_2.related_column;
در این قالب نمونه، عبارت FROM با اتصال table1 و table2 شروع می‌شود. در ادامه و پس از اتصال با عبارت ON، دومین عبارت JOIN باعث ترکیب سری جدول قبلی با table3 می‌شود. به خاطر داشته باشید که جدول سوم می‌تواند به یک ستون در جدول اول یا دوم وصل شود.
برای اینکه تصوّری از این موضوع داشته باشید، فرض کنید که می‌خواهید مقدار سودی که فروش کارمندان شما ایجاد کرده‌اند را بدانید. ولی در عین حال، تنها چیزی به ان اهمیت می‌دهید، فروش‌های ثبت‌شده کارمندانی است که محصولاتی را که در آن تخصص دارند، فروخته‌اند.
برای دریافت این اطلاعات می‌توانید کوئری زیر را اجرا نمایید. این کوئری با اتصال products و sale بر اساس ستون‌های productID شروع می‌شود. سپس جدول team بر اساس مطابق هر ردیف با ستون productSpecialty به این دو متصل می‌گردد. سپس کوئری نتایج را با عبارت WHERE برای بدست آوردن نام کارمندی که فروش را انجام داده، فیلتر می‌کند. همچنین این کوئری شامل عبارت ORDER BY نیز برای ترتیب صعودی در نتایج بر اساس مقدار ستون saleID می‌شود.
SELECT sales.saleID, team.empName, products.productName, (sales.quantity * products.price) FROM products JOIN sales USING (productID) JOIN team ON team.productSpecialty = sales.productID WHERE team.empID = sales.salesperson ORDER BY sales.saleID;
به خاطر داشته باشید که در میان ستون‌های لیست شده در عبارت SELECT، مقدار موجود در ستون quantity در جدول فروش ضربدر در مقدار price در جدول محصولات می‌شود.
+--------+----------+-------------+-----------------------------------+ | saleID | empName | productName | (sales.quantity * products.price) | +--------+----------+-------------+-----------------------------------+ | 1 | Florence | widget | 132.93 | | 3 | Betty | gizmo | 115.92 | | 4 | Diana | thingamajig | 39.99 | +--------+----------+-------------+-----------------------------------+ 3 rows in set (0.00 sec)
تمام مثال‌ها تا به اینجا دارای یک نوع عبارت JOIN بوده‌اند؛ INNER JOIN. برای درک بهتر اتصال‌های INNER، OUTER و تفاوت این دو، بخش بعدی این آموزش را دنبال کنید.
تفاوت عملیات‌های اتصال Inner و Outer
اتصال‌های Inner و Outer دو نوع اصلی عبارت JOIN هستند. تفاوت این دو در داده‌هایی است که برگشت می‌دهند.عملیات‌های INNER join تنها سطرهایی را از جداول اتصال‌یافته برگشت می‌دهند که با یکدیگر مطابقت داشته باشند. این در حالی است که OUTER join ها همه سطرها، چه مطابقت داشته و چه نداشته باشند، برگشت می‌دهند.
قالب‌ها و کوئری‌های نمونه در بخش‌های قبلی همگی از عبارت INNER JOIN استفاده کرده بودند. این در حالی است که هیچکدام از آنها حاوی کلیدواژه INNER نبودند. در بسیاری از ابزارهای SQL، عبارت JOIN به صورت داخلی و inner درنظر گرفته می‌شود. مگر اینکه به صورت واضح، موضوع دیگری در کوئری مشخص شود.
کوئری‌هایی که ترکیب جدول OUTER JOIN دارند، تمام سطرهای مطابق و نامطابق را برگشت می‌دهند. این موضوع می‌تواند برای مقادیر از دست رفته و یا زمانی که بخواهیم مطابقت جزئی داشته باشیم، مفید باشد.
عملیات اتصال خارجی یا OUTER می‌تواند به سه شاخه تقسیم شود. اتصال‌های LEFT OUTER، RIGHT OUTER و FULL OUTER. اتصال‌های LEFT OUTER یا به طور خلاصه، LEFT موجب برگشت سطرهای مطابق از دو جدول و همین‌طور برگشت سطور نامطابق از «جدول سمت چپ» می‌شوند. در عملیات JOIN، منظور از «جدول سمت چپ» همیشه اولین جدولی است که بعد از کلیدواژه FROM و سمت چپ کلمه JOIN می‌آید. به همین ترتیب، «جدول سمت راست» جدول دوم و آن جدولی است که بلافاصله از عبارت JOIN ذکر می‌شود. بر این اساس، اتصال RIGHT OUTER باعث برگشت سطرهای مطابق دو جدول و سطور نامطابق «جدول سمت راست» می‌شود. همچنین در یک اتصال FULL OUTER JOIN، تمام سطرها از دو ستون، فارغ از مطابقت یا عدم‌مطابقت، برگشت داده می‌شوند.
برای درک بهتر تفاوت انواع عبارت JOIN، نمونه کوئری‌های زیر را برای جداول ساخته‌شده در مراحل قبل بررسی می‌کنیم. این کوئری‌ها مشابه هستند و تنها نوع عبارت JOIN در آنها فرق می‌کند.
در اولین مثال، از یک عبارت INNER JOIN برای ترکیب جداول sales و team بر اساس مطابقت شخص فروشنده و ستون‌های empID استفاده شده است.
SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName FROM sales JOIN team ON sales.salesperson = team.empID;
به دلیل اینکه این کوئری از یک عبارت INNER استفاده می‌کند، تنها سطرهای مطابق در هر دو جدول را برگشت خواهد داد.
+--------+----------+-------------+----------+ | saleID | quantity | salesperson | empName | +--------+----------+-------------+----------+ | 1 | 7 | 1 | Florence | | 4 | 1 | 3 | Diana | | 5 | 5 | 3 | Diana | | 2 | 10 | 4 | Betty | | 3 | 8 | 4 | Betty | +--------+----------+-------------+----------+ 5 rows in set (0.00 sec)
این نسخه از کوئری نیز دارای یک عبارت LEFT OUTER JOIN است.
SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName FROM sales LEFT OUTER JOIN team ON sales.salesperson = team.empID;
همانند کوئری قبلی، در اینجا نیز تمام مقادیر در هر دو جدول برگشت داده می‌شوند. با این وجود، سایر مقادیر نامنطبق موجود در «جدول سمت چپ» یا sales نیز در نتیجه ظاهر می‌گردند. از آنجایی که این مقادیر جدول سمت چپ دارای یک مطابق در جدول سمت راست نیستند، مقادیر برگشتی به این طریق، به صورت Null نمایش داده می‌شوند.
+--------+----------+-------------+----------+ | saleID | quantity | salesperson | empName | +--------+----------+-------------+----------+ | 1 | 7 | 1 | Florence | | 2 | 10 | 4 | Betty | | 3 | 8 | 4 | Betty | | 4 | 1 | 3 | Diana | | 5 | 5 | 3 | Diana | | 6 | 1 | NULL | NULL | | 7 | 3 | NULL | NULL | | 8 | 4 | NULL | NULL | +--------+----------+-------------+----------+ 8 rows in set (0.00 sec)
نسخه بعدی کوئری، از یک عبارت RIGHT JOIN استفاده می‌کند.
SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName FROM sales RIGHT JOIN team ON sales.salesperson = team.empID;
دقت داشته باشید که این عبارت JOIN به صورت RIGHT JOIN، و نه RIGHT OUTER JOIN نوشته شده است. همانند کلمه INNER در هنگام نوشتن یک عبارت INNER JOIN، استفاده از کلمه OUTER در هنگام نوشتن LEFT JOIN یا RIGHT JOIN ضروری نخواهد بود.
نتایج این کوئری در مقابل سری قبلی قرار می‌گیرند. جایی که سطرهای مطابق در هر دو جدول به نمایش درمی‌آیند، ولی تنها سطرهای نامطابق در «جدول سمت راست» در نتیجه ظاهر می‌شوند.
+--------+----------+-------------+----------+ | saleID | quantity | salesperson | empName | +--------+----------+-------------+----------+ | 1 | 7 | 1 | Florence | | NULL | NULL | NULL | Mary | | 4 | 1 | 3 | Diana | | 5 | 5 | 3 | Diana | | 2 | 10 | 4 | Betty | | 3 | 8 | 4 | Betty | +--------+----------+-------------+----------+ 6 rows in set (0.00 sec)
نکته: به خاطر داشته باشید که MySQL از عبارت FULL OUTER JOIN پشتیبانی نمی‌کند. برای اینکه نشان دهیم این کوئری در هنگام استفاده از عبارت FULL OUTER JOIN چه نتیجه‌ای ارائه می‌دهد، در یک پایگاه داده PostgreSQL داریم.
SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName FROM sales FULL OUTER JOIN team ON sales.salesperson = team.empID;
خروجی
saleid | quantity | salesperson | empname --------+----------+-------------+---------- 1 | 7 | 1 | Florence 2 | 10 | 4 | Betty 3 | 8 | 4 | Betty 4 | 1 | 3 | Diana 5 | 5 | 3 | Diana 6 | 1 | | 7 | 3 | | 8 | 4 | | | | | Mary (9 rows)
همان‌طور که این خروجی نشان می‌دهد، FULL JOIN تمام سطور موجود در هر دو جدول و از جمله آنهایی که مطابقت ندارند، در نتیجه ظاهر می‌شوند.
مخفف‌کردن نام جدول و ستون در عبارت JOIN
وقتی نام جداولی که می‌خواهیم آنها را به یکدیگر متصل کنیم طولانی باشد، ارجاع مناسب به ستون‌ها می‌تواند به کار پرزحمتی تبدیل شود. برای جلوگیری از این موضوع، گاهی کاربران چاره کار را در استفاده از نام‌های مخفف برای جدول یا ستون می‌بینند.
چنین کاری در SQL با اضافه کردن کلمه AS بعد از تعریف جدول در عبارت FROM صورت می‌گیرد. بعد از این کلمه می‌توانید مخفف مورد علاقه خود را وارد کنید.
SELECT t1.column1, t2.column2 FROM table1 AS t1 JOIN table2 AS t2 ON t1.related_column = t2.related_column;
در این مثال مشاهده می‌کنید که مخفف‌ها در عبارت SELECT با این که هنوز در عبارت FROM تعریف نشده‌اند، مورد استفاده قرار گرفته‌اند. چنین کاری با توجه به اینکه در کوئری‌های SQL، ترتیب اجرا از عبارت FROM شروع می‌شود، ممکن خواهد بود.
به عنوان نمونه، کوئری زیر را با اتصال جداول sales و products و مخف‌های S و P به جای این دو درنظر بگیرید.
SELECT S.saleID, S.quantity, P.productName, (P.price * S.quantity) AS revenue FROM sales AS S JOIN products AS P USING (productID);
احتمالاً متوجه شده‌اید که یک مخفف سوم به عنوان سود (revenue) تعریف کرده‌ایم که از ضرب قیمت در تعداد محصولات به‌دست آمده است. چنین چیزی تنها در عنوان ستون در سری نتایج قابل مشاهده خواهد بود. تعریف چنین مخفف‌هایی می‌تواند در نمایش بهتر نتایج کارآیی داشته باشد.
+--------+----------+-------------+---------+ | saleID | quantity | productName | revenue | +--------+----------+-------------+---------+ | 1 | 7 | widget | 132.93 | | 2 | 10 | whatzit | 299.90 | | 3 | 8 | gizmo | 115.92 | | 4 | 1 | thingamajig | 39.99 | | 5 | 5 | widget | 94.95 | | 6 | 1 | whatzit | 29.99 | | 7 | 3 | widget | 56.97 | | 8 | 4 | whatzit | 119.96 | +--------+----------+-------------+---------+ 8 rows in set (0.00 sec)
به خاطر داشته باشید که در هنگام تعریف یک مخفف، استفاده از کلمه AS اختیاری است. نمونه قبلی می‌تواند به صورت زیر نیز نوشته شود.
SELECT S.saleID, S.quantity, P.productName, (P.price * S.quantity) revenue FROM sales S JOIN products P USING (productID);
البته در نظر داشته باشید که استفاده از کلمه AS می‌تواند موجب واضح‌تر شدن هدف کوئری و خوانایی بهتر آن شود.
جمع‌بندی
با مطالعه این مطلب نسبتاً طولانی، نحوه استفاده از عبارت JOIN را برای اتصال جداول جداگانه و تبدیل آنها به یک سری نتایج کوئری را آموختید. فرمان‌هایی که در اینجا مرور شدند، تقریباً در تمام پایگاه‌های داده منطقی قابل‌استفاده هستند. با این وجود، باید درنظر داشته باشید که هر پایگاه داده SQL از کدها و ابزارهای منحصر به فرد خودش برای این منظور استفاده می‌کند.