Summary of seven MySQL JOIN types

CREATE TABLE `tbl_dept` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `deptName` VARCHAR(30) DEFAULT NULL,
   `locAdd` VARCHAR(40) DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_emp` (
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(20) DEFAULT NULL,
   `deptId` VARCHAR(11) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `fk_dept_id` (`deptId`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

 SELECT < select_list >
 FROM TableA A
 INNER JOIN TableB B # TOTAL ON A.Key = B.Key 

 SELECT < select_list >
 FROM TableA A
 LEFT JOIN TableB B
 ON A.Key = B.Key 

 SELECT < select_list >
 FROM TableA A
 RIGHT JOIN TableB B
 ON A.Key = B.Key 

 SELECT < select_list >
 FROM TableA A
 LEFT JOIN TableB B
 ON A.Key = B.Key # Main table is retained when ON WHERE B.Key IS NULL # Filter data in table A 

 SELECT < select_list >
 FROM TableA A
 RIGHT JOIN TableB B
 ON A.Key = B.Key
 WHERE A.Key IS NULL 

 SELECT < select_list >
 FROM TableA A
 FULL OUTER JOIN TableB B ## FULL OUTER ON A.Key = B.Key is only supported by Oracle 

 SELECT < select_list >
 FROM TableA A
 FULL OUTER JOIN TableB B
 ON A.Key = B.Key
 WHERE A.Key IS NULL OR B.Key IS NULL