Recently at work, I was asked to give some DB training to my team. I was taken back to find out that "JOIN" SYNTAX considered it VERY COMPLEX.

The challenge now was HOW to change that perception and work with the COMMON STANDARDS, which brought me here :)

ONLY 3 TYPES OF JOIN SHOULD COVER ALL BASES.

A. JOIN
B. LEFT JOIN
C. RIGHT JOIN

1- JOIN = Get Data from TWO or MORE tables based on COMMONALITIES.
EX: select * from tbl1 JOIN tbl2 on tbl1.f1 = tbl2.f1

RESULT = Print ONLY THOSE RECORDS that are COMMON in both TABLES.

2- LEFT JOIN = Get Data from TWO or MORE tables based on COMMONALITIES, which emphasis on DATA in the LEFT TABLE.
EX: select * from tbl1 LEFT JOIN tbl2 on tbl1.f1 = tbl2.f2

RESULT = Print RECORDS common in BOTH tables based on COMMONALITIES AND remaining from tbl1 (i.e table on LEFT)

3- RIGHT JOIN = Get Data from TWO or MORE tables based on COMMONALITIES, which emphasis on DATA in the RIGHT TABLE.
EX: select * from tbl1 RIGHT JOIN tbl2 on tbl1.f1 = tbl2.f1

RESULT = Print RECORDS common in BOTH tables based on COMMONALITIES AND remaining from tbl2 (i.e table on RIGHT)

Lastly, the MESS of OUTER, INNER JOINS is nothing more than semantics, which IN MOST cases can be covered by JOIN, LEFT JOIN or RIGHT JOIN i.e

- JOIN is SYNONYMOUS with INNER JOIN
- LEFT JOIN and RIGHT JOIN can do what LEFT OUTER or RIGHT OUTER can do.