mysql,SQL標準,多表查詢中內連接，外連接，自然連接等詳解之查詢結果集的笛卡爾積的演化

先附上數據。 CREATE TABLE `course` ( `cno` int(11) NOT NULL, `cname` char(30) CHARACTER SET utf8 NOT NULL, `ctime` int(11) NOT NULL, `scount` int(11) NOT NU

先附上數據。

CREATE TABLE `course` (
  `cno` int(11) NOT NULL,
  `cname` char(30) CHARACTER SET utf8 NOT NULL,
  `ctime` int(11) NOT NULL,
  `scount` int(11) NOT NULL,
  `ctest` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `course` VALUES ('4', '應用數學基礎', '48', '120', '2016-03-10 10:08:29');
INSERT INTO `course` VALUES ('5', '生物工程概論', '32', '80', '2016-03-10 10:09:24');
INSERT INTO `course` VALUES ('1', '電腦軟體基礎', '32', '70', '2016-03-10 10:09:47');
INSERT INTO `course` VALUES ('2', '電腦硬體基礎', '24', '80', '2016-03-10 10:10:28');
INSERT INTO `course` VALUES ('8', '模擬電路設計', '28', '90', '2016-04-06 10:11:02');
INSERT INTO `course` VALUES ('7', '機械設計實踐', '48', '68', '2016-03-10 10:11:29');
INSERT INTO `course` VALUES ('3', '生物化學', '32', '40', '2016-03-29 10:11:54');
INSERT INTO `course` VALUES ('9', '資料庫設計', '16', '80', '2016-03-10 10:12:14');
INSERT INTO `course` VALUES ('6', '設計理論', '28', '45', '2016-03-10 10:12:33');
INSERT INTO `course` VALUES ('10', '電腦入門', '24', '150', '2016-03-10 10:12:53');
INSERT INTO `course` VALUES ('11', '數字電路設計基礎', '30', '125', '2016-03-10 10:13:10');

CREATE TABLE `student` (
  `sno` char(4) CHARACTER SET utf8 DEFAULT NULL,
  `sname` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `dname` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `ssex` char(2) CHARACTER SET utf8 NOT NULL,
  `cno` int(11) NOT NULL,
  `mark` decimal(3,1) NOT NULL,
  `type` char(4) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `student` VALUES ('9701', '劉建國', '管理工程', '男', '4', '82.5', '必修');
INSERT INTO `student` VALUES ('9701', '劉建國', '管理工程', '男', '10', '70.0', '必修');
INSERT INTO `student` VALUES ('9701', '劉建國', '管理工程', '男', '1', '78.5', '選修');
INSERT INTO `student` VALUES ('9702', '李春', '環境工程', '女', '5', '63.0', '必修');
INSERT INTO `student` VALUES ('9702', '李春', '環境工程', '女', '10', '58.0', '選修');
INSERT INTO `student` VALUES ('9703', '王天', '生物', '男', '5', '48.5', '必修');
INSERT INTO `student` VALUES ('9703', '王天', '生物', '男', '2', '86.0', '選修');
INSERT INTO `student` VALUES ('9704', '李華', '電腦', '女', '4', '76.0', '必修');
INSERT INTO `student` VALUES ('9704', '李華', '電腦', '女', '1', '92.0', '必修');
INSERT INTO `student` VALUES ('9704', '李華', '電腦', '女', '2', '89.0', '必修');
INSERT INTO `student` VALUES ('9704', '李華', '電腦', '女', '9', '80.0', '必修');
INSERT INTO `student` VALUES ('9704', '李華', '電腦', '女', '8', '70.0', '選修');
INSERT INTO `student` VALUES ('9705', '孫慶', '電子工程', '男', '8', '79.0', '必修');
INSERT INTO `student` VALUES ('9705', '孫慶', '電子工程', '男', '1', '59.0', '必修');
INSERT INTO `student` VALUES ('9705', '孫慶', '電子工程', '男', '11', '52.0', '必修');
INSERT INTO `student` VALUES ('9705', '孫慶', '電子工程', '男', '6', '68.0', '必修');
INSERT INTO `student` VALUES ('9706', '高偉', '機械工程', '男', '13', '93.0', '必修');
INSERT INTO `student` VALUES ('9706', '高偉', '機械工程', '男', '12', '88.5', '必修');
INSERT INTO `student` VALUES ('9706', '高偉', '機械工程', '男', '1', '78.0', '選修');
INSERT INTO `student` VALUES ('9706', '高偉', '機械工程', '男', '10', '76.0', '選修');

CREATE TABLE `teacher` (
  `tno` int(11) NOT NULL,
  `tname` varchar(10) CHARACTER SET utf8 NOT NULL,
  `cno` int(11) NOT NULL,
  `sal` int(11) DEFAULT NULL,
  `dname` char(10) CHARACTER SET utf8 NOT NULL,
  `tsex` char(2) CHARACTER SET utf8 NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `teacher` VALUES ('1', '王軍', '4', '800', '數學', '男', '32');
INSERT INTO `teacher` VALUES ('2', '李丹', '5', '1200', '生物', '女', '54');
INSERT INTO `teacher` VALUES ('3', '王永軍', '1', '900', '電腦', '男', '40');
INSERT INTO `teacher` VALUES ('4', '劉小靜', '2', '1200', '電腦', '女', '46');
INSERT INTO `teacher` VALUES ('5', '高偉', '8', '2100', '電子工程', '男', '39');
INSERT INTO `teacher` VALUES ('6', '李偉', '7', '1200', '機械工程', '男', '29');
INSERT INTO `teacher` VALUES ('7', '劉輝', '3', '900', '生物', '女', '46');
INSERT INTO `teacher` VALUES ('8', '劉靜', '12', '1300', '經濟管理', '女', '28');
INSERT INTO `teacher` VALUES ('9', '李偉', '9', null, '電腦', '女', '43');
INSERT INTO `teacher` VALUES ('10', '劉一凱', '13', null, '電腦', '女', '33');

簡單的二表連接

SELECT tname,dname,cname,ctest from teacher,course WHERE teacher.cno=course.cno

該語句的執行過程實例可以表示這樣： a,系統首先執行from子句，這裡from子句列出有兩個表teacher表和course表，DBMS講計算這兩個表的笛卡爾積，列出這兩個表中行的所以可能組合，形成一個中間表。中間表中的每條記錄包含了兩個表中的所有行。 b,然後系統執行where子句，根據teacher.cno=course.cno關係對中間表進行搜索，去除那些不滿足該關係的記錄。 c,最後系統執行select語句，從執行where子句後得到的中間表的每條記錄中，提取tname,dname,cname,ctest4個欄位的信息作為結果表。需要強調，表的連接所依據的關係是在where子句中定義的。在實際應用中，用戶要實現表的連接必然要依據一定的關係。如果不指明連接關係，即不使用where子句。

SELECT tname,dname,cname,ctest from teacher,course

從結果可以看到，每個教師的信息均與所有課程信息進行了匹配連接。它實際返回連接表中所有數據行的笛卡爾積，其結果集合中的數據行數等於第一個表中符合查詢條件的數據行乘以第二個表中符合查詢條件的數據行數，即10X11=110條記錄。 採用join關鍵字建立連接 也可以在from子句中，通過連接關鍵字實現表的連接，這樣有助於將連接操作與where的搜索條件區分開來。

SELECT COLUMN from join_table join_type join_table on (join_condition)

join_type為連接類型，可分為4種：自然連接，內連接，外連接和交叉連接。 自連接 自連接是指表與其自身進行連接，這需要使用表別名。查詢成績中存在不及格課程的學生的姓名，所在系，所有的課程及成績信息。

SELECT s.sname,s.dname,s.cno,s.mark
from student s
where s.mark<60

無法得到想要結果

SELECT s.sname,s.dname,s.cno,s.mark
from student s
where s.sno in(SELECT DISTINCT s.sno from student s where s.mark<60)

得到想要結果

SELECT DISTINCT s.sname,s.dname,s.cno,s.mark
from student s,student s2
where s.sno=s2.sno
and s2.mark<60

from子句中的兩個表實際上都是表student。為了獨立地使用它們，採用表別名方法。

SELECT s.sname,s.dname,s.cno,s.mark
from student s,student s2
where s.sno=s2.sno
and s2.mark<60

系統首先執行from子句，將student表S1與它自身S2的笛卡爾積，作為中間表。實際上，該中間表的每一條記錄包含兩部分信息，一部分是S1的記錄，一部分是S2的記錄。而後執行where子句，在中間表中，搜索S2中成績低於60的學生的記錄，同時要求記錄中S1與S2是同一個學生的記錄即學號相同。最後執行select語句，從中間表獲取S1中相應的信息作為結果表。當執行where子句，從中間表中逐條搜索S2中成績低於60的學生的記錄時，由於孫慶有兩門課程不及格，所以對每門不及格的記錄都滿足搜索條件，因此導致從S1得到的信息中出現了重覆的記錄。簡單來說，中間表是沒有重覆記錄的，但是S1部分欄位是有重覆的，而結果集提取的只是S1部分的欄位，因此就有可能有重覆記錄。一般情況，自連接也可以使用子查詢的方式實現。

SELECT DISTINCT s.sname,s.dname,s.cno,s.mark
from student s,student s2
where s.sno=s2.sno
and s.mark<60

自然連接它將表中具有相同名稱的列自動進行記錄匹配，自然連接不必指定任何同等連接條件。

自然連接自動判斷相同名稱的列，而後形成匹配。缺點是，雖然可以指定查詢結果包括哪些列，但是不能人為地指定哪些列被匹配。另外，自然連接的一個特點是連接後的結果表中匹配的列只有一個。如上，在自然連接後的表中只有一列C。從student表和teacher表中查詢學生姓名，所在系，所修的本系教師開設的課程的課程號以及開課教師姓名。這時候就採用natural join對兩個表進行自然連接。

SELECT sname,dname,cno,tname
from student NATURAL join teacher

等價

SELECT sname,s.dname,s.cno,tname
from student s, teacher t
where s.dname=t.dname
and s.cno=t.cno

事實上，使用基於where子句的等值連接要比使用natural join運算符進行自然連接要靈活的多。正如前面介紹的，使用natural join運算符自動判斷出具有相同名稱的列，而後形成匹配，不能人為地指定哪些列被匹配。當自然連接student和teacher表時，CNO和dname列同時被匹配，而不能只匹配一列。外連接不管是內連接還是帶where子句的多表查詢，都組合自多個表，並生成結果表。換句話說，如果任何一個源表中的行在另一個源表中沒有匹配，DBMS將把該行放在最後的結果表中。而外連接告訴ODBC生成的結果表，不僅包含符合條件的行，而且還包含左表（左外連接時），右表（右外連接時）或兩個邊接表（全外連接）中所有的數據行。 SQL的外連接共有三種類型：左外連接，右外連接，全外連接。 1，左外連接左外連接，left outer join ,告訴DBMS生成的結果表中，除了包括匹配行外，還包括join關鍵字（from子句中）左邊表的不匹配行。左外連接實際可以表示為：左外連接=內連接+左邊表中失配的元組。其中，缺少的右邊表中的屬性值用null表示。如下：

SELECT s.sno,sname,s.cno,cname,ctest,mark
from student s LEFT JOIN course c
on s.cno=c.cno
ORDER BY sname

右外連接右外連接，right outer join ,告訴DBMS生成的結果表中，除了包括匹配行外，還包括join關鍵字（from子句中）右邊表的不匹配行。右外連接實際可以表示為：右外連接=內連接+右邊表中失配的元組。其中，缺少的左邊表中的屬性值用null表示。如下：

SELECT s.sno,sname,s.cno,cname,ctest,mark
from student s RIGHT JOIN course c
on s.cno=c.cno
ORDER BY sname<