درس سوم- پیوند زدن (Join)

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

 

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

دستور کلی برای استفاده از پیوند به شکل زیر است:

SELECT نام ستون. نام جدول  FROM  نام جدول یکم  نوع پیوند      نام جدول دوم  جمله پیوند

این دستور با کلمه  SELECTآغاز می‌شود، در مرحله دوم باید نام ستون قید شود. بخاطر استفاده از چندین ستون از جدول‌های متفاوت برای نوشتن نام ستون باید ابتدا نام جدول را نوشت، یک نقطه و بعد نام ستون. بعد باید نام جدول ابتدایی را نوشت و نوع پیوندش را مشخص کرد قبل از اینکه نام جدول دوم نوشته شود. در آخر هم جمله پیوند که مربوط به ستون رابط بین دو جدول است آورده می‌شود. اجازه بدهید قبل از ادامه این درس مثالی را بیاوریم تا در طول درس از آن استفاده کنیم.

 فرض کنید که می‌خواهید یک تالار گفتگوی ساده طراحی کنید. این تالار دارای سه جدول است که شامل:

users: برای نگهداری اطلاعات مربوط به کاربران

forums: برای نگهداری اطلاعات مربوط به تالار

messages: برای نگهداری مطالب نوشته شده در تالار

CREATE DATABASE  forums ;

USE forums;

CREATE TABLE  users  (

user_id  mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

username  varchar(30) NOT NULL,

pass  char(40) NOT NULL,

 first_name  varchar(20) NOT NULL,

last_name  varchar(40) NOT NULL,

email  varchar(60) NOT NULL,

 PRIMARY KEY ( user_id ),

);

 

CREATE TABLE messages (

 message_id  int(10) unsigned NOT NULL AUTO_INCREMENT,

 parent_id  int(10) unsigned NOT NULL DEFAULT '0',

 forum_id  tinyint(3) unsigned NOT NULL,

 user_id` mediumint(8) unsigned NOT NULL,

 subject  varchar(100) NOT NULL,

 body  longtext NOT NULL,

 date_entered  datetime  NOT NULL,

 PRIMARY KEY ( message_id ),

 ) ENGINE=MyISAM ;

CREATE TABLE forums (

forum_id  tinyint(3) unsigned NOT NULL AUTO_INCREMENT,

name  varchar(60) NOT NULL,

PRIMARY KEY (forum_id),

) ;

حال که جدول‌ها ساخته شد می‌توانیم داده‌هایی در آنها قرار دهیم. یادتان هست؟ با کمک دستور INSERT INTO لطفا شما هم داده‌های در تمام سه جدول وارد کنید.

 

INNER JOIN
پیوند داخلی تمام داده‌های داخل دو جدول نام برده شده را که با شرایط گفته شده در دستور همخوانی دارند بر می‌‌گرداند. حال ما می‌خواهیم تمام  مطالب مربوط به PHP که در تالار نوشته شده را برگردانیم:

SELECT messages.message_id, messages.subject, forums.name FROM messages

INNER JOIN forums ON messages.forum_id =  forums.forum_id

WHERE forums.name = 'PHP' ;

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

نکته: یک مشکل این دستور طولانی‌ بودن آن است، البته یک راه برای خلاصه کردن نام جدول‌ها وجود دارد که به آن Alias یا نام مستعار می‌گویند. بدین ترتیب که می‌توانیم برای هر نام ستون یا ستون‌هایی یک نام مستعار کوچک انتخاب کنیم و از آن بجای نام اصلی استفاده کنیم.

SELECT m.message_id, m.subject, f.name FROM messages AS m   INNER JOIN  forums AS f

ON m.forum_id = f.forum_id 

WHERE f.name = 'PHP' ;

همان طور که مشاهده می‌کنید  با آوردن حروف AS بعد از نام اصلی جدول و بعد نام مستعار این نام تعریف می‌شود. توجه کنید که شما می‌توانید در ابتدا از این نام مستعار استفاده کنید و بعد آن را تعریف کنید.

اگر به خط دوم این دستور دقت کنید جمله ارتباطی این دو جدول را می‌بینید که: ON m.forum_id = f.forum_id   است. در زمان‌هایی که دو طرف این تساوی برابر یک عبارت بود. یعنی از کلید اصلی در یک جدول و همان کلید به عنوان کلید فرعی در جدول دیگر استفاده شده باشد، راه دیگری برای نوشتن جمله ارتباطی وجود دارد.

SELECT m.message_id, m.subject, f.name FROM messages AS m   INNER JOIN  forums AS f

USING  (forum_id )  //  بجای ON m.forum_id = f.forum_id 

 WHERE f.name = 'PHP' ;

حال فرض می‌کنیم که می‌خواهیم تالار‌هایی را با بیش از ۳ مطلب تازه درخواست کنیم:

SELECT f.name FROM forums AS f

INNER JOIN messages AS m

USING (forum_id)

ORDER BY m.date_entered DESC LIMIT 3;

در مثال دیگر می‌خواهیم تمام تالار‌ها و موضوعات مطرح شده را نمایش دهیم:

SELECT f.name, m.subject  FROM  forums AS  f  

INNER JOIN  messages AS m

USING (forum_id)   

ORDER BY f.name ;

این نوع پیوند تنها مواردی که با  شرایط دستور همخوانی دارند را دریافت می‌کند و این همخوانی در اصل همان یکی بودن جدول‌ها از نظر ارتباط است.

 

OUTER JOIN
پیوند خارجی بر عکس پیوند داخلی نه تنها موارد همخوانی را بر می‌گرداند بلکه تمام موارد دیگر را هم فهرست می‌کند. پیوند خارجی به سه نوع تقسیم می‌شود،LEFT OUTER JOIN, RIGHT OUTER JOIN  و FULL OUTER JOIN. البته پیوند خارجی چپ از موارد دیگر بیشتر استفاده می‌شود. برای درک بهتر پیوند خارجی بهتر آن‌ست که هر دو پیوند چپ و راست را بررسی کنیم.

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

فرض کنید می‌خواهیم تمام کاربر‌ها و شماره پیامی که ‌آنها ارسال کرده‌اند را فهرست کنیم:

SELECT u.username,  m.message_id

FROM users AS u

LEFT JOIN messages AS m

USING (user_id);

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

SELECT  f.*,  m.subject  FROM  forums AS f

LEFT JOIN  messages AS m

USING (forum_id)  ;

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

SELECT  f.*,  m.subject FROM  messages  AS m

RIGHT JOIN   forums  AS  f

USING  (forum_id)  ;

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

SELECT  f.*,  m.subject  FROM  forums AS f

RIGHT JOIN   messages AS m

USING (forum_id)  ;

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

 

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

SELECT m.message_id, m.subject,  f.name FROM users AS u

INNER JOIN messages AS m

USING (user_id)

INNER JOIN forums AS f

USING (forum_id);

همان طور که می‌بینید در این مثال ابتدا دو جدول پیام‌ها و کاربران با هم پیوند خورده و بعد نتیجه آن با جدول تالارها پیوند بعدی را می‌زند.

SELECT u.username, m.subject, f.name  FROM users AS u

LEFT JOIN  messages AS m

USING (user_id)

LEFT JOIN forums AS f

USING (forum_id);

یکی از موارد مهم در پیوند چه در دو جدول و یا بیشتر انتخاب نوع پیوند است. این انتخاب بر اساس نیاز برنامه به داده‌های ویژه‌ای که مورد توجه است بنا گذاشته می‌شود. البته بیشتر مواقع می‌توان خروجی یک دستور را از راه‌های دیگر هم بدست آورد اما نکته مهم بهینه بودن آن دستور است.

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

در درس بعد به سراغ پیدا کردن ایراد‌ها و رفع آنها می‌پردازیم.