sql連接查詢

簡述

　　　　簡單回顧並總結下不同的表連接語句有什麼異同之處以及一些概念。

　　　　建庫語句如下

 1 DROP DATABASE IF EXISTS `demo`;
 2 CREATE DATABASE `demo`;
 3 USE `demo`;
 4 /*部門表*/
 5 CREATE TABLE department (
 6     `id` INT PRIMARY KEY AUTO_INCREMENT,
 7     `name` VARCHAR(50)
 8 );
 9 INSERT INTO department VALUES(DEFAULT, '財務'),(DEFAULT, '客服'),(DEFAULT, '技術');
10 /*雇員表*/
11 CREATE TABLE employee (
12     `id` INT PRIMARY KEY AUTO_INCREMENT,
13     `name` VARCHAR(50),
14     `department_id` INT,
15     CONSTRAINT fk_employee_department FOREIGN KEY (id) REFERENCES department(id)
16 );
17 INSERT INTO employee VALUES(DEFAULT, '張三', 1),(DEFAULT, '李四', 2),(DEFAULT, '王五', 2);

 　　　　由上代碼可知，分別創建了部門表和雇員表，雇員表和部門表是多對一的關係，技術部沒有雇員。

內連接

　　　　語法：inner join ... on ... inner可以省略。

　　　　概念：內連接查詢的結果是從兩個或兩個以上的表的組合中挑選出符合連接條件的數據，如果數據無法滿足連接條件則將其忽略。在內連接查詢中，參與連接的表的地位是平等的。

　　　　例：現在要查詢員工和其所在的部門，sql語句如下

1 SELECT e.id, e.`name`, d.`name` '部門' FROM employee e JOIN department d ON e.`department_id`=d.`id`

　　　　結果如下：

　　　　可以看出，部門一列只是將有員工的部門查詢了出來，而沒有員工的技術部則沒有查詢出來。

外連接

　　　　概念：上文提到：在內連接查詢中，參與連接的表的地位是平等的。而在外連接中參與連接的表有主從之分。以主表的每行數據匹配從表的數據列，將符合連接條件的數據直接返回到結果集中；對那些不符合連接條件的列，將被填上空值後再返回到結果集中。

左外連接

　　　　語法：left  outer join ... on ... outer可以省略。

　　　　概念：左外連接查詢的結果集包括左表中的所有行，而不僅僅是連接匹配的行。若左表的某行在右表中沒有匹配行，則在相關聯的結果集中右表的所有選擇列均為空值。

　　　　例一：將上面內連接的語句改成左外連接

SELECT e.id, e.`name`, d.`name` '部門' FROM department d LEFT JOIN employee e ON e.`department_id`=d.`id`

　　　　查詢結果如下圖

　　　　可以看出，沒有員工的部門也被查詢了出來，並且員工的id和姓名列被填上了空值。

　　　　例二：查詢所有部門包含的員工數，沒有員工的部門也要被查詢出來。sql語句如下

1 SELECT d.`name`,COUNT(e.`id`) '部門人數' FROM department d LEFT JOIN employee e ON e.`department_id`=d.`id` GROUP BY e.`department_id`

　　　　結果如下

　　　　可以看出，沒有員工的技術部也被查詢了出來，統計人數為0(ps：這個0和之前自動填充的null值不衝突，因為這裡使用了聚合函數count()，結果集先填充了null,再進行count統計的)。

右外連接 

　　　　語法：right  outer join ... on ... outer可以省略。

　　　　概念：右外連接和左外連接類似，只是位置變了一下。結果集必須包含右表中的所有行，若右表中有的項在左表中沒有，則以null值填充。

　　　　例一：將上面左外連接的語句改成右外連接，sql語句如下

1 SELECT d.`name`,COUNT(e.`id`) '部門人數' FROM employee e RIGHT JOIN department d ON e.`department_id`=d.`id` GROUP BY e.`department_id`

　　　　結果如下