Difference between left join and right join in SQL Server

Question

I know about joins in SQL Server.

For example. There are two tables Table1, Table2.

Their table structures are the 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 the above SQL statements.

Solution 1

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.

Solution 2

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  

Solution 3

(INNER) JOIN: Returns records that have matching values in both tables.

LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table.

RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table.

FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

For example, lets suppose we have two table with following records:

Table A

id   firstname   lastname
___________________________
1     Ram         Thapa
2     sam         Koirala
3     abc         xyz
6    sruthy       abc

Table B

id2   place
_____________
1      Nepal
2      USA
3      Lumbini
5      Kathmandu

Inner Join

Note: It give the intersection of two table.

Syntax

SELECT column_name FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

Apply it in your sample table:

SELECT TableA.firstName,TableA.lastName,TableB.Place FROM TableA INNER JOIN TableB ON TableA.id = TableB.id2;

Result will be:

firstName       lastName       Place
_____________________________________
  Ram         Thapa             Nepal
  sam         Koirala            USA
  abc         xyz              Lumbini

Left Join

Note : will give all selected rows in TableA, plus any common selected rows in TableB.

SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

Apply it in your sample table

SELECT TableA.firstName,TableA.lastName,TableB.Place FROM TableA LEFT JOIN TableB ON TableA.id = TableB.id2;

Result will be:

firstName   lastName    Place
______________________________
 Ram         Thapa      Nepal
 sam         Koirala    USA
 abc         xyz        Lumbini
sruthy       abc        Null

Right Join

Note:will give all selected rows in TableB, plus any common selected rows in TableA.

Syntax:

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Apply it in your samole table:

SELECT TableA.firstName,TableA.lastName,TableB.Place FROM TableA RIGHT JOIN TableB ON TableA.id = TableB.id2;

Result will bw:

firstName   lastName     Place
______________________________
Ram         Thapa         Nepal
sam         Koirala       USA
abc         xyz           Lumbini
Null        Null          Kathmandu

Full Join

Note : It is same as union operation, it will return all selected values from both tables.

Syntax:

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Apply it in your samp[le table:

SELECT TableA.firstName,TableA.lastName,TableB.Place FROM TableA FULL JOIN TableB ON TableA.id = TableB.id2;

Result will be:

firstName   lastName    Place
______________________________
 Ram         Thapa      Nepal
 sam         Koirala    USA
 abc         xyz        Lumbini
sruthy       abc        Null
 Null         Null      Kathmandu

Some facts

For INNER joins the order doesn't matter

For (LEFT, RIGHT or FULL) OUTER joins,the order matter

Find More at w3schools

Solution 4

select fields 
from tableA --left
left join tableB --right
on tableA.key = tableB.key

The table in the from in this example tableA, is on the left side of relation.

tableA <- tableB
[left]------[right]

So if you want to take all rows from the left table (tableA), even if there are no matches in the right table (tableB), you'll use the "left join".

And if you want to take all rows from the right table (tableB), even if there are no matches in the left table (tableA), you will use the right join.

Thus, the following query is equivalent to that used above.

select fields
from tableB 
right join tableA on tableB.key = tableA.key

Solution 5

You seem to be asking, "If I can rewrite a RIGHT OUTER JOIN using LEFT OUTER JOIN syntax then why have a RIGHT OUTER JOIN syntax at all?" I think the answer to this question is, because the designers of the language didn't want to place such a restriction on users (and I think they would have been criticized if they did), which would force users to change the order of tables in the FROM clause in some circumstances when merely changing the join type.

View more solutions

Comments

[onedaywhen]

"I don't think all RDBMS support RIGHT JOIN" -- sure, not all RDBMSs support SQL. But if you are implying that some SQL products support LEFT but not RIGHT then please indicate which ones.

[Mac_Cain13]

@onedaywhen For example, SQLite 3 doesn't implement RIGHT and FULL OUTER JOIN : sqlite.org/omitted.html

[Incerteza]

sometimes left and right outer joins are completely interchangeable, correct?

[onedaywhen]

@Alex: indeed left and right outer joins are always interchangeable.

[user1857492]

So why do we need RIGHT JOIN if we can achieve any desired result with just LEFT JOIN? :P

[Ian Herve Chu Te]

@SilapAliyev That's actually a very good question. Can anyone answer? :D

[Programador Adagal]

Select * from Table1 left join Table 2 will return ALL the records of table 1 plus coincident records of Table 2. The opposite Select * from Table1 right join Table 2would return ALL records from Table 2 and coincident records of Table 1. Hopes it helps.

[ʞɔıɥʇɹɐʞ ouɐɯ]

if you use more then 2 tables , using right join can be meaningful and readable

[Markus Meskanen]

@ProgramadorAdagal This doesn't explain why we have both, you can get same results with only one or the other: SELECT * FROM Table1 LEFT JOIN Table2 and SELECT * FROM Table2 LEFT JOIN Table1

[Programador Adagal]

@MarkusMeskanen you are changing a simple sentence of 7 words. When u have an 356 lines sentence with multiple subsentences and need to change the logic of Left Right you ewill wonder changing it with only oneword...