استفاده از JOIN و ساخت پرس و جوی ترکیبی در MySQL

ساخت جداول فرضی با دستور CREATE

برای درک بهتر نحوه عملکرد دستور JOIN پیش از هر چیز لازم است که چند جدول و ستون فرضی با مقادیری از پیش تعریف شده بسازیم که در زیر پرس و جوی آن را ملاحظه می کنید.
نکته: پیش از اجرای کدهای زیر باید از طریق برنامه phpMyAdmin دیتابیسی با نام فرضی test بسازید، دقت کنید که Collation دیتابیس بر روی utf8_persian_ci تنظیم شده باشد.

<?php
$con = mysql_connect("localhost","root","");
if (!$con){
    die('اتصال برقرار نشد: ' . mysql_error());
}
mysql_select_db("test", $con);
mysql_query("SET NAMES 'utf8'");

mysql_query("CREATE TABLE tbl_1(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
name VARCHAR(255), 
skill VARCHAR(255)) ENGINE=MyISAM ")
or die(mysql_error());

mysql_query("ALTER TABLE tbl_1
DEFAULT CHARACTER SET utf8
COLLATE utf8_persian_ci;")
or die(mysql_error());

mysql_query("CREATE TABLE tbl_2(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
language VARCHAR(255), 
count VARCHAR(255)) ENGINE=MyISAM ")
or die(mysql_error());

mysql_query("ALTER TABLE tbl_2
DEFAULT CHARACTER SET utf8
COLLATE utf8_persian_ci;")
or die(mysql_error());

echo "جدول با موفقیت ساخته شد!<br>";

//اطلاعات اولیه به صورت آرایه
$name_skill = array('علی' => 'HTML', 'ساسان' => 'PHP', 'پدرام' => 'Ajax');
$language_count = array('HTML' => 9, 'PHP' => 3, 'Ajax' => 6);

//ذخیره در دیتابیس
foreach($name_skill as $key => $value){
    mysql_query("INSERT INTO tbl_1 (name, skill) VALUES('$key', '$value')")
    or die(mysql_error()); 
}
foreach($language_count as $key => $value){
    mysql_query("INSERT INTO tbl_2 (language, count) VALUES('$key', '$value')")
    or die(mysql_error()); 
}

echo "اطلاعات با موفقیت افزوده شد!<br>";

mysql_close($con);
?>

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

id     name     skill
1     علی       HTML
2     ساسان     PHP
3     پدرام     Ajax

در جدول دوم عنوان هر زبان برنامه نویسی و به فرض تعداد برنامه نویس موجود و مسلط به آن زبان درج شده.

id     language      count
1       HTML           9
2       PHP            3
3       Ajax           6

همان طور که ملاحظه می کنید بین دو جدول در ستون های skill و language ارتباطی متقابل وجود دارد (هر دو ستون عناوین مربوط به زبان های برنامه نویسی را در خود دارند)، بنابراین به فرض اگر بخواهیم مسابقه ای بین افراد هم مهارت برگزار کنیم و نیاز به محاسبه تعداد رقبای اسامی موجود در جدول اول باشد، به ناچار باید در پرس و جوی خود هم از اصلاعات جدول اول (اسامی و مهارت آنها) و هم از اطلاعات جدول دوم (مهارت و تعداد شرکت کننده) استفاده کنیم، هرچند این کار با نوشتن دو پرس و جو نیز امکانپذیر است، اما حرفه ای تر آن است که به کمک JOIN و مشتقات آن (INNER JOIN، LEFT JOIN، RIGHT JOIN، OUTER JOIN) یک پرس و جوی ترکیبی نوشته و به هدف خود برسیم، بدین ترتیب می توان نوشت:

$resutl = mysql_query("SELECT tbl_1.name, tbl_2.count FROM tbl_1 INNER JOIN tbl_2 ON tbl_1.skill = tbl_2.language")
or die(mysql_error());
while($row = mysql_fetch_array($resutl)){
    echo $row['name']. ' : ' .$row['count']. '<br>';    
}

که خروجی دستور بالا به صورت نمونه زیر خواهد بود.

علی : 9
ساسان : 3
پدرام : 6

نکته: برای تعریف شرط در هنگام استفاده از دستور JOIN (و مشتقات آن)، از عبارت ON استفاده می شود.
برای آشنایی بیشتر با JOIN و مشتقات آن، در ادامه به صورت موردی این دستور را بررسی می کنیم.

حالت INNER JOIN

حالت اول از مشتقات دستور JOIN به صورت INNER JOIN می باشد، در این حالت می توان به مواردی مشترک از هر دو جدول دسترسی داشت، نتیجه پرس و جو شامل برخی موارد از جدول اول به همراه برخی موارد دیگر از جدول دوم (بر اساس شرط تعیین شده) خواهد بود، مثال:

SELECT tbl_1.skill, tbl_2.count FROM tbl_1 INNER JOIN tbl_2 ON tbl_1.skill = tbl_2.language

خروجی این حالت به صورت زیر خواهد بود.

skill     count
HTML       9
PHP        3
Ajax       6

ملاحظه می کنید که با اجرای پرس و جوی بالا ردیف های زیر از جدول اول:

HTML
PHP
Ajax

و ردیف های زیر از جدول دوم به عنوان خروجی برگردانده می شوند:

9
3
6

حالت LEFT JOIN

نوع دیگر از مشتقات دستور JOIN به صورت LEFT JOIN است که در این حالت تمام ردیف های جدول اول صرف نظر از شرط تعیین شده به همراه ردیف های جدول دوم (بر اساس شرط تعیین شده) به عنوان خروجی برگردانده می شوند، مثال:

SELECT tbl_1.skill, tbl_2.count FROM tbl_1 LEFT JOIN tbl_2 ON tbl_1.id = tbl_2.count

خروجی پرس و جوی بالا به صورت زیر خواهد بود.

skill     count
HTML      NULL
PHP       NULL
Ajax       3

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

حالت RIGHT JOIN

مشابه حالت LEFT JOIN، حالت RIGHT JOIN است که در این صورت ردیف ها از جدول دوم صرف نظر از شرط تعیین شده انتخاب و ردیف های جدول اول بر اساس شرط انتخاب می شوند، مثال:

SELECT tbl_1.skill, tbl_2.count FROM tbl_1 RIGHT JOIN tbl_2 ON tbl_1.id = tbl_2.count

خروجی پرس و جوی بالا به صورت زیر خواهد بود.

skill     count
NULL        9
Ajax        3
NULL        6

حالت OUTER JOIN

حالت آخر از مشتقات JOIN به صورت OUTER JOIN یا FULL JOIN است که در این صورت می توان دو پرس و جوی مبتنی بر JOIN را با عبارت کلیدی UNION (اجتماع) به هم مرتبط نمود، مثال:

SELECT tbl_1.skill, tbl_2.count FROM tbl_1 LEFT JOIN tbl_2 ON tbl_1.id = tbl_2.count UNION SELECT tbl_1.skill, tbl_2.count FROM tbl_1 RIGHT JOIN tbl_2 ON tbl_1.id = tbl_2.count

نتیجه پرس و جوی بالا به صورت زیر خواهد بود.

skill     count
HTML      NULL
PHP       NULL
Ajax       3  
NULL       9
NULL       6