[原]SQL内外连接

-- Author:Winty(天堂露珠)
-- Date:2009-6-14
use test;
-- 创建表
CREATE TABLE Student(
    sid VARCHAR(20),
    sname VARCHAR(50),
    cid VARCHAR(20),
    PRIMARY KEY(sid)
);

CREATE TABLE Course(
    cid VARCHAR(20),
    cname VARCHAR(50),
    PRIMARY KEY(cid)
);

INSERT INTO Student values('S001','张三','C100');
INSERT INTO Student values('S002','李四','C200');
INSERT INTO Student values('S003','王五','C300');
-- C000是Course表中没有的
INSERT INTO Student values('S004','赵六','C000');

INSERT INTO Course values('C100','操作系统');
INSERT INTO Course values('C200','编译原理');
INSERT INTO Course values('C300','网络安全');
-- C400是Student表中没有引用的
INSERT INTO Course values('C400','软件技术');


-- 笛卡尔积
SELECT * FROM Student , Course;
/*
结果
+------+-------+------+------+----------+
| sid  | sname | cid  | cid  | cname    |
+------+-------+------+------+----------+
| S001 | 张三  | C100 | C100 | 操作系统 |
| S002 | 李四  | C200 | C100 | 操作系统 |
| S003 | 王五  | C300 | C100 | 操作系统 |
| S004 | 赵六  | C000 | C100 | 操作系统 |
| S001 | 张三  | C100 | C200 | 编译原理 |
| S002 | 李四  | C200 | C200 | 编译原理 |
| S003 | 王五  | C300 | C200 | 编译原理 |
| S004 | 赵六  | C000 | C200 | 编译原理 |
| S001 | 张三  | C100 | C300 | 网络安全 |
| S002 | 李四  | C200 | C300 | 网络安全 |
| S003 | 王五  | C300 | C300 | 网络安全 |
| S004 | 赵六  | C000 | C300 | 网络安全 |
| S001 | 张三  | C100 | C400 | 软件技术 |
| S002 | 李四  | C200 | C400 | 软件技术 |
| S003 | 王五  | C300 | C400 | 软件技术 |
| S004 | 赵六  | C000 | C400 | 软件技术 |
+------+-------+------+------+----------+
*/


-- 自然连接:NATURAL JOIN
SELECT * FROM Student NATURAL JOIN Course;
/*
结果:
+------+------+-------+----------+
| cid  | sid  | sname | cname    |
+------+------+-------+----------+
| C100 | S001 | 张三  | 操作系统 |
| C200 | S002 | 李四  | 编译原理 |
| C300 | S003 | 王五  | 网络安全 |
+------+------+-------+----------+
*/


-- 内连接:INNER JOIN
SELECT * FROM Student s INNER JOIN Course c ON s.cid=c.cid;
/*
结果:
+------+------+-------+----------+
| cid  | sid  | sname | cname    |
+------+------+-------+----------+
| C100 | S001 | 张三  | 操作系统 |
| C200 | S002 | 李四  | 编译原理 |
| C300 | S003 | 王五  | 网络安全 |
+------+------+-------+----------+
*/

-- 与使用INNER JOIN结果类似，但包含重复列(cid)
SELECT * FROM Student s , Course c WHERE s.cid=c.cid;
/*
结果:
+------+-------+------+------+----------+
| sid  | sname | cid  | cid  | cname    |
+------+-------+------+------+----------+
| S001 | 张三  | C100 | C100 | 操作系统 |
| S002 | 李四  | C200 | C200 | 编译原理 |
| S003 | 王五  | C300 | C300 | 网络安全 |
+------+-------+------+------+----------+
*/


-- 左外连接: LEFT OUTER JOIN
SELECT * FROM Student AS s LEFT JOIN Course AS c ON s.cid=c.cid;
/*
结果:
+------+-------+------+------+----------+
| sid  | sname | cid  | cid  | cname    |
+------+-------+------+------+----------+
| S001 | 张三  | C100 | C100 | 操作系统 |
| S002 | 李四  | C200 | C200 | 编译原理 |
| S003 | 王五  | C300 | C300 | 网络安全 |
| S004 | 赵六  | C000 | NULL | NULL     |
+------+-------+------+------+----------+
*/


-- 右外连接: RIGHT OUTER JOIN
SELECT * FROM Student s RIGHT JOIN Course c ON s.cid=c.cid;
/*
结果:
+------+-------+------+------+----------+
| sid  | sname | cid  | cid  | cname    |
+------+-------+------+------+----------+
| S001 | 张三  | C100 | C100 | 操作系统 |
| S002 | 李四  | C200 | C200 | 编译原理 |
| S003 | 王五  | C300 | C300 | 网络安全 |
| NULL | NULL  | NULL | C400 | 软件技术 |
+------+-------+------+------+----------+
*/


-- FULL OUTER JOIN:用左右连接实现
SELECT * FROM Student AS s LEFT JOIN Course AS c ON s.cid=c.cid
UNION
SELECT * FROM Student s RIGHT JOIN Course c ON s.cid=c.cid;
/*
结果
+------+-------+------+------+----------+
| sid  | sname | cid  | cid  | cname    |
+------+-------+------+------+----------+
| S001 | 张三  | C100 | C100 | 操作系统 |
| S002 | 李四  | C200 | C200 | 编译原理 |
| S003 | 王五  | C300 | C300 | 网络安全 |
| S004 | 赵六  | C000 | NULL | NULL     |
| NULL | NULL  | NULL | C400 | 软件技术 |
+------+-------+------+------+----------+
*/

-- 交叉连接:CROSS JOIN
-- 与SELECT * FROM Student
SELECT * FROM Student CROSS JOIN Course;
/*
结果
+------+-------+------+------+----------+
| sid  | sname | cid  | cid  | cname    |
+------+-------+------+------+----------+
| S001 | 张三  | C100 | C100 | 操作系统 |
| S002 | 李四  | C200 | C100 | 操作系统 |
| S003 | 王五  | C300 | C100 | 操作系统 |
| S004 | 赵六  | C000 | C100 | 操作系统 |
| S001 | 张三  | C100 | C200 | 编译原理 |
| S002 | 李四  | C200 | C200 | 编译原理 |
| S003 | 王五  | C300 | C200 | 编译原理 |
| S004 | 赵六  | C000 | C200 | 编译原理 |
| S001 | 张三  | C100 | C300 | 网络安全 |
| S002 | 李四  | C200 | C300 | 网络安全 |
| S003 | 王五  | C300 | C300 | 网络安全 |
| S004 | 赵六  | C000 | C300 | 网络安全 |
| S001 | 张三  | C100 | C400 | 软件技术 |
| S002 | 李四  | C200 | C400 | 软件技术 |
| S003 | 王五  | C300 | C400 | 软件技术 |
| S004 | 赵六  | C000 | C400 | 软件技术 |
+------+-------+------+------+----------+
*/


-- UNION
SELECT * FROM Student WHERE sid='S001'
UNION
SELECT * FROM Student WHERE sid='S002';
-- 与此等价
SELECT * FROM Student WHERE sid='S001' OR sid='S002';
/*
结果
+------+-------+------+
| sid  | sname | cid  |
+------+-------+------+
| S001 | 张三  | C100 |
| S002 | 李四  | C200 |
+------+-------+------+
*/