Joins : Retrieving data from two or more tables having common columns (primary-foreign key)
Types of Joins:
- Equi join / Inner Join / Natural Join (most frequently used)
- In-equi join (use for exception reports)
- Outer join (Full outer join, left outer join, right outer join)
- Cartesian join/ cross join (fastest join – as no ‘WHERE’ clause is involved)
- Self join (based on recursion)
Inner join : Matching rows from both the tables. Inner join can be equi join or inequi join.
Equi join : join condition has equality sign ( = ). Equi join can be inner, outer or self join, but as long as join condition has equality sign, it is equi join.
Natural join : Automatically joins two tables on column with same name in each table with equality condition. Natural join can be inner join or outer join.
Example-1 :
Example 2 :
| SELECT FROM left table LEFT JOIN right table | SELECT FROM left table RIGHT JOIN right table |
When we read our SELECT statement, the table listed in the FROM statement is considered the left table, while the table in the JOIN is considered the right table.
LEFT OUTER JOIN is similar to LEFT JOIN
RIGHT OUTER JOIN is similar to RIGHT JOIN
FULL OUTER JOIN is similar to OUTER JOIN
Ref:
https://www.youtube.com/watch?v=9yeOJ0ZMUYw
https://www.youtube.com/watch?v=4_Q2pnmo1tM
https://youtube.com/playlist?list=PLi01XoE8jYojRqM4qGBF1U90Ee1Ecb5tt
other ref:
Rishabh's Space 