参考:
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');
- 表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);
2.(INNER) JOIN
返回的是表1和表2所有匹配的记录。
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一样。
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
返回来自左表的所有记录和右表的匹配记录。
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
返回来自右表的所有记录和左表的匹配记录。
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
结果集: