MYSQL JOIN 的各种用法

参考：
1.SQL JOIN，你想知道的应该都有
2.Spring Boot Data JPA Left, Right, Inner and Cross Join Examples

公司Dao层数据库查询使用的是JPA 。根据sql日志打印有个关联查询使用的是cross join,但是关联表的结果为空集，导致了整个查询的结果为空。
如果将原先的查询使用原生sql，用left join倒是可以解决。但由于原先查询关联的表实在太多，贸然改变害怕引入更多bug，故甩给原先的开发同事。
借此机会回顾各种 join 的定义。

1.准备

• 表department

CREATE TABLE IF NOT EXISTS `department` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `department` (`id`, `name`, `description`) VALUES
    (1, 'IT', 'Information Technology'),
    (2, 'TelComm', 'Telecommunication'),
    (3, 'Ins', 'Insurance'),
    (4, 'HR', 'Human Resources');

department

• 表employee

CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dept_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `employee` (`id`, `name`, `email`, `address`, `dept_id`) VALUES
    (1, 'Soumitra', 'soumitra@gmail.com', NULL, 1),
    (2, 'Suman', 'suman@gmail.com', NULL, 2),
    (3, 'Avisek', 'avisek@gmail.com', NULL, 3);

employee

2.(INNER) JOIN

返回的是表1和表2所有匹配的记录。

(INNER) JOIN
SQL语句：
SELECT e.*,d.name AS depart_name,d.description FROM employee e INNER JOIN department d ON e.dept_id=d.id
结果集：

3.CROSS JOIN

• 返回的是表1和表2的笛卡尔积结果。(当其中一个表为空集时，返回的结果为空。)
• 如果WHERE子句与CROSS JOIN一起使用，它的作用就像inner join一样。
  CROSS JOIN
  SQL语句：
  1.SELECT e.*,d.name AS depart_name,d.description FROM employee e CROSS JOIN department d
  结果集：
  
  2.如果使用了条件语句：
  SELECT e.*,d.name AS depart_name,d.description FROM employee e CROSS JOIN department d ON e.dept_id=d.id
  结果集：
  

4.LEFT (OUTER) JOIN

返回来自左表的所有记录和右表的匹配记录。

LEFT (OUTER) JOIN

SQL语句：
1.SELECT e.*,d.name AS depart_name,d.description FROM employee e LEFT JOIN department d ON e.dept_id=d.id
结果集：

2.SELECT d.name AS depart_name,d.description,e.* FROM department d LEFT JOIN employee e ON e.dept_id=d.id
结果集:

5.RIGHT (OUTER) JOIN

返回来自右表的所有记录和左表的匹配记录。

RIGHT (OUTER) JOIN
SQL语句：
1.SELECT e.*,d.name AS depart_name,d.description FROM employee e RIGHT JOIN department d ON e.dept_id=d.id
结果集:

2.SELECT d.name AS depart_name,d.description,e.* FROM department d RIGHT JOIN employee e ON e.dept_id=d.id
结果集: