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.
This question is related to
sql
sql-server
join
left-join
right-join
The answer is
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.
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).
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.
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
(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
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
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
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 <select_list>
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
Source: Stackoverflow.com