Again, if we perform a left outer join where date = date, each row from Table 5 will join on to every matching row from Table 4. However, in this case, the join will result in 4 rows of duplicate dates in the joined DataSet (see Table 6).
Different Types of SQL JOINs (INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.
The main difference between these joins is the inclusion of non-matched rows. The LEFT JOIN includes all records from the left side and matched rows from the right table, whereas RIGHT JOIN returns all rows from the right side and unmatched rows from the left table.
The most substantial difference between the left and right outer join lies in the unmatched records that are obtained besides matched records. The left join takes all matching records and unmatched records of the left table while the right join takes all matching records and unmatched records of the right table.
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 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With