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

User · Answer

select   from Table1 left join Table2 on Table1 id   Table2 id In the first query Left join compares left-sided table table1 to right-sided table table2  In Which all the properties of table1 will be shown  whereas in table2 only those properties will be shown in which condition get true  select   from Table2 right join Table1 on Table1 id   Table2 id In the first query Right join compares right-sided table table1 to left-sided table table2  In Which all the properties of table1 will be shown  whereas in table2 only those properties will be shown in which condition get true  Both queries will give the same result because the order of table declaration in query are different like you are declaring table1 and table2 in left and right respectively in first left join query  and also declaring table1 and table2 in right and left respectively in second right join query  This is the reason why you are getting the same result in both queries  So if you want different result then execute this two queries respectively  select   from Table1 left join Table2 on Table1 id   Table2 id select   from Table1 right join Table2 on Table1 id   Table2 id

User · Answer

I feel we may require AND condition in where clause of last figure of Outer Excluding JOIN so that we get the desired result of A Union B Minus A Interaction B  I feel query needs to be updated to   SELECT  lt select list gt  FROM Table A A FULL OUTER JOIN Table B B ON A Key   B Key WHERE A Key IS NULL AND B Key IS NULL   If we use OR   then we will get all the results of A Union B

User · Answer

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

User · Answer

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

User · Answer

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  gt  Table2 Rows Count Then         Left Join    Else         Right Join    End If

User · Answer

Select   from Table1 t1 Left Join Table2 t2 on t1 id t2 id By definition  Left Join selects all columns mentioned with the  select  keyword from Table 1 and the columns from Table 2 which matches the criteria after the  on  keyword   Similarly By definition  Right Join selects all columns mentioned with the  select  keyword from Table 2 and the columns from Table 1 which matches the criteria after the  on  keyword   Referring to your question  id s in both the tables are compared with all the columns needed to be thrown in the output  So  ids 1 and 2 are common in the both the tables and as a result in the result you will have four columns with id and name columns from first and second tables in order    select   from Table1   left join Table2 on Table1 id   Table2 id  The above expression it takes all the records  rows  from table 1 and columns  with matching id s from table 1 and table 2  from table 2   select   from Table2   right join Table1 on Table1 id   Table2 id    Similarly from the above expression it takes all the records  rows  from table 1 and columns  with matching id s from table 1 and table 2  from table 2   remember  this is a right join so all the columns from table2 and not from table1 will be considered

User · Answer

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

User · Answer

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  lt - 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

User · Answer

Your two statements are equivalent   Most people only use LEFT JOIN since it seems more intuitive  and it s universal syntax - I don t think all RDBMS support RIGHT JOIN

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

Examples related to sql

Examples related to sql-server

Examples related to join

Examples related to left-join

Examples related to right-join