MySQL教程（四） SQL串連查詢

標籤：student   charset   primary   笛卡爾積   int   exists   name   union   arch   

更多情況下，我們查詢的資料來源於多張表，所有有必要瞭解一下MySQL中的串連查詢。

SQL中將串連查詢分成四類：交叉串連，內串連，外串連和自然串連。

資料準備

student表

-- ------------------------------ Table structure for `student`-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(10) DEFAULT NULL,  `age` tinyint(4) DEFAULT NULL,  `classId` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ------------------------------ Records of student-- ----------------------------INSERT INTO `student` VALUES (‘1‘, ‘s1‘, ‘20‘, ‘1‘);INSERT INTO `student` VALUES (‘2‘, ‘s2‘, ‘22‘, ‘1‘);INSERT INTO `student` VALUES (‘3‘, ‘s3‘, ‘22‘, ‘2‘);INSERT INTO `student` VALUES (‘4‘, ‘s4‘, ‘22‘, null);

class表

-- ------------------------------ Table structure for `class`-- ----------------------------DROP TABLE IF EXISTS `class`;CREATE TABLE `class` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `cname` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;-- ------------------------------ Records of class-- ----------------------------INSERT INTO `class` VALUES (‘1‘, ‘一班‘);INSERT INTO `class` VALUES (‘2‘, ‘二班‘);INSERT INTO `class` VALUES (‘3‘, ‘‘);

score表

