Problem
SQL Server joins are something I’m familiar with.
As an example. Table 1 and Table 2 are the two tables.
The following are their table structures.
create table Table1 (id int, Name varchar (10))
create table Table2 (id int, Name varchar (10))
The data in Table 1 is as follows:
Id Name
-------------
1 A
2 B
Table 2 contains the following information:
Id Name
-------------
1 A
2 B
3 C
Both outcomes will be the same if I perform both SQL commands below.
select *
from Table1
left join Table2 on Table1.id = Table2.id
select *
from Table2
right join Table1 on Table1.id = Table2.id
Explain the difference between the left and right joins in the SQL queries above.
Asked by Pankaj Agarwal
Solution #1
Select * from Table1 left join Table2 ...
and
Select * from Table2 right join Table1 ...
are, in fact, fully interchangeable. To see if there is a difference, try Table2 left join Table1 (or its identical counterpart, Table1 right join Table2). Because Table2 contains an entry with an id that is not present in Table1, this query should return additional rows.
Answered by Péter Török
Solution #2
‘LEFT’ is the table from which you are extracting data. The table you’re joining is on the ‘RIGHT’ side of the screen. LEFT JOIN: Take everything from the left table AND (just) the items that match from the right table. RIGHT JOIN: Take everything from the right table AND (just) the items that match from the 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 linking a table with fewer rows on it with a table with more rows, and then left joining a table with fewer rows on it with a table with more rows. Try:
If Table1.Rows.Count > Table2.Rows.Count Then
' Left Join
Else
' Right Join
End If
Answered by vbole
Solution #3
(INNER) JOIN: Returns records from both tables that have the same values.
LEFT (OUTER) JOIN: Returns all records from the left table as well as the records that match from the right table.
RIGHT (OUTER) JOIN: Returns all records from the right table, as well as the records that match those in the left table.
When there is a match in either the left or right table, the FULL (OUTER) JOIN returns all records.
Let’s say we have two tables, each having the following records:
id firstname lastname
___________________________
1 Ram Thapa
2 sam Koirala
3 abc xyz
6 sruthy abc
id2 place
_____________
1 Nepal
2 USA
3 Lumbini
5 Kathmandu
Inner Join
Syntax
SELECT column_name FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
Use the following formula 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
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
It should be used 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
Syntax:
SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Use it in your samole table as follows:
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
Syntax:
SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
Use it in your sample table as follows:
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
The order of INNER joins is irrelevant.
The sequence matters for (LEFT, RIGHT, or FULL) OUTER joins.
More information can be found at w3schools.com.
Answered by Sushank Pokharel
Solution #4
select fields
from tableA --left
left join tableB --right
on tableA.key = tableB.key
The table in the from, tableA, is on the left side of the relation in this case.
tableA <- tableB
[left]------[right]
You’ll use the “left join” if you wish to take all rows from the left table (tableA), even if there are no matches in the right table (tableB).
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.
As a result, the following query is equivalent to the one used previously.
select fields
from tableB
right join tableA on tableB.key = tableA.key
Answered by Klevlon
Solution #5
“If I can rewrite a RIGHT OUTER JOIN using LEFT OUTER JOIN syntax, then why use a RIGHT OUTER JOIN syntax at all?” you appear to be asking. Because the language’s designers didn’t want to impose such a restriction on users (and I believe they would have been chastised if they did), which would force users to change the order of tables in the FROM clause in some circumstances when simply changing the join type, I believe the answer to this question is yes.
Answered by onedaywhen
Post is based on https://stackoverflow.com/questions/4715677/difference-between-left-join-and-right-join-in-sql-server