Difference between left join and right join in SQL Server
I know about joins in SQL Server.
For example. there is two tables Table1, Table2.
There table structure are following.
create table Table1 (id int, Name varchar (10))
create table Table2 (id int, Name varchar (10))
Table1 Data as follows:
Id Name
-------------
1 A
2 B
Table2 Data as follows:
Id Name
-------------
1 A
2 B
3 C
If I execute both below mentioned SQL statements, both outputs will be the same
select *
from Table1
left join Table2 on Table1.id = Table2.id
select *
from Table2
right join Table1 on Table1.id = Table2.id
Please explain the difference between left and right join in above sql statements.
Select * from Table1 left join Table2 ...
and
Select * from Table2 right join Table1 ...
are indeed completely interchangeable. Try however Table2 left join Table1 (or its identical pair, Table1 right join Table2 ) to see a difference. This query should give you more rows, since Table2 contains a row with an id which is not present in Table1.
Codeproject has this image which explains the simple basics of SQL joins, taken from: http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx 
Table from which you are taking data is 'LEFT'.
Table you are joining is 'RIGHT'.
LEFT JOIN: Take all items from left table AND (only) matching items from right table.
RIGHT JOIN: Take all items from right table AND (only) matching items from left table.
So:
Select * from Table1 left join Table2 on Table1.id = Table2.id
gives:
Id Name
-------------
1 A
2 B
but:
Select * from Table1 right join Table2 on Table1.id = Table2.id
gives:
Id Name
-------------
1 A
2 B
3 C
you were right joining table with less rows on table with more rows
AND
again, left joining table with less rows on table with more rows
Try:
If Table1.Rows.Count > Table2.Rows.Count Then
' Left Join
Else
' Right Join
End If
上一篇: SQL离开连接与FROM行上的多个表？