×

بررسی انواع JOIN در SQL Server

فصل یازدهم :

JOIN و تمرینات سلکت نویسی

از آنجا که نوشتن select های کارامد و موثر جزو یکی از مهمترین ویژگیهای یک متخصص اس کیو ال بشمار میرور. این قسمت را به تمرینات select نویسی اختصاص داده و سایر مفاهیم select را در متن آن توضیح میدهیم.

یکی از معرف تریم وقدیمی ترین بانک های اطلاعاتی نمونه که بسیار از متخصان دنیا کار خود را با ان شروع کرده اند دیتابیس ساده و جالب pubs می باشد.که در واقع یک دیتابیس برای نگهداری اطلاعات نویسندگان و ناشران و کتابها می باشد.
اسکریپت مربوط به ان را در محیط اس کیوال سرور اجرا نمایید تا این دیتا بیس بهمراه جداول آن ایجاد شود.
https://www.microsoft.com/en-us/download/details.aspx?id=23654

از دیتابیس pubs صرفا جهت انجام تمرینات select استفاده میشود و قوعد نامگذاری آن منطبق انچه قبلا ذکر شد نیست. لذا لطفا تنها مفاهیم را مد نظر قرار دهید .
جدول authors شامل اطلاعات نویسندگان
جدول Titles عناوین کتابها
Publishers : شرکت های انتشاراتی

1. تمامی رکورد ها و فیلد های جدول نویسندگان را لیست کنید.
SELECT * FROM authors

2. نام ، نام خانوادگی و آدرس و شماره تلفن تمامی نویسندگان را لیست کنید.
select au_fname,au_lname,address,phone from authors
دقت کنید که اس کیو ال سرور فیلد آدرس را آبی رنگ نشان داده و انرا بعنوان یکی از کلمات کلیدی خود می شناسد. اساسا از انتخاب چنین نامهایی برای فیلد ها بپرهیزید ولی در صورت وجود و یا هنگامی که در نام فیلدی کاراکتر space وجود دارد می تواننید نام فیلد را در ][ قرار دهید
select au_fname,au_lname,[address],phone from authors

3. لیست نام و نام خانوادگی نویسندگان را نمایش دهید بصورتی که ستون نام با FirstName و ستون نام خانوادگی با نام LastName نمایش داده شوند :
select au_fname as FirstName,au_lname as LastName from authors
(تعریف alias برای هر ستون)

4 . لیست عناوین کتابها بترتیب قیمت :
select title from titles order by price
(مرتب سازی پیشفرض صعودی)

5 . لیست عناوین کتابها و قیمت ها بترتیب فیلد دوم نمایش داده شد :
select title,price from titles order by 2
در لیست فیلد ها در select 2 فیلد داریم مرتب سازی را برحسب فیلد دوم انجام بده (بدون درج نام فیلد)

6. لیست کتابهایی که قیمت انها بیشتر از 20 دلار است :
select title,price from titles where price>20

7. لیست کتابهایی که قیمت انها بین 10 تا 15 دلار است
select title,price from titles where price>10 and price<15
یا
select title,price from titles where price between 10 and 15

8. لیست عنوان کتابها و ناشران انها نمایش داده شود.
خوب . مسئله اندکی پیچیده تر شد. اطلاعاتی را از دو جدول لیست کنیم . راه حل بسیار سادست . هنگامی که بخواهیم اطلاعاتی را از چندین جدول بعنوان یک نتیجه لیست کنیم از join (متصل کردن ) استفاده میشود . و این join یا اتصال بر اساس فیلد های مشترک بین دو جدول انجام می شود . جدول کلاس ها و شاگردان را بخاطر بیاورید. کلید اصلی یک رکورد کلاس را به نام ClassID در جدول student ذخیره کردیم . فیلد مشترک بین این دو جدول که باعث ارتباط انها با هم میشود همین فیلد است.که در join نیز بایستی ان فیلد ارتباط دهنده مشخص شود .حالا بوسیله join بین دو جدول ارتباط برقرار میکنیم
join بین دو یا چند جدول به چند روش انجام میشود

• Inner join

Inner join در این نوع join رکوردهایی از جداول انتخاب میشوند که فیلد ارتباط دهنده در دو جدول مقادیر یکسانی داشته باشد. فرض کنید می خواهیم لیستی از نام ، نام خانوادگی و نام کلاس هر دانش اموز را نمایش دهیم . inner join در این مثال به این معنی است که تمام کلاسهایی لیست میشوند که حتما دارای شاگرد باشند.
select ClassName,FirstName,LastName from ClassRoom
inner join Student
on ClassRoom.ClassID=Student.ClassID

در عبارت select فیلد هایی از دو جدول را داریم.
بعد از عبارت inner join نام جدول دوم . و در عبارت on فیلد ارتباط دهنده بین این دو جدول را مشخص میکنیم.
رکوردهایی از جدول classroomدر نتیجه نمایش داده می شوند که متناظر با انها شاگردی وجود داشته باشد که مقدار فیلد classid او با فیلد Classid جدول classroomبرابر باشد.
بنابرین اگر فرض کنیم کلاسی فاقد دانش آموز استد رنتایج نمایش داده نخواهد شد.
چگونه لیستی داشته باشیم که نام و نام خانوادگی هر دانش اموز به همراه نما کلاس وی نمایش داده شود و اگر کلاسی فاقد دانش اموز بود ان کلاس هم در نتایج ما دیده شود .

• Left outer join

Left outer join دراین join تمام رکورد های جدول اول (ان جدول که نام آن در عبارت from ذکر می شود .) لیست می شوند بعلاوه رکوردهایی که مقدار فیلد ارتباط دهنده بین دو جدول یکی باشد.
select ClassName,FirstName,LastName from ClassRoom
left join Student
on ClassRoom.ClassID=Student.ClassID

پس در نتایج لیست تمامی کلاس ها را خواهیم داشت اگر کلاسی دارای شاگرد نبود هم موجوداست ولی فیلد های نام و نام خانوادگی آن null است.

• Right outer join

Right outer join دراین join تمام رکورد های جدول دوم (ان جدول که نام آن در عبارت from ذکر می شود .) لیست می شوند بعلاوه رکوردهایی که مقدار فیلد ارتباط دهنده بین دو جدول یکی باشد.
select ClassName,FirstName,LastName from ClassRoom
right join Student
on ClassRoom.ClassID=Student.ClassID

پس در نتایج لیست تمامی دانش اموزان ها را خواهیم داشت اگر دانش اموزی فاقد کلاس بود نام کلاس وی null است.

• Full outer join

Full outer join دراین join تمام رکورد های جدول اول و دوم در نتایج موجود خواهند بود
select ClassName,FirstName,LastName from ClassRoom
full outer join Student
on ClassRoom.ClassID=Student.ClassID

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

• Cross join

Cross join هیچگونه مقایسه ایی در آن انجام نمی شود و فاقد عبارت on می باشد .و ضرب کارتزین رکوردهای دو جدول در نتیجه لیست خواهند شد.یعنی تعداد رکورد های جدول اول ضرب در تعداد رکورد های جدول دوم برابر است با تعداد رکورد نهایی
select ClassName,FirstName,LastName from ClassRoom
cross join Student




مفاهیم و موضوعات این فصل عبارتند از :

  • بررسی انواع JOIN در TSQL

  • INNER JOIN

  • LEFT OUTER JOIN

  • RIGHT OUTER JOIN

  • FULL OUTER JOIN

  • CROSS JOIN

vatantop
نظرات و سوالات درج سوال/ نظر