For some time I have only been knowing how to use the INNER JOIN and absolutely no clue about what LEFT/RIGHT (OUTER) JOIN does. Although, as I just read about it, I cannot see what purpose the RIGHT has?
It seems to me it's identical to a reverse LEFT JOIN
If we follow my example:
SELECT t1.* FROM table1 t1 RIGHT JOIN table2 t2 ON t2.value = t1.value
Would be identical to:
SELECT t2.* FROM table2 t2 LEFT JOIN table1 t1 ON t1.value = t2.value
Is this right, or am I missing something out?
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).
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.
Using an Incomplete ON Condition. Unwanted rows in the result set may come from incomplete ON conditions. In some cases, you need to join tables by multiple columns. In these situations, if you use only one pair of columns, it results in duplicate rows.
Solution. Select column values in a specific order within rows to make rows with duplicate sets of values identical. Then you can use SELECT DISTINCT to remove duplicates. Alternatively, retrieve rows in such a way that near-duplicates are not even selected.
Yes, you're right. From Wikipedia:
A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B. A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).
Yes this is right, you can find more information if you searched:
Difference between left join and right join in SQL Server
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