What's the different of inner join and right join? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

What's the different of inner join and right join?

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-04-07 : 05:12:07

I've as follow,

declare @Departments table
(DepartmentID int identity(1,1),Name varchar(30));
insert into @Departments values('D1');
insert into @Departments values('D2');
insert into @Departments values('D3');
insert into @Departments values('D4');

declare @Employees table
(EmployeeID int identity(1,1),[First Name] varchar(20),[Last Name] varchar(20),DepartmentID int);
insert into @Employees values('John','Smith',1);
insert into @Employees values('Dave','Johnson',2);
insert into @Employees values('Mark','Andrew',2);
insert into @Employees values('George','Michael',4);
insert into @Employees values('Michael','Peterson',3);

select EmployeeID,t2.DepartmentID,[First Name] + ' ' + [Last Name] as 'EmployeeFullName'
,t2.Name as DepartmentName
from @Employees t1 inner join @Departments t2
on t1.DepartmentID=t2.DepartmentID
Output
EmployeeID  | DepartmentID  | EmployeeFullName  | DepartmentName
--------------------------------------------------------------------
1	      1	              John Smith	  D1
2	      2	              Dave Johnson	  D2
3	      2	              Mark Andrew	  D2
4	      4	              George Michael	  D4
5	      3	              Michael Peterson	  D3

select EmployeeID,t2.DepartmentID,[First Name] + ' ' + [Last Name] as 'EmployeeFullName'
,t2.Name as DepartmentName
from @Employees t1 right join @Departments t2
on t1.DepartmentID=t2.DepartmentID

Output
EmployeeID  | DepartmentID  | EmployeeFullName  | DepartmentName
--------------------------------------------------------------------
1	      1	              John Smith	  D1
2	      2	              Dave Johnson	  D2
3	      2	              Mark Andrew	  D2
5	      3	              Michael Peterson	  D3
4	      4	              George Michael	  D4

What's the easy explanation between inner join and right join?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 05:21:46

Now see the difference:-


declare @Departments table
(DepartmentID int identity(1,1),Name varchar(30));
insert into @Departments values('D1');
insert into @Departments values('D2');
insert into @Departments values('D3');
insert into @Departments values('D4');
insert into @Departments values('D5');

declare @Employees table
(EmployeeID int identity(1,1),[First Name] varchar(20),[Last Name] varchar(20),DepartmentID int);
insert into @Employees values('John','Smith',1);
insert into @Employees values('Dave','Johnson',2);
insert into @Employees values('Mark','Andrew',2);
insert into @Employees values('George','Michael',4);
insert into @Employees values('Michael','Peterson',3);

select EmployeeID,t2.DepartmentID,[First Name] + ' ' + [Last Name] as 'EmployeeFullName'
,t2.Name as DepartmentName
from @Employees t1 inner join @Departments t2
on t1.DepartmentID=t2.DepartmentID

output
------------------------------------
EmployeeID	DepartmentID	EmployeeFullName	DepartmentName
1	1	John Smith	D1
2	2	Dave Johnson	D2
3	2	Mark Andrew	D2
4	4	George Michael	D4
5	3	Michael Peterson	D3


select EmployeeID,t2.DepartmentID,[First Name] + ' ' + [Last Name] as 'EmployeeFullName'
,t2.Name as DepartmentName
from @Employees t1 right join @Departments t2
on t1.DepartmentID=t2.DepartmentID

output
-------------------
EmployeeID	DepartmentID	EmployeeFullName	DepartmentName
1	1	John Smith	D1
2	2	Dave Johnson	D2
3	2	Mark Andrew	D2
5	3	Michael Peterson	D3
4	4	George Michael	D4
NULL	5	NULL	D5

the difference is right join brings all data from right table regardless of a match in left side (D5 in example) while inner join bring only matching values existing in both tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-07 : 05:24:44

This is a pretty basic question so it's quite possible I've misunderstood what you were wanting.

It sounds like you were just wanting the definition of INNER JOIN compared to RIGHT OUTER JOIN?

Inner join only returns matching rows in both tables (so if a corresponding row doesn't exist in the left or right table then it won't be returned at all)

Left join returns all rows in the left table (that aren't eliminated by the where clause) even if there is no matching row in the right table

Right join is exactly the same but returns all rows in the right table (that aren't eliminated by where clause) even if there are no matching rows in left table

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-07 : 05:25:21

See the difference


declare @Departments table
(DepartmentID int identity(1,1),Name varchar(30));
insert into @Departments values('D1');
insert into @Departments values('D2');
insert into @Departments values('D3');
insert into @Departments values('D4');

declare @Employees table
(EmployeeID int identity(1,1),[First Name] varchar(20),[Last Name] varchar(20),DepartmentID int);
insert into @Employees values('John','Smith',1);
insert into @Employees values('Dave','Johnson',2);
insert into @Employees values('Mark','Andrew',2);
insert into @Employees values('George','Michael',4);
--insert into @Employees values('Michael','Peterson',3);

select EmployeeID,t2.DepartmentID,[First Name] + ' ' + [Last Name] as 'EmployeeFullName'
,t2.Name as DepartmentName
from @Employees t1 inner join @Departments t2
on t1.DepartmentID=t2.DepartmentID

select EmployeeID,t2.DepartmentID,[First Name] + ' ' + [Last Name] as 'EmployeeFullName'
,t2.Name as DepartmentName
from @Employees t1 right join @Departments t2
on t1.DepartmentID=t2.DepartmentID

Right Join takes all the records from right hand side table and join with left hand side table
show the matching data and null for non matching data on join condition
unlike the inner join takes only records which are common to both the table on join condition
for more details see Books online...

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-04-07 : 05:26:37

Hey visakh identical answers

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 05:35:55

quote:
Originally posted by vaibhavktiwari83

Hey visakh identical answers

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

great minds think alike

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2010-04-07 : 05:42:34

tq very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources