參考：
1.SQL JOIN，你想知道的應(yīng)該都有
2.Spring Boot Data JPA Left, Right, Inner and Cross Join Examples
公司Dao層數(shù)據(jù)庫查詢使用的是JPA 。根據(jù)sql日志打印有個(gè)關(guān)聯(lián)查詢使用的是cross join,但是關(guān)聯(lián)表的結(jié)果為空集，導(dǎo)致了整個(gè)查詢的結(jié)果為空。
如果將原先的查詢使用原生sql，用left join倒是可以解決。但由于原先查詢關(guān)聯(lián)的表實(shí)在太多，貿(mào)然改變害怕引入更多bug，故甩給原先的開發(fā)同事。
借此機(jī)會(huì)回顧各種 join 的定義。
1.準(zhǔn)備
- 表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結(jié)果集：
3.CROSS JOIN
- 返回的是表1和表2的笛卡爾積結(jié)果。(當(dāng)其中一個(gè)表為空集時(shí)，返回的結(jié)果為空。)
- 如果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
結(jié)果集：
2.如果使用了條件語句：
SELECT e.*,d.name AS depart_name,d.description FROM employee e CROSS JOIN department d ON e.dept_id=d.id
結(jié)果集：
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
結(jié)果集：
2.
SELECT d.name AS depart_name,d.description,e.* FROM department d LEFT JOIN employee e ON e.dept_id=d.id結(jié)果集:
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結(jié)果集:
2.SELECT d.name AS depart_name,d.description,e.* FROM department d RIGHT JOIN employee e ON e.dept_id=d.id
結(jié)果集:
