استفاده از Inner Join در Query های MS SQL Sever

استفاده از Inner Join

در این مقاله سعی میکنیم مروری داشته باشیم بر یکی از اجزای سازنده Query در MS SQL Server به نام Inner Join که خود از خانواده Join ها می‌باشد . مثالی که در این مقاله دنبال می‌شود علاوه بر جنبه آموزشی ، پیشنیاز مقاله ای در باره استفاده از نقش ها در شناسایی کاربران است . فرض کنیم سه table مقابل در یک بانک اطلاعاتی موجود باشد :

UserInfo : که حاوی اطلاعات کابری و دارای دو ستون به نامهای ID و Username می باشد (ستون های اطلاعاتی دیگری نیز در این table وجود دارند ، اما در این مبحث استفاده نمیشوند ) Groups : شامل دو ستون GroupID و RoleName میشود ، نام مربوط به هر نقش (Role ) در سایت در این table به یک GroupID منتصب گشته است (مثال ) Roles : دارای دو ستون UserID و GroupID است که تمامی نقش های نصبت داده شده به هر کاربر در آن ثبت شده است

UserInfo

ID Username 100 Admin 101 Iran1360 102 Hossein 103 Mahdi_V

Groups

GroupID RoleName 1 Administrator 2 CanInsert 3 CanEdit 4 CanDelete

Roles

UserID GroupId 100 1 100 2 100 3 100 4 101 2 101 3 102 2 103 2

همانطور که در جدول Roles می‌بینید هر کاربر می‌تواند یک یا تمامی نقش ها را داشته باشد مثلا کاربری با نام کاربری "Admin" که مقدار ID برابر 100 است در جدول Roles نقشهایی باتمامی GroupId های موجود را دارا میباشد (1و2و3و4) و به عبارت دبگر ابن کاربر نقشهای "AdministratorوCanInsertوCanEditوCanDelete " را دارا می‌باشد و این در حالی است که کاربر دیگری مانند Hossein فقط نقش CanInsert را دارا است .

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

برای این مقصود از Inner Join استفاده می‌کنیم .Inner Join ، پیوستن‌‌ای است که در آن مقادیری از ستونها به هم می پیوندند که توسط عملگرهای منطقی با هم مقایسه شده باشند . در استاندارد SQL-92 ، میتوانیم برای Inner Join ها از عبارات From و where استفاده کنیم که در این ستاندارد تنهای Join ای که قابلیت استفاده از عبارت where را دارد Inner Join است .

کد به ما کمک میکند با گرفتن نام کاربری از برنامه و با استفاده از جداول بالا نام تمامی نقش های منتصب به کاربر موردنظر را استخراج کنیم :

Code : CREATE PROCEDURE rolesForUser ( @Username varchar(50) ) AS SELECT G.RoleName FROM Roles R INNER JOIN Groups G ON R.GroupID = G.GroupID INNER JOIN Users U ON R.UserID = U.UserID AND U.Username = @Username

توضیح : خط اول stored procedure ای با نام rolesForUser می‌سازد ، نام کاربری از برنامه دریافت شده و در Username@ ذخیره می‌گردد در ادامه نقش‌هایی انتخاب میشوند که به ID کاربر مورد نظر منتصب اند R.UserID = U.UserID AND U.Username = @Username و فقط نام نقش‌هایی انتخاب میشوند که به این کاربر منتصب اند R.GroupID = G.GroupID .

برای درک بهتر شما از موضوع مثالی از MSDN 2005 را با هم مرور میکنم :

در مثال زیر عملگر کوچکتر مورد استفاده قرار گرفته است :

Code: USE AdventureWorks; GO SELECT DISTINCT p.ProductID, p.Name, p.ListPrice, sd.UnitPrice AS 'Selling Price' FROM Sales.SalesOrderDetail AS sd JOIN Production.Product AS p ON sd.ProductID = p.ProductID AND sd.UnitPrice < p.ListPrice WHERE p.ProductID = 718; GO

که نتیجه آن به صورت زیر خواهد بود :

ProductID Name ListPrice Selling Price ----------- --------------------------- ------------------ ------------- 718 HL Road Frame - Red, 44 1431.5000 758.0759 718 HL Road Frame - Red, 44 1431.5000 780.8182 718 HL Road Frame - Red, 44 1431.5000 858.90

شما همینطور میتوانید از عملگر نامساوی <> نیز بدین منظور بهره ببرید .