JOINS in simple way:

             JOIN concept can be applied in database, file system and in day to day scenarios. I am putting it in a simple way so that all can understand the same.

Let’s speak about AJ our friend who is in Grade X in Public school and also plays Football.

If we want to know, how many AJ’s Football team members from Grade X then it will be INNER JOIN between GRADE_X and FOOTBALL_TEAM.

Scenario 1 & 2 in below table explains the same.

If we want to know, all the AJ’s classmates from Grade X with Football membership, then it will LEFT OUTER JOIN between GRADE_X and FOOTBALL_TEAM.

Scenario 1, 2, 3 & 4 in below table explains the same.

If we want to know, all the AJ’s Football team and will details of his class mate, then it will RIGHT OUTER JOIN between GRADE_X and FOOTBALL_TEAM.

Scenario 1, 2, 5 & 6 in below table explains the same.

If we want to know, all the AJ’s classmates from Grade X who aren’t the member of Football team, then it is LEFT OUTER JOIN between GRADE_X and FOOTBALL_TEAM and condition that Foot_Team_Member is NULL.

Scenario 3 & 4 in below table explains the same.

If we want to know, all the AJ’s Football team who are not from GRADE X, then it will RIGHT OUTER JOIN between GRADE_X and FOOTBALL_TEAM.and condition that GRADE_X_Student_Name  is NULL.

Scenario 5 & 6 in below table explains the same.

If we want to know, all the AJ’s Football as well as his classmates from GRADE X, then it will FULL OUTER JOIN between GRADE_X and FOOTBALL_TEAM.

Scenario 1, 2, 3, 4, 5 & 6 in below table explains the same

             

Scenario Number	Grade X Student Name	Football Team Member
1	AJ	AJ
2	DJ	DJ
3	KK	<NULL>
4	DK	<NULL>
5	<NULL>	GK
6	<NULL>	PK

The important point in JOINs is the KEY. We need KEY to join and in the above scenario it is Student Name. In practice, this JOIN KEY is numeric as it is more efficient. So in real time scenarios we use KEY like Student Number, Serial Number etc.

JOIN without KEY is called as Cartesian product where all rows from each table JOINS with another table. For e.g. if each table has 10 rows then result will have total 10 X 10 = 100 rows.

Joining the table with same table is called as SELF JOIN.

Type of JOIN and selecting KEY is skill as JOIN queries are expensive. These queries consume lot of CPU time and performance of system downgrades. Hence there is need of INDEXES, HINT and FILTER Criteria.

INDEXES: Increases the performance.

HINTS: Helps in selecting the right index.

FILTER Criteria:  Reduces no. of rows from comparison and hence total query time.