Exploring the Depths of SQL and LINQ Joining Fundamentals

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

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 Query in C# 

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 more than two tables

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();

Linq Query 

Outer Join

Left Out Join

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