I have already explain regarding Right Join in SQL Server,Self Join in SQL Server, Inner Join in SQL Server,RANK(), DENSE_RANK(), ROW_NUMBER() and Aggregate functions in sql server. Today I am going to explain regarding Right Join in Sql server.
The LEFT OUTER JOIN (left join sql server) includes all rows in the left table in the results, whether or not there is a match on the right table. Notice that in the results where there is no matching records, the row contains a null value of right table column.
Syntax :
SELECT T1.column_name(s),T2.column_name(s),
FROM table1 T1
LEFT JOIN table2 T2
ON T1.column_name=T2.column_name;
NOTE : For good coding practice always use table alias while working with joins
The LEFT OUTER JOIN includes all rows in the Employee table in the results, whether or not there is a match on the EmployeeId column in the Salary table. Notice that in the results where there is no matching EmployeeId for a salary, the row contains a null value in the Salary column.
CREATE TABLE [dbo].[Employee]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, [Address1] [varchar](200) NOT NULL, [Address2] [varchar](200) NULL, [City] [varchar](50) NOT NULL, [State] [varchar](50) NOT NULL, [Country] [varchar](50) NOT NULL, [ZipCode] [int] NULL ) ON [PRIMARY] INSERT [dbo].[Employee] ([Name], [Address1], [Address2], [City], [State], [Country], [ZipCode]) VALUES (N'John', N'Main Street', NULL, N'Greenville', N'Alabama', N'USA', 1233) INSERT [dbo].[Employee] ([Name], [Address1], [Address2], [City], [State], [Country], [ZipCode]) VALUES (N'Davis', N'100th Second Street', NULL, N'Franklin', N'Georgia', N'USA', 67612) INSERT [dbo].[Employee] ([Name], [Address1], [Address2], [City], [State], [Country], [ZipCode]) VALUES (N'Jack', N'78th Street', N'Opp St Library', N'Springfield', N'New Jersey', N'USA', 65932) INSERT [dbo].[Employee] ([Name], [Address1], [Address2], [City], [State], [Country], [ZipCode]) VALUES (N'Anmol', N'A-502 Anmol Tower', N'Central Mumbai', N'Mumbai', N'Maharastra', N'INDIA', 653231) CREATE TABLE [dbo].[Salary]( [Id] [int] IDENTITY(1,1) NOT NULL, [EmployeeId] [int] NOT NULL, [Salary] [decimal](18, 3) NULL ) ON [PRIMARY] GO INSERT [dbo].[Salary] ([EmployeeId], [Salary]) VALUES (1, CAST(18000.000 AS Decimal(18, 3))) INSERT [dbo].[Salary] ([EmployeeId], [Salary]) VALUES (3, CAST(22000.000 AS Decimal(18, 3))) INSERT [dbo].[Salary] ([EmployeeId], [Salary]) VALUES (5, CAST(16000.000 AS Decimal(18, 3))) SELECT E.ID,E.NAME,E.ADDRESS1,E.ADDRESS2, E.CITY,E.STATE,E.COUNTRY,E.ZIPCODE,S.SALARY FROM EMPLOYEE E LEFT JOIN SALARY S ON S.EMPLOYEEID = E.ID
Result :
For above running example please visit below sql fiddle link
http://sqlfiddle.com/#!3/2a15d/1