Difference between left join and right join in SQL Server [duplicate]

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.

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  

(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         Thapa2     sam         Koirala3     abc         xyz6    sruthy       abc

Table B

id2   place_____________1      Nepal2      USA3      Lumbini5      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        Lumbinisruthy       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         Nepalsam         Koirala       USAabc         xyz           LumbiniNull        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        Lumbinisruthy       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