-- ------------------------------ Table structure for `score`-- ----------------------------DROP TABLE IF EXISTS `score`;CREATE TABLE `score` (  `id` int(11) DEFAULT NULL,  `name` varchar(10) DEFAULT NULL,  `score` decimal(4,1) DEFAULT NULL,  `studentId` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of score-- ----------------------------INSERT INTO `score` VALUES (‘1‘, ‘語文‘, ‘90.0‘, ‘1‘);INSERT INTO `score` VALUES (‘2‘, ‘數學‘, ‘95.0‘, ‘1‘);INSERT INTO `score` VALUES (‘3‘, ‘語文‘, ‘92.0‘, ‘2‘);INSERT INTO `score` VALUES (‘4‘, ‘數學‘, ‘88.0‘, ‘2‘);INSERT INTO `score` VALUES (‘5‘, ‘語文‘, ‘96.0‘, ‘3‘);INSERT INTO `score` VALUES (‘6‘, ‘數學‘, null, ‘3‘);

交叉串連

交叉串連(CROSS JOIN)是用左表中的每一行與右表中的每一行進行串連，不能使用ON關鍵字。所得到的結果將是這兩個表中各行資料的所有組合，即這兩個表所有資料的笛卡爾積。如果A表有4條記錄，B表有3條，則結果有4*3=12條記錄。

mysql> SELECT * FROM student CROSS JOIN class;+----+------+-----+---------+----+-------+| id | name | age | classId | id | cname |+----+------+-----+---------+----+-------+|  1 | s1   |  20 |       1 |  1 | 一班  ||  1 | s1   |  20 |       1 |  2 | 二班  ||  1 | s1   |  20 |       1 |  3 |       ||  2 | s2   |  22 |       1 |  1 | 一班  ||  2 | s2   |  22 |       1 |  2 | 二班  ||  2 | s2   |  22 |       1 |  3 |       ||  3 | s3   |  22 |       2 |  1 | 一班  ||  3 | s3   |  22 |       2 |  2 | 二班  ||  3 | s3   |  22 |       2 |  3 |       ||  4 | s4   |  22 | NULL    |  1 | 一班  ||  4 | s4   |  22 | NULL    |  2 | 二班  ||  4 | s4   |  22 | NULL    |  3 |       |+----+------+-----+---------+----+-------+12 rows in set

如果給交叉串連加上WHERE關鍵字，此時將返回合格結果集，這時候與內串連的執行結果一樣。

mysql> SELECT * FROM student CROSS JOIN class WHERE student.classId = class.id;+----+------+-----+---------+----+-------+| id | name | age | classId | id | cname |+----+------+-----+---------+----+-------+|  1 | s1   |  20 |       1 |  1 | 一班  ||  2 | s2   |  22 |       1 |  1 | 一班  ||  3 | s3   |  22 |       2 |  2 | 二班  |+----+------+-----+---------+----+-------+3 rows in set

內串連

內串連(INNER JOIN)是用左表中的每一行與右表中的所有記錄進行匹配，查詢的結果為兩個表經過ON條件過濾後的笛卡爾積

mysql> SELECT * FROM student INNER JOIN class ON student.classId = class.id; -- 推薦寫法，INNER可寫可不寫+----+------+-----+---------+----+-------+| id | name | age | classId | id | cname |+----+------+-----+---------+----+-------+|  1 | s1   |  20 |       1 |  1 | 一班  ||  2 | s2   |  22 |       1 |  1 | 一班  ||  3 | s3   |  22 |       2 |  2 | 二班  |+----+------+-----+---------+----+-------+3 rows in set

等價於

mysql> SELECT * FROM student, class WHERE student.classId = class.id;+----+------+-----+---------+----+------+| id | name | age | classId | id | name |+----+------+-----+---------+----+------+|  1 | s1   |  20 |       1 |  1 | 一班 ||  2 | s2   |  22 |       1 |  1 | 一班 ||  3 | s3   |  22 |       2 |  2 | 二班 |+----+------+-----+---------+----+------+3 rows in set

可以串連多張表

mysql> SELECT * FROM student    -> JOIN class ON student.classId = class.id    -> JOIN score ON student.id = score.studentId;+----+------+-----+---------+----+-------+----+------+-------+-----------+| id | name | age | classId | id | cname | id | name | score | studentId |+----+------+-----+---------+----+-------+----+------+-------+-----------+|  1 | s1   |  20 |       1 |  1 | 一班  |  1 | 語文 | 90    |         1 ||  1 | s1   |  20 |       1 |  1 | 一班  |  2 | 數學 | 95    |         1 ||  2 | s2   |  22 |       1 |  1 | 一班  |  3 | 語文 | 92    |         2 ||  2 | s2   |  22 |       1 |  1 | 一班  |  4 | 數學 | 88    |         2 ||  3 | s3   |  22 |       2 |  2 | 二班  |  5 | 語文 | 96    |         3 ||  3 | s3   |  22 |       2 |  2 | 二班  |  6 | 數學 | NULL  |         3 |+----+------+-----+---------+----+-------+----+------+-------+-----------+6 rows in set

外串連 左外串連

左外串連包含LEFT JOIN左表所有行，如果左表中某行在右表沒有匹配，則結果中對應行右表的部分全部為空白(NULL)。

mysql> SELECT * FROM student LEFT JOIN class ON student.classId = class.id; -- 或者LEFT OUTER JOIN+----+------+-----+---------+------+-------+| id | name | age | classId | id   | cname |+----+------+-----+---------+------+-------+|  1 | s1   |  20 |       1 |    1 | 一班  ||  2 | s2   |  22 |       1 |    1 | 一班  ||  3 | s3   |  22 |       2 |    2 | 二班  ||  4 | s4   |  22 | NULL    | NULL | NULL  |+----+------+-----+---------+------+-------+4 rows in set

右外串連

右外串連包含RIGHT JOIN左表所有行，如果右表中某行在左表沒有匹配，則結果中對應行左表的部分全部為空白(NULL)。

mysql> SELECT * FROM student RIGHT JOIN class ON student.classId = class.id; -- 或者RIGHT OUTER JOIN+------+------+------+---------+----+-------+| id   | name | age  | classId | id | cname |+------+------+------+---------+----+-------+|    1 | s1   |   20 |       1 |  1 | 一班  ||    2 | s2   |   22 |       1 |  1 | 一班  ||    3 | s3   |   22 |       2 |  2 | 二班  || NULL | NULL | NULL | NULL    |  3 |       |+------+------+------+---------+----+-------+4 rows in set

全外串連

如果左表中某行在右表沒有匹配，則結果中對應行右表的部分全部為空白(NULL)，如果右表中某行在左表沒有匹配，則結果中對應行左表的部分全部為空白(NULL)。MySQL不支援FULL JOIN，但是我們可以對左串連和右串連的結果做UNION操作來實現

mysql> SELECT * FROM student LEFT JOIN class ON student.classId = class.id    -> UNION    -> SELECT * FROM student RIGHT JOIN class ON student.classId = class.id;+------+------+------+---------+------+-------+| id   | name | age  | classId | id   | cname |+------+------+------+---------+------+-------+|    1 | s1   |   20 |       1 |    1 | 一班  ||    2 | s2   |   22 |       1 |    1 | 一班  ||    3 | s3   |   22 |       2 |    2 | 二班  ||    4 | s4   |   22 | NULL    | NULL | NULL  || NULL | NULL | NULL | NULL    |    3 |       |+------+------+------+---------+------+-------+5 rows in set

自然串連

自然串連無需指定串連列，SQL會檢查兩個表中是否有相同名稱的列，且相同的列名稱只能有一個，自然串連基本不用。

mysql> SELECT * FROM student NATURAL JOIN class;+----+------+-----+---------+-------+| id | name | age | classId | cname |+----+------+-----+---------+-------+|  1 | s1   |  20 |       1 | 一班  ||  2 | s2   |  22 |       1 | 二班  ||  3 | s3   |  22 |       2 |       |+----+------+-----+---------+-------+3 rows in set

可以看到只有一列id了，因為student與class兩張表中相同的id列自動合并了，相當於內串連

SELECT * FROM student INNER JOIN class ON student.id = class.id

如果更改class表的cname欄位名稱為name會出現什麼情況呢？

mysql> ALTER TABLE class CHANGE cname name VARCHAR(10);Query OK, 3 rows affectedRecords: 3  Duplicates: 0  Warnings: 0mysql> SELECT * FROM student NATURAL JOIN class; -- 因為兩張表同名欄位有兩個，所有結果為空白Empty set

由於現在student與class表有兩個同名的欄位，所有結果為空白

MySQL教程（四） SQL串連查詢