دستور Join در Linq

همان طور که میدانید دستور Join یک دستور کاربردی و مهم در Sql است .این دستور برای ادغام دو یا چند جدول کاربرد دارد.این دستور جداول متفاوتی را با هم ترکیب می کند.

انواع مختلفی از دستور join وجود دارد که در زیر شکل آنها نشان داده شده است

در زیر توضیح کوتاهی راجع به هر کدام از دستورات join ارائه می دهیم .

InnerJoin -1 سطرهایی از جداول نشان داده خواهد شد که طبق شرط در هر دو جدول وجود داشته باشد .یعنی رابطه ای بین جداول وجود داشته باشد .به عنوان مثال در جداول مشتری و سفارش اگر دستور InnerJoin برای آنها بنویسیم مشتری هایی که سفارش نداشته باشند و یا سفارش هایی که مشتری نداشته باشند در نتیجه این join نخواهند آمد . تفاوت Join و InnerJoin در performance آنها می باشد. نتیجه ی اجرای هر دوی آنها یکسان است اما InnerJoin دارای Prformance و کارایی بالاتری می باشد و به همین دلیل توصیه می شود که از InnerJoin استفاده شود.شکل کلی دستور InnerJoin در زیر آورده شده است .

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

در InnerJoin در sql باید حداقل یک رابطه در هر دو جدول وجود داشته باشد که بعد از کلمه کلیدی On می آید.

Outer Join -2 : این نوع Join خود به سه دسته left,right,full تقسیم می شود .در خروجی این نوع join سطر هایی از جدول اصلی که سطر متناظر در جدول دیگر ندارند هم در خروجی می آیند .

Left Outer Join : تمام سطرهای جدول اولی (جدول سمت چپ join) در خروجی ظاهر می شوند. ولی سطرهایی از جدول دوم که متناظری در جدول اول ندارند در خروجی نمی آیند

Right Outer Join : تمام سطرهای جدول دومی (جدولی که در سمت راست Join قرار گرفته است) در خروجی ظاهر می شوند.

Full Outer Join : تمام سطرهای هر دو جدول در خروجی می آیند چه در جدول دیگر متناظر داشته باشند چه نداشته باشند.

Cross Join -3 در این دستور یک سطر جدول اول در کنار تمام سطرهای جدول دوم قرار می گیرد.و این کار برای تمام سطر ها تکرار می شود .

حال برای اینکه به صورت عملی دستور join را درک کنیم به ادامه مقاله توجه کنید.ابتدا در داخل پایگاه داده یک دیتابیس با سه جدول ایجاد می کنیم .اسکریپت این دیتابیس در زیر آمده است .

USE [Inventory]
GO
/****** Object:  Table [dbo].[Customer]    Script Date: 1/4/2016 11:20:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customer](
    [CustomerId] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](60) NULL,
    [Email] [varchar](100) NULL,
    [Address] [varchar](255) NULL,
    [MobileNo] [bigint] NULL,
 CONSTRAINT [PK__Customer__A4AE64D8D4F5B013] PRIMARY KEY CLUSTERED 
(
    [CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Orders]    Script Date: 1/4/2016 11:20:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
    [OrderId] [int] IDENTITY(1,1) NOT NULL,
    [OrderNumber] [int] NULL,
    [ProductId] [int] NULL,
    [CustomerId] [int] NULL,
    [Quantity] [int] NULL,
    [TotalAmount] [int] NULL,
    [OrderDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Product]    Script Date: 1/4/2016 11:20:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Product](
    [ProductId] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [varchar](50) NOT NULL,
    [UnitPrice] [int] NULL,
    [CategoryId] [int] NULL,
    [AddedDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
    [ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Customer] ON 

INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], _
[MobileNo]) VALUES (1, N'Mukesh Kumar', N'MukeshKumar@gmail.com', N'New Delhi', 9898767654)
INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], _
[MobileNo]) VALUES (2, N'Rahul Singh', N'RahulSingh@gmail.com', N'Noida', 7878787865)
INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], _
[MobileNo]) VALUES (3, N'SatishGupta', N'SatishGupta@gmail.com', N'Mumbai', 9198765432)
INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], _
[MobileNo]) VALUES (4, N'VishalSingh', N'VishalSingh', N'Patna', 7654324566)
INSERT [dbo].[Customer] ([CustomerId], [CustomerName], [Email], [Address], _
[MobileNo]) VALUES (5, N'VinayPathak', N'VinayPathak@gmail.com', N'Kanpur', 9898989765)
SET IDENTITY_INSERT [dbo].[Customer] OFF
SET IDENTITY_INSERT [dbo].[Orders] ON 

INSERT [dbo].[Orders] ([OrderId], [OrderNumber], [ProductId], [CustomerId], [Quantity], _
[TotalAmount], [OrderDate]) VALUES (1, 8001, 4, 1, 4, 120000, CAST(N'2015-11-03 22:21:13.143' AS DateTime))
INSERT [dbo].[Orders] ([OrderId], [OrderNumber], [ProductId], [CustomerId], [Quantity], _
[TotalAmount], [OrderDate]) VALUES (2, 8002, 4, 2, 1, 30000, CAST(N'2015-11-13 22:21:13.143' AS DateTime))
INSERT [dbo].[Orders] ([OrderId], [OrderNumber], [ProductId], [CustomerId], [Quantity], _
[TotalAmount], [OrderDate]) VALUES (3, 8003, 2, 3, 2, 40000, CAST(N'2015-12-15 22:21:13.143' AS DateTime))
SET IDENTITY_INSERT [dbo].[Orders] OFF
SET IDENTITY_INSERT [dbo].[Product] ON 

INSERT [dbo].[Product] ([ProductId], [ProductName], [UnitPrice], [CategoryId], _
[AddedDate]) VALUES (1, N'Samsung', 30000, 3, CAST(N'2015-05-13 22:21:13.143' AS DateTime))
INSERT [dbo].[Product] ([ProductId], [ProductName], [UnitPrice], [CategoryId], _
[AddedDate]) VALUES (2, N'Noika', 20000, 4, CAST(N'2015-05-03 22:21:13.143' AS DateTime))
INSERT [dbo].[Product] ([ProductId], [ProductName], [UnitPrice], [CategoryId], _
[AddedDate]) VALUES (3, N'Sony', 15000, 5, CAST(N'2015-01-24 22:21:13.143' AS DateTime))
INSERT [dbo].[Product] ([ProductId], [ProductName], [UnitPrice], [CategoryId], _
[AddedDate]) VALUES (4, N'Apple', 45000, 6, CAST(N'2016-01-03 22:21:13.143' AS DateTime))
SET IDENTITY_INSERT [dbo].[Product] OFF

شکل جداولی که در دیتابیس داریم به صورت زیر است

جدول order

جدول مشتری

حال به شرح مفهوم انواع join به صورت عملی می پردازیم

Inner Join

مثالی که برای Inner join می خواهیم بزنیم در زیر آورده شده است

SELECT [t1].[OrderId], [t1].[OrderNumber], [t0].[ProductName], _
	[t1].[Quantity], [t1].[TotalAmount], [t1].[OrderDate]

FROM [Product] AS [t0] INNER JOIN [Orders] AS [t1] ON ([t0].[ProductId]) = [t1].[ProductId]

معادل این دستور که در لینک (Linq)به صورت زیر است

var result=(from p in Products
            join o in Orders
            on p.ProductId equals o.ProductId
            select new{
            o.OrderId,
            o.OrderNumber,
            p.ProductName,
            o.Quantity,
            o.TotalAmount,
            o.OrderDate
            }).ToList();

هر دو کوئری های بالا نتیجه یکسانی برای ما خواهند داشت

دستور inner join برای بیش از دو جدول

SELECT [t1].[OrderId], [t1].[OrderNumber], [t0].[ProductName], _
[t1].[Quantity], [t1].[TotalAmount], [t1].[OrderDate], _
[t2].[CustomerName], [t2].[MobileNo], [t2].[Address]
FROM [Product] AS [t0]
INNER JOIN [Orders] AS [t1] ON ([t0].[ProductId]) = [t1].[ProductId]
INNER JOIN [Customer] AS [t2] ON [t1].[CustomerId] = ([t2].[CustomerId])

معادل دستور لینک عبارت بالا در زیر آورده شده است.

var result=(from p in Products
            join o in Orders
            on p.ProductId equals o.ProductId
            join c in Customers
            on o.CustomerId equals c.CustomerId
            select new{
            o.OrderId,
            o.OrderNumber,
            p.ProductName,
            o.Quantity,
            o.TotalAmount,
            o.OrderDate,
            c.CustomerName,
            c.MobileNo,
            c.Address
            }).ToList();

بعد از اجرا شکل زیر را خواهید دید

Left Outer Join
اگر بخواهیم شکل خروجی این دستور را ببینیم مطابق شکل زیر است

اگر این دستور را در سمت sql بخواهیم بنویسیم مانند زیر است

SELECT [t0].[ProductId], [t1].[OrderId] AS [OrderId], _
[t1].[OrderNumber] AS [OrderNumber], [t0].[ProductName], [t1].[Quantity] AS [Quantity], _
[t1].[TotalAmount] AS [TotalAmount], [t1].[OrderDate] AS [OrderDate]
FROM [Product] AS [t0]
LEFT OUTER JOIN [Orders] AS [t1] ON ([t0].[ProductId]) = [t1].[ProductId]

معادل لینک این دستور هم مثل زیر است

var result=(from p in Products
            join o in Orders
            on p.ProductId equals o.ProductId into temp
            from t in temp.DefaultIfEmpty()
            select new{
            p.ProductId,
            OrderId=(int?)t.OrderId,
            t.OrderNumber,            
            p.ProductName,
            Quantity=(int?)t.Quantity,
            t.TotalAmount,
            t.OrderDate
            }).ToList();

بعد از اجرای هر دو دستور خروجی زیر را خواهیم دید

Right Outer Join
این دستور از جدولی که در سمت راست join آمده همه را می آورد و از جدول دیگر آن سطرهایی که relation دارند آورده می شود .

اگر این دستور را در سمت sql بنویسیم به صورت زیر خواهد بود

SELECT [t0].[OrderId], [t1].[ProductId] AS [ProductId], [t0].[OrderNumber], _
[t1].[ProductName] AS [ProductName], [t0].[Quantity],
 [t0].[TotalAmount], [t0].[OrderDate]
FROM [Orders] AS [t0]
Right OUTER JOIN [Product] AS [t1] ON [t0].[OrderId] = [t1].[ProductId]

بعد از اجرا شکل زیر را خواهیم دید

اگر دستور Right Outer Join را با لینک بنویسیم معادل کد زیر خواهد بود

var result=(from o in Orders
            join p in Products
            on o.OrderId equals p.ProductId into temp
            from t in temp.DefaultIfEmpty()
            select new{
            OrderId=(int?)o.OrderId,
            ProductId=(int?)t.ProductId,
            o.OrderNumber,            
            t.ProductName,
            Quantity=(int?)o.Quantity,
            o.TotalAmount,
            o.OrderDate
            }).ToList();

با اجرای این دستور هم خروجی معادل کوئری sql خواهیم گرفت

Cross Join
برای این دستور خروجی ایی که به دست خواهیم آورد حاصل قرار دادن سطرهای جدول اول در کنار تمام سطرهای جدول دوم خواهد بود

کوئری زیر دستور Cross Join را در sql نمایش می دهد .

SELECT [t1].[OrderId], [t1].[OrderNumber], [t0].[ProductName], _
[t1].[Quantity], [t1].[TotalAmount], [t1].[OrderDate]
FROM [Product] AS [t0], [Orders] AS [t1]
ORDER BY [t1].[OrderId]

معادل کوئری بالا در لینک به صورت زیر است

var result=(from p in Products
            from o in Orders 
            orderby o.OrderId
            select new{
            o.OrderId,
            o.OrderNumber,
            p.ProductName,
            o.Quantity,
            o.TotalAmount,
            o.OrderDate,           
            }).ToList();

اگر کوئری های بالا را اجرا کنیم همان طور که می بینید تعداد رکوردهای ما بسیار زیاد شده است .