4.1.2 外连接

发表于 更新于 分类于 ， ， ， Waline： 本文字数： 38k 阅读时长 ≈ 35 分钟

4.1.2 外连接

假设我们要显示一个所有学生的列表,显示他们的ID、name、 dept_name和tot_cred,以及他们所选修的课程。下面的查询好像检索出了所需的信息:

1
2
select *
from student natural join takes;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> select *
from student natural join takes;
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| ID    | name     | dept_name  | tot_cred | course_id | sec_id | semester | year | grade |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| 00128 | Zhang    | Comp. Sci. | 102      | CS-101    | 1      | Fall     | 2009 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | CS-347    | 1      | Fall     | 2009 | A-    |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-101    | 1      | Fall     | 2009 | C     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-190    | 2      | Spring   | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-315    | 1      | Spring   | 2010 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-347    | 1      | Fall     | 2009 | A     |
| 19991 | Brandt   | History    | 80       | HIS-351   | 1      | Spring   | 2010 | B     |
| 23121 | Chavez   | Finance    | 110      | FIN-201   | 1      | Spring   | 2010 | C+    |
| 44553 | Peltier  | Physics    | 56       | PHY-101   | 1      | Fall     | 2009 | B-    |
| 45678 | Levy     | Physics    | 46       | CS-101    | 1      | Fall     | 2009 | F     |
| 45678 | Levy     | Physics    | 46       | CS-101    | 1      | Spring   | 2010 | B+    |
| 45678 | Levy     | Physics    | 46       | CS-319    | 1      | Spring   | 2010 | B     |
| 54321 | Williams | Comp. Sci. | 54       | CS-101    | 1      | Fall     | 2009 | A-    |
| 54321 | Williams | Comp. Sci. | 54       | CS-190    | 2      | Spring   | 2009 | B+    |
| 55739 | Sanchez  | Music      | 38       | MU-199    | 1      | Spring   | 2010 | A-    |
| 76543 | Brown    | Comp. Sci. | 58       | CS-101    | 1      | Fall     | 2009 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | CS-319    | 2      | Spring   | 2010 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | EE-181    | 1      | Spring   | 2009 | C     |
| 98765 | Bourikas | Elec. Eng. | 98       | CS-101    | 1      | Fall     | 2009 | C-    |
| 98765 | Bourikas | Elec. Eng. | 98       | CS-315    | 1      | Spring   | 2010 | B     |
| 98988 | Tanaka   | Biology    | 120      | BIO-101   | 1      | Summer   | 2009 | A     |
| 98988 | Tanaka   | Biology    | 120      | BIO-301   | 1      | Summer   | 2010 | NULL  |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
22 rows in set (0.08 sec)

遗憾的是,上述查询与想要的结果是不同的。假设有一些学生,他们没有选修任何课程。那么这些学生在student关系中所对应的元组与takes关系中的任何元组配对,都不会满足自然连接的条件,从而这些学生的数据就不会出现在结果中。这样我们就看不到没有选修任何课程的学生的任何信息。例如,
在student关系和takes关系中,ID为70557的学生Snow没有选修任何课程。Snow出现在student关系中,但是Snow的ID号没有出现在takes的I列中。从而Snow不会出现在自然连接的结果中。

外连接不会丢失元组

更为一般地,在参与连接的任何一个或两个关系中的某些元组可能会以这种方式”丢失“。外连接( outer join)运算与我们已经学过的连接运算类似,但通过在结果中创建包含空值元组的方式,保留了那些在连接中丢失的元组。
例如,为了保证在我们前例中的名为Snow的学生出现在结果中,可以在连接结果中加入一个元组,它

  • 在来自student关系的所有属性上的值被设置为学生Snow的相应值,
  • 在所有余下的来自takes关系属性上的值被设为null,这些属性是course id、sed、 semester和year

内连接定义

不保留未匹配元组的连接运算被称作内连接运算(inner join)。

外连接定义

保留未匹配元组的连接运算被称作外连接运算(outer join)。

外连接分类

实际上有三种形式的外连接:

  • 左外连接(left outer join)只保留出现在左外连接运算之前(左边)的关系中的元组。
  • 右外连接(right outer join)只保留出现在右外连接运算之后**(右边)的关系中的元组**。
  • 全外连接(full outer join)保留出现在两个关系中的元组。

左外连接运算过程

我们现在详细解释每种形式的外连接是怎样操作的。我们可以按照如下方式计算左外连接运算

  • 首先,像前面那样计算出内连接的结果;
  • 然后,对于在内连接的左侧关系中任意一个与右侧关系中任何元组都不匹配的元组t,向连接结果中加入一个元组r,r的构造如下:
    • 元组r从左侧关系得到的属性被赋为元组t中的值。
    • 元组r的其他属性被赋为空值。

实例

1
2
select *
    from student natural left outer join takes;

与内连接的结果不同,此结果中包含了学生Snow(ID 70557),但是在Snow对应的元组中,在**那些只出现在takes关系模式中的属性上取空值**。

左外连接结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> select *
    from student natural left outer join takes;
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| ID    | name     | dept_name  | tot_cred | course_id | sec_id | semester | year | grade |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| 00128 | Zhang    | Comp. Sci. | 102      | CS-101    | 1      | Fall     | 2009 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | CS-347    | 1      | Fall     | 2009 | A-    |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-101    | 1      | Fall     | 2009 | C     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-190    | 2      | Spring   | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-315    | 1      | Spring   | 2010 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-347    | 1      | Fall     | 2009 | A     |
| 19991 | Brandt   | History    | 80       | HIS-351   | 1      | Spring   | 2010 | B     |
| 23121 | Chavez   | Finance    | 110      | FIN-201   | 1      | Spring   | 2010 | C+    |
| 44553 | Peltier  | Physics    | 56       | PHY-101   | 1      | Fall     | 2009 | B-    |
| 45678 | Levy     | Physics    | 46       | CS-101    | 1      | Fall     | 2009 | F     |
| 45678 | Levy     | Physics    | 46       | CS-101    | 1      | Spring   | 2010 | B+    |
| 45678 | Levy     | Physics    | 46       | CS-319    | 1      | Spring   | 2010 | B     |
| 54321 | Williams | Comp. Sci. | 54       | CS-101    | 1      | Fall     | 2009 | A-    |
| 54321 | Williams | Comp. Sci. | 54       | CS-190    | 2      | Spring   | 2009 | B+    |
| 55739 | Sanchez  | Music      | 38       | MU-199    | 1      | Spring   | 2010 | A-    |
| 70557 | Snow     | Physics    | 0        | NULL      | NULL   | NULL     | NULL | NULL  |
| 76543 | Brown    | Comp. Sci. | 58       | CS-101    | 1      | Fall     | 2009 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | CS-319    | 2      | Spring   | 2010 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | EE-181    | 1      | Spring   | 2009 | C     |
| 98765 | Bourikas | Elec. Eng. | 98       | CS-101    | 1      | Fall     | 2009 | C-    |
| 98765 | Bourikas | Elec. Eng. | 98       | CS-315    | 1      | Spring   | 2010 | B     |
| 98988 | Tanaka   | Biology    | 120      | BIO-101   | 1      | Summer   | 2009 | A     |
| 98988 | Tanaka   | Biology    | 120      | BIO-301   | 1      | Summer   | 2010 | NULL  |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
23 rows in set (0.07 sec)

内连接结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> select *
    from student natural join takes;
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| ID    | name     | dept_name  | tot_cred | course_id | sec_id | semester | year | grade |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| 00128 | Zhang    | Comp. Sci. | 102      | CS-101    | 1      | Fall     | 2009 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | CS-347    | 1      | Fall     | 2009 | A-    |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-101    | 1      | Fall     | 2009 | C     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-190    | 2      | Spring   | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-315    | 1      | Spring   | 2010 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-347    | 1      | Fall     | 2009 | A     |
| 19991 | Brandt   | History    | 80       | HIS-351   | 1      | Spring   | 2010 | B     |
| 23121 | Chavez   | Finance    | 110      | FIN-201   | 1      | Spring   | 2010 | C+    |
| 44553 | Peltier  | Physics    | 56       | PHY-101   | 1      | Fall     | 2009 | B-    |
| 45678 | Levy     | Physics    | 46       | CS-101    | 1      | Fall     | 2009 | F     |
| 45678 | Levy     | Physics    | 46       | CS-101    | 1      | Spring   | 2010 | B+    |
| 45678 | Levy     | Physics    | 46       | CS-319    | 1      | Spring   | 2010 | B     |
| 54321 | Williams | Comp. Sci. | 54       | CS-101    | 1      | Fall     | 2009 | A-    |
| 54321 | Williams | Comp. Sci. | 54       | CS-190    | 2      | Spring   | 2009 | B+    |
| 55739 | Sanchez  | Music      | 38       | MU-199    | 1      | Spring   | 2010 | A-    |
| 76543 | Brown    | Comp. Sci. | 58       | CS-101    | 1      | Fall     | 2009 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | CS-319    | 2      | Spring   | 2010 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | EE-181    | 1      | Spring   | 2009 | C     |
| 98765 | Bourikas | Elec. Eng. | 98       | CS-101    | 1      | Fall     | 2009 | C-    |
| 98765 | Bourikas | Elec. Eng. | 98       | CS-315    | 1      | Spring   | 2010 | B     |
| 98988 | Tanaka   | Biology    | 120      | BIO-101   | 1      | Summer   | 2009 | A     |
| 98988 | Tanaka   | Biology    | 120      | BIO-301   | 1      | Summer   | 2010 | NULL  |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
22 rows in set (0.07 sec)

外连接比内连接多了一行:

1
| 70557 | Snow     | Physics    | 0        | NULL      | NULL   | NULL     | NULL | NULL  |

找出所有一门课程也没有选修的学生

作为使用外连接运算的另一个例子,我们可以写出查询”找出所有一门课程也没有选修的学生“:

1
2
3
select ID
    from student natural left outer join takes
    where course_id is null;
1
2
3
4
5
6
7
8
9
mysql> select ID
    from student natural left outer join takes
    where course_id is null;
+-------+
| ID    |
+-------+
| 70557 |
+-------+
1 row in set (0.03 sec)

右外连接

右外连接和左外连接是对称的。
来自右侧关系中不匹配左侧关系任何元组的元组被补上空值并加入到右外连接的结果中。

实例

如果我们使用右外连接来重写前面的查询,并交换列出关系的次序,如下所示:

1
2
select *
    from takes natural right outer join student;

我们得到的结果是一样的,只不过结果中属性出现的顺序不同

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> select *
    from takes natural right outer join student;
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| ID    | name     | dept_name  | tot_cred | course_id | sec_id | semester | year | grade |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| 00128 | Zhang    | Comp. Sci. | 102      | CS-101    | 1      | Fall     | 2009 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | CS-347    | 1      | Fall     | 2009 | A-    |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-101    | 1      | Fall     | 2009 | C     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-190    | 2      | Spring   | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-315    | 1      | Spring   | 2010 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-347    | 1      | Fall     | 2009 | A     |
| 19991 | Brandt   | History    | 80       | HIS-351   | 1      | Spring   | 2010 | B     |
| 23121 | Chavez   | Finance    | 110      | FIN-201   | 1      | Spring   | 2010 | C+    |
| 44553 | Peltier  | Physics    | 56       | PHY-101   | 1      | Fall     | 2009 | B-    |
| 45678 | Levy     | Physics    | 46       | CS-101    | 1      | Fall     | 2009 | F     |
| 45678 | Levy     | Physics    | 46       | CS-101    | 1      | Spring   | 2010 | B+    |
| 45678 | Levy     | Physics    | 46       | CS-319    | 1      | Spring   | 2010 | B     |
| 54321 | Williams | Comp. Sci. | 54       | CS-101    | 1      | Fall     | 2009 | A-    |
| 54321 | Williams | Comp. Sci. | 54       | CS-190    | 2      | Spring   | 2009 | B+    |
| 55739 | Sanchez  | Music      | 38       | MU-199    | 1      | Spring   | 2010 | A-    |
| 70557 | Snow     | Physics    | 0        | NULL      | NULL   | NULL     | NULL | NULL  |
| 76543 | Brown    | Comp. Sci. | 58       | CS-101    | 1      | Fall     | 2009 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | CS-319    | 2      | Spring   | 2010 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | EE-181    | 1      | Spring   | 2009 | C     |
| 98765 | Bourikas | Elec. Eng. | 98       | CS-101    | 1      | Fall     | 2009 | C-    |
| 98765 | Bourikas | Elec. Eng. | 98       | CS-315    | 1      | Spring   | 2010 | B     |
| 98988 | Tanaka   | Biology    | 120      | BIO-101   | 1      | Summer   | 2009 | A     |
| 98988 | Tanaka   | Biology    | 120      | BIO-301   | 1      | Summer   | 2010 | NULL  |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
23 rows in set (0.07 sec)

经过我的测试MySQL8.0中这里的左外连接的结果和右外连接的结果一样.并且属性出现的顺序也一样:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> select *
    from student natural left outer join takes;
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| ID    | name     | dept_name  | tot_cred | course_id | sec_id | semester | year | grade |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| 00128 | Zhang    | Comp. Sci. | 102      | CS-101    | 1      | Fall     | 2009 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | CS-347    | 1      | Fall     | 2009 | A-    |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-101    | 1      | Fall     | 2009 | C     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-190    | 2      | Spring   | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-315    | 1      | Spring   | 2010 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-347    | 1      | Fall     | 2009 | A     |
| 19991 | Brandt   | History    | 80       | HIS-351   | 1      | Spring   | 2010 | B     |
| 23121 | Chavez   | Finance    | 110      | FIN-201   | 1      | Spring   | 2010 | C+    |
| 44553 | Peltier  | Physics    | 56       | PHY-101   | 1      | Fall     | 2009 | B-    |
| 45678 | Levy     | Physics    | 46       | CS-101    | 1      | Fall     | 2009 | F     |
| 45678 | Levy     | Physics    | 46       | CS-101    | 1      | Spring   | 2010 | B+    |
| 45678 | Levy     | Physics    | 46       | CS-319    | 1      | Spring   | 2010 | B     |
| 54321 | Williams | Comp. Sci. | 54       | CS-101    | 1      | Fall     | 2009 | A-    |
| 54321 | Williams | Comp. Sci. | 54       | CS-190    | 2      | Spring   | 2009 | B+    |
| 55739 | Sanchez  | Music      | 38       | MU-199    | 1      | Spring   | 2010 | A-    |
| 70557 | Snow     | Physics    | 0        | NULL      | NULL   | NULL     | NULL | NULL  |
| 76543 | Brown    | Comp. Sci. | 58       | CS-101    | 1      | Fall     | 2009 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | CS-319    | 2      | Spring   | 2010 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | EE-181    | 1      | Spring   | 2009 | C     |
| 98765 | Bourikas | Elec. Eng. | 98       | CS-101    | 1      | Fall     | 2009 | C-    |
| 98765 | Bourikas | Elec. Eng. | 98       | CS-315    | 1      | Spring   | 2010 | B     |
| 98988 | Tanaka   | Biology    | 120      | BIO-101   | 1      | Summer   | 2009 | A     |
| 98988 | Tanaka   | Biology    | 120      | BIO-301   | 1      | Summer   | 2010 | NULL  |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
23 rows in set (0.06 sec)

全外连接

全外连接是左外连接与右外连接类型的组合。在内连接结果计算出来之后:

  • 左侧关系中不匹配右侧关系任何元组的元组被添上空值并加到结果中.
  • 右侧关系中不匹配左侧关系任何元组的元组也被添上空值并加到结果中。

实例

作为使用全外连接的例子,考虑查询:”显示Comp. Sci. 系所有学生以及他们在2009年春季选修的所有课程段的列表,2009年春季开设的所有课程段都必须显示,即使没有Comp. Sci. 系的学生选修这些课程段“。此查询可写为:

1
2
3
4
5
6
7
8
9
10
select *
from(
    select *
    from student
    where dept_name ='Comp. Sci. '
) as S natural full outer join(
    select *
    from takes
    where semester='Spring' and year =2009
) as T;

MySQL8.0不支持全外连接

经过我的测试最新版的MySQL8.0不支持全外连接natural full outer join;
on子句可以和外连接一起使用。下述查询与我们见过的第一个使用”student natural left outer join takes“的查询是相同的,只不过属性ID在结果中出现两次。

MySQL中实现全外连接

通过union元素

MySQL支持并运算(union),所以可以通过**左外连接和右外连接的并运算**得到全外连接.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select S1.*,T1.course_id,T1.sec_id,T1.semester,T1.year,grade
from(
    select *
    from student
    where dept_name ='Comp. Sci. '
) as S1
natural left outer join
(
    select *
        from takes
        where semester='Spring' and year =2009
) as T1
union
select S2.*,T2.course_id,T2.sec_id,T2.semester,T2.year,grade
from(
    select *
    from student
    where dept_name ='Comp. Sci. '
) as S2
natural right outer join
(
    select *
        from takes
        where semester='Spring' and year =2009
) as T2;

左外连接结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql>  select S1.*,T1.course_id,T1.sec_id,T1.semester,T1.year,grade
from(
    select *
    from student
    where dept_name ='Comp. Sci. '
) as S1
natural left outer join
(
    select *
        from takes
        where semester='Spring' and year =2009
) as T1;
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| ID    | name     | dept_name  | tot_cred | course_id | sec_id | semester | year | grade |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| 00128 | Zhang    | Comp. Sci. | 102      | NULL      | NULL   | NULL     | NULL | NULL  |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-190    | 2      | Spring   | 2009 | A     |
| 54321 | Williams | Comp. Sci. | 54       | CS-190    | 2      | Spring   | 2009 | B+    |
| 76543 | Brown    | Comp. Sci. | 58       | NULL      | NULL   | NULL     | NULL | NULL  |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
4 rows in set (0.04 sec)

右外连接结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> select S2.*,T2.course_id,T2.sec_id,T2.semester,T2.year,grade
from(
    select *
    from student
    where dept_name ='Comp. Sci. '
) as S2
natural right outer join
(
    select *
        from takes
        where semester='Spring' and year =2009
) as T2;
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| ID    | name     | dept_name  | tot_cred | course_id | sec_id | semester | year | grade |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| 12345 | Shankar  | Comp. Sci. | 32       | CS-190    | 2      | Spring   | 2009 | A     |
| 54321 | Williams | Comp. Sci. | 54       | CS-190    | 2      | Spring   | 2009 | B+    |
| NULL  | NULL     | NULL       | NULL     | EE-181    | 1      | Spring   | 2009 | C     |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
3 rows in set (0.04 sec)

全外连接结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
mysql> select S1.*,T1.course_id,T1.sec_id,T1.semester,T1.year,grade
from(
    select *
    from student
    where dept_name ='Comp. Sci. '
) as S1
natural left outer join
(
    select *
        from takes
        where semester='Spring' and year =2009
) as T1
union
select S2.*,T2.course_id,T2.sec_id,T2.semester,T2.year,grade
from(
    select *
    from student
    where dept_name ='Comp. Sci. '
) as S2
natural right outer join
(
    select *
        from takes
        where semester='Spring' and year =2009
) as T2;
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| ID    | name     | dept_name  | tot_cred | course_id | sec_id | semester | year | grade |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
| 00128 | Zhang    | Comp. Sci. | 102      | NULL      | NULL   | NULL     | NULL | NULL  |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-190    | 2      | Spring   | 2009 | A     |
| 54321 | Williams | Comp. Sci. | 54       | CS-190    | 2      | Spring   | 2009 | B+    |
| 76543 | Brown    | Comp. Sci. | 58       | NULL      | NULL   | NULL     | NULL | NULL  |
| NULL  | NULL     | NULL       | NULL     | EE-181    | 1      | Spring   | 2009 | C     |
+-------+----------+------------+----------+-----------+--------+----------+------+-------+
5 rows in set (0.04 sec)

要显示给出属性的顺序

还需要注意的是左外连接和又外连接的结果集中属性的排列顺序不同,所以不要使用select *,而是在select子句中显示给出两个表的属性的排列顺序.

错误的写法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
mysql> select *
from(
    select *
    from student
    where dept_name ='Comp. Sci. '
) as S1
natural left outer join
(
    select *
        from takes
        where semester='Spring' and year =2009
) as T1
union
select *
from(
    select *
    from student
    where dept_name ='Comp. Sci. '
) as S2
natural right outer join
(
    select *
        from takes
        where semester='Spring' and year =2009
) as T2;
+-------+----------+------------+----------+-----------+--------+----------+------------+-------+
| ID    | name     | dept_name  | tot_cred | course_id | sec_id | semester | year       | grade |
+-------+----------+------------+----------+-----------+--------+----------+------------+-------+
| 00128 | Zhang    | Comp. Sci. | 102      | NULL      | NULL   | NULL     | NULL       | NULL  |
| 12345 | Shankar  | Comp. Sci. | 32       | CS-190    | 2      | Spring   | 2009       | A     |
| 54321 | Williams | Comp. Sci. | 54       | CS-190    | 2      | Spring   | 2009       | B+    |
| 76543 | Brown    | Comp. Sci. | 58       | NULL      | NULL   | NULL     | NULL       | NULL  |
| 12345 | CS-190   | 2          | Spring   | 2009      | A      | Shankar  | Comp. Sci. | 32    |
| 54321 | CS-190   | 2          | Spring   | 2009      | B+     | Williams | Comp. Sci. | 54    |
| 76653 | EE-181   | 1          | Spring   | 2009      | C      | NULL     | NULL       | NULL  |
+-------+----------+------------+----------+-----------+--------+----------+------------+-------+
7 rows in set (0.04 sec)

on子句和外连接一起使用

on子句可以和外连接一起使用。下述查询与我们见过的第一个使用”student natural left outer join takes“的查询是相同的,只不过属性ID在结果中出现两次。

1
2
3
select *
from student left outer join takes 
on student.ID= takes.ID;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> select *
from student left outer join takes 
on student.ID= takes.ID;
+-------+----------+------------+----------+-------+-----------+--------+----------+------+-------+
| ID    | name     | dept_name  | tot_cred | ID    | course_id | sec_id | semester | year | grade |
+-------+----------+------------+----------+-------+-----------+--------+----------+------+-------+
| 00128 | Zhang    | Comp. Sci. | 102      | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 12345 | Shankar  | Comp. Sci. | 32       | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 12345 | Shankar  | Comp. Sci. | 32       | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 19991 | Brandt   | History    | 80       | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 23121 | Chavez   | Finance    | 110      | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 44553 | Peltier  | Physics    | 56       | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 45678 | Levy     | Physics    | 46       | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 45678 | Levy     | Physics    | 46       | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 45678 | Levy     | Physics    | 46       | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 54321 | Williams | Comp. Sci. | 54       | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 54321 | Williams | Comp. Sci. | 54       | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 55739 | Sanchez  | Music      | 38       | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 70557 | Snow     | Physics    | 0        | NULL  | NULL      | NULL   | NULL     | NULL | NULL  |
| 76543 | Brown    | Comp. Sci. | 58       | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 98765 | Bourikas | Elec. Eng. | 98       | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 98765 | Bourikas | Elec. Eng. | 98       | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 98988 | Tanaka   | Biology    | 120      | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 98988 | Tanaka   | Biology    | 120      | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
+-------+----------+------------+----------+-------+-----------+--------+----------+------+-------+
23 rows in set (0.07 sec)

外连接中on子句和where子句

外连接补空值的条件

外连接只为那些对相应内连接结果没有贡献的元组补上空值并加入结果。也就是on子句为false时,外连接才会补上空值.

外连接中on子句和where子句的不同

  • on条件是外连接声明的一部分,
  • 但where子句却不是。

在我们的例子中,ID为70557的学生”Snow“所对应的student元组的情况就说明了这样的差异。假设我们把前述查询中的on子句谓词换成where子句,并使用on条件true

1
2
3
4
select *
    from student left outer join takes
    on true
    where student.ID= takes.ID;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> select *
    from student left outer join takes
    on true
    where student.ID= takes.ID;
+-------+----------+------------+----------+-------+-----------+--------+----------+------+-------+
| ID    | name     | dept_name  | tot_cred | ID    | course_id | sec_id | semester | year | grade |
+-------+----------+------------+----------+-------+-----------+--------+----------+------+-------+
| 00128 | Zhang    | Comp. Sci. | 102      | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 12345 | Shankar  | Comp. Sci. | 32       | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 12345 | Shankar  | Comp. Sci. | 32       | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 19991 | Brandt   | History    | 80       | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 23121 | Chavez   | Finance    | 110      | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 44553 | Peltier  | Physics    | 56       | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 45678 | Levy     | Physics    | 46       | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 45678 | Levy     | Physics    | 46       | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 45678 | Levy     | Physics    | 46       | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 54321 | Williams | Comp. Sci. | 54       | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 54321 | Williams | Comp. Sci. | 54       | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 55739 | Sanchez  | Music      | 38       | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 76543 | Brown    | Comp. Sci. | 58       | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 98765 | Bourikas | Elec. Eng. | 98       | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 98765 | Bourikas | Elec. Eng. | 98       | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 98988 | Tanaka   | Biology    | 120      | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 98988 | Tanaka   | Biology    | 120      | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
+-------+----------+------------+----------+-------+-----------+--------+----------+------+-------+
22 rows in set (0.07 sec)

早先的查询使用带on条件的左外连接,包括元组(70557,Snow, Physics,0,null,null,null,null,null,null),
因为在takes中没有ID=70557的元组。然而在后面的查询中,每个元组都满足连接条件true,因此外连接不会产生出补上空值的元组。
外连接实际上产生了两个关系的笛卡儿积。因为在takes中没有ID=70557的元组,每次当外连接中出现name="Snow"的元组时, student.ID与takes.ID的取值必然是不同的,这样的元组会被where子句谓词排除掉。从而学生Snow不会出现在后面查询的结果中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
mysql> select *
    from student left outer join takes
    on true;
+-------+----------+------------+----------+-------+-----------+--------+----------+------+-------+
| ID    | name     | dept_name  | tot_cred | ID    | course_id | sec_id | semester | year | grade |
+-------+----------+------------+----------+-------+-----------+--------+----------+------+-------+
| 00128 | Zhang    | Comp. Sci. | 102      | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 19991 | Brandt   | History    | 80       | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 23121 | Chavez   | Finance    | 110      | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 44553 | Peltier  | Physics    | 56       | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 45678 | Levy     | Physics    | 46       | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 54321 | Williams | Comp. Sci. | 54       | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 55739 | Sanchez  | Music      | 38       | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 70557 | Snow     | Physics    | 0        | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 98765 | Bourikas | Elec. Eng. | 98       | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 98988 | Tanaka   | Biology    | 120      | 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 12345 | Shankar  | Comp. Sci. | 32       | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 19991 | Brandt   | History    | 80       | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 23121 | Chavez   | Finance    | 110      | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 44553 | Peltier  | Physics    | 56       | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 45678 | Levy     | Physics    | 46       | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 54321 | Williams | Comp. Sci. | 54       | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 55739 | Sanchez  | Music      | 38       | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 70557 | Snow     | Physics    | 0        | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 76543 | Brown    | Comp. Sci. | 58       | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 76653 | Aoi      | Elec. Eng. | 60       | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 98765 | Bourikas | Elec. Eng. | 98       | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 98988 | Tanaka   | Biology    | 120      | 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 00128 | Zhang    | Comp. Sci. | 102      | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 12345 | Shankar  | Comp. Sci. | 32       | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 19991 | Brandt   | History    | 80       | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 23121 | Chavez   | Finance    | 110      | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 44553 | Peltier  | Physics    | 56       | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 45678 | Levy     | Physics    | 46       | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 54321 | Williams | Comp. Sci. | 54       | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 55739 | Sanchez  | Music      | 38       | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 70557 | Snow     | Physics    | 0        | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 76543 | Brown    | Comp. Sci. | 58       | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 76653 | Aoi      | Elec. Eng. | 60       | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 98765 | Bourikas | Elec. Eng. | 98       | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 98988 | Tanaka   | Biology    | 120      | 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 00128 | Zhang    | Comp. Sci. | 102      | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 19991 | Brandt   | History    | 80       | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 23121 | Chavez   | Finance    | 110      | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 44553 | Peltier  | Physics    | 56       | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 45678 | Levy     | Physics    | 46       | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 54321 | Williams | Comp. Sci. | 54       | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 55739 | Sanchez  | Music      | 38       | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 70557 | Snow     | Physics    | 0        | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 98765 | Bourikas | Elec. Eng. | 98       | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 98988 | Tanaka   | Biology    | 120      | 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 19991 | Brandt   | History    | 80       | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 23121 | Chavez   | Finance    | 110      | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 44553 | Peltier  | Physics    | 56       | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 45678 | Levy     | Physics    | 46       | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 54321 | Williams | Comp. Sci. | 54       | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 55739 | Sanchez  | Music      | 38       | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 70557 | Snow     | Physics    | 0        | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 98765 | Bourikas | Elec. Eng. | 98       | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 98988 | Tanaka   | Biology    | 120      | 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 19991 | Brandt   | History    | 80       | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 23121 | Chavez   | Finance    | 110      | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 44553 | Peltier  | Physics    | 56       | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 45678 | Levy     | Physics    | 46       | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 54321 | Williams | Comp. Sci. | 54       | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 55739 | Sanchez  | Music      | 38       | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 70557 | Snow     | Physics    | 0        | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 98765 | Bourikas | Elec. Eng. | 98       | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 98988 | Tanaka   | Biology    | 120      | 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 12345 | Shankar  | Comp. Sci. | 32       | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 19991 | Brandt   | History    | 80       | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 23121 | Chavez   | Finance    | 110      | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 44553 | Peltier  | Physics    | 56       | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 45678 | Levy     | Physics    | 46       | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 54321 | Williams | Comp. Sci. | 54       | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 55739 | Sanchez  | Music      | 38       | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 70557 | Snow     | Physics    | 0        | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 76543 | Brown    | Comp. Sci. | 58       | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 76653 | Aoi      | Elec. Eng. | 60       | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 98765 | Bourikas | Elec. Eng. | 98       | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 98988 | Tanaka   | Biology    | 120      | 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 00128 | Zhang    | Comp. Sci. | 102      | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 12345 | Shankar  | Comp. Sci. | 32       | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 19991 | Brandt   | History    | 80       | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 23121 | Chavez   | Finance    | 110      | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 44553 | Peltier  | Physics    | 56       | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 45678 | Levy     | Physics    | 46       | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 54321 | Williams | Comp. Sci. | 54       | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 55739 | Sanchez  | Music      | 38       | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 70557 | Snow     | Physics    | 0        | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 76543 | Brown    | Comp. Sci. | 58       | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 76653 | Aoi      | Elec. Eng. | 60       | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 98765 | Bourikas | Elec. Eng. | 98       | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 98988 | Tanaka   | Biology    | 120      | 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 00128 | Zhang    | Comp. Sci. | 102      | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 12345 | Shankar  | Comp. Sci. | 32       | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 19991 | Brandt   | History    | 80       | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 23121 | Chavez   | Finance    | 110      | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 44553 | Peltier  | Physics    | 56       | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 45678 | Levy     | Physics    | 46       | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 54321 | Williams | Comp. Sci. | 54       | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 55739 | Sanchez  | Music      | 38       | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 70557 | Snow     | Physics    | 0        | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 76543 | Brown    | Comp. Sci. | 58       | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 76653 | Aoi      | Elec. Eng. | 60       | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 98765 | Bourikas | Elec. Eng. | 98       | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 98988 | Tanaka   | Biology    | 120      | 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 00128 | Zhang    | Comp. Sci. | 102      | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 12345 | Shankar  | Comp. Sci. | 32       | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 19991 | Brandt   | History    | 80       | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 23121 | Chavez   | Finance    | 110      | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 44553 | Peltier  | Physics    | 56       | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 45678 | Levy     | Physics    | 46       | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 54321 | Williams | Comp. Sci. | 54       | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 55739 | Sanchez  | Music      | 38       | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 70557 | Snow     | Physics    | 0        | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 76543 | Brown    | Comp. Sci. | 58       | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 76653 | Aoi      | Elec. Eng. | 60       | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 98765 | Bourikas | Elec. Eng. | 98       | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 98988 | Tanaka   | Biology    | 120      | 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 00128 | Zhang    | Comp. Sci. | 102      | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 12345 | Shankar  | Comp. Sci. | 32       | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 19991 | Brandt   | History    | 80       | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 23121 | Chavez   | Finance    | 110      | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 44553 | Peltier  | Physics    | 56       | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 45678 | Levy     | Physics    | 46       | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 54321 | Williams | Comp. Sci. | 54       | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 55739 | Sanchez  | Music      | 38       | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 70557 | Snow     | Physics    | 0        | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 76543 | Brown    | Comp. Sci. | 58       | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 76653 | Aoi      | Elec. Eng. | 60       | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 98765 | Bourikas | Elec. Eng. | 98       | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 98988 | Tanaka   | Biology    | 120      | 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 00128 | Zhang    | Comp. Sci. | 102      | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 12345 | Shankar  | Comp. Sci. | 32       | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 19991 | Brandt   | History    | 80       | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 23121 | Chavez   | Finance    | 110      | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 44553 | Peltier  | Physics    | 56       | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 45678 | Levy     | Physics    | 46       | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 54321 | Williams | Comp. Sci. | 54       | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 55739 | Sanchez  | Music      | 38       | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 70557 | Snow     | Physics    | 0        | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 76543 | Brown    | Comp. Sci. | 58       | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 76653 | Aoi      | Elec. Eng. | 60       | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 98765 | Bourikas | Elec. Eng. | 98       | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 98988 | Tanaka   | Biology    | 120      | 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 00128 | Zhang    | Comp. Sci. | 102      | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 12345 | Shankar  | Comp. Sci. | 32       | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 19991 | Brandt   | History    | 80       | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 23121 | Chavez   | Finance    | 110      | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 44553 | Peltier  | Physics    | 56       | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 45678 | Levy     | Physics    | 46       | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 54321 | Williams | Comp. Sci. | 54       | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 55739 | Sanchez  | Music      | 38       | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 70557 | Snow     | Physics    | 0        | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 76543 | Brown    | Comp. Sci. | 58       | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 76653 | Aoi      | Elec. Eng. | 60       | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 98765 | Bourikas | Elec. Eng. | 98       | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 98988 | Tanaka   | Biology    | 120      | 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 00128 | Zhang    | Comp. Sci. | 102      | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 12345 | Shankar  | Comp. Sci. | 32       | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 19991 | Brandt   | History    | 80       | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 23121 | Chavez   | Finance    | 110      | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 44553 | Peltier  | Physics    | 56       | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 45678 | Levy     | Physics    | 46       | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 54321 | Williams | Comp. Sci. | 54       | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 55739 | Sanchez  | Music      | 38       | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 70557 | Snow     | Physics    | 0        | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 76543 | Brown    | Comp. Sci. | 58       | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 76653 | Aoi      | Elec. Eng. | 60       | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 98765 | Bourikas | Elec. Eng. | 98       | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 98988 | Tanaka   | Biology    | 120      | 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 00128 | Zhang    | Comp. Sci. | 102      | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 12345 | Shankar  | Comp. Sci. | 32       | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 19991 | Brandt   | History    | 80       | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 23121 | Chavez   | Finance    | 110      | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 44553 | Peltier  | Physics    | 56       | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 45678 | Levy     | Physics    | 46       | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 54321 | Williams | Comp. Sci. | 54       | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 55739 | Sanchez  | Music      | 38       | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 70557 | Snow     | Physics    | 0        | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 76543 | Brown    | Comp. Sci. | 58       | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 76653 | Aoi      | Elec. Eng. | 60       | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 98765 | Bourikas | Elec. Eng. | 98       | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 98988 | Tanaka   | Biology    | 120      | 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 00128 | Zhang    | Comp. Sci. | 102      | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 19991 | Brandt   | History    | 80       | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 23121 | Chavez   | Finance    | 110      | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 44553 | Peltier  | Physics    | 56       | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 45678 | Levy     | Physics    | 46       | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 54321 | Williams | Comp. Sci. | 54       | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 55739 | Sanchez  | Music      | 38       | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 70557 | Snow     | Physics    | 0        | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 98765 | Bourikas | Elec. Eng. | 98       | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 98988 | Tanaka   | Biology    | 120      | 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 19991 | Brandt   | History    | 80       | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 23121 | Chavez   | Finance    | 110      | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 44553 | Peltier  | Physics    | 56       | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 45678 | Levy     | Physics    | 46       | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 54321 | Williams | Comp. Sci. | 54       | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 55739 | Sanchez  | Music      | 38       | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 70557 | Snow     | Physics    | 0        | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 98765 | Bourikas | Elec. Eng. | 98       | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 98988 | Tanaka   | Biology    | 120      | 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 12345 | Shankar  | Comp. Sci. | 32       | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 19991 | Brandt   | History    | 80       | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 23121 | Chavez   | Finance    | 110      | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 44553 | Peltier  | Physics    | 56       | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 45678 | Levy     | Physics    | 46       | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 54321 | Williams | Comp. Sci. | 54       | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 55739 | Sanchez  | Music      | 38       | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 70557 | Snow     | Physics    | 0        | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 76543 | Brown    | Comp. Sci. | 58       | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 76653 | Aoi      | Elec. Eng. | 60       | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 98765 | Bourikas | Elec. Eng. | 98       | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 98988 | Tanaka   | Biology    | 120      | 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 00128 | Zhang    | Comp. Sci. | 102      | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 12345 | Shankar  | Comp. Sci. | 32       | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 19991 | Brandt   | History    | 80       | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 23121 | Chavez   | Finance    | 110      | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 44553 | Peltier  | Physics    | 56       | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 45678 | Levy     | Physics    | 46       | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 54321 | Williams | Comp. Sci. | 54       | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 55739 | Sanchez  | Music      | 38       | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 70557 | Snow     | Physics    | 0        | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 76543 | Brown    | Comp. Sci. | 58       | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 76653 | Aoi      | Elec. Eng. | 60       | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 98765 | Bourikas | Elec. Eng. | 98       | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 98988 | Tanaka   | Biology    | 120      | 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 00128 | Zhang    | Comp. Sci. | 102      | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 12345 | Shankar  | Comp. Sci. | 32       | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 19991 | Brandt   | History    | 80       | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 23121 | Chavez   | Finance    | 110      | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 44553 | Peltier  | Physics    | 56       | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 45678 | Levy     | Physics    | 46       | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 54321 | Williams | Comp. Sci. | 54       | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 55739 | Sanchez  | Music      | 38       | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 70557 | Snow     | Physics    | 0        | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 76543 | Brown    | Comp. Sci. | 58       | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 76653 | Aoi      | Elec. Eng. | 60       | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 98765 | Bourikas | Elec. Eng. | 98       | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 98988 | Tanaka   | Biology    | 120      | 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 00128 | Zhang    | Comp. Sci. | 102      | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 12345 | Shankar  | Comp. Sci. | 32       | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 19991 | Brandt   | History    | 80       | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 23121 | Chavez   | Finance    | 110      | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 44553 | Peltier  | Physics    | 56       | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 45678 | Levy     | Physics    | 46       | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 54321 | Williams | Comp. Sci. | 54       | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 55739 | Sanchez  | Music      | 38       | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 70557 | Snow     | Physics    | 0        | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 76543 | Brown    | Comp. Sci. | 58       | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 76653 | Aoi      | Elec. Eng. | 60       | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 98765 | Bourikas | Elec. Eng. | 98       | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 98988 | Tanaka   | Biology    | 120      | 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 00128 | Zhang    | Comp. Sci. | 102      | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
| 12345 | Shankar  | Comp. Sci. | 32       | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
| 19991 | Brandt   | History    | 80       | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
| 23121 | Chavez   | Finance    | 110      | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
| 44553 | Peltier  | Physics    | 56       | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
| 45678 | Levy     | Physics    | 46       | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
| 54321 | Williams | Comp. Sci. | 54       | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
| 55739 | Sanchez  | Music      | 38       | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
| 70557 | Snow     | Physics    | 0        | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
| 76543 | Brown    | Comp. Sci. | 58       | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
| 76653 | Aoi      | Elec. Eng. | 60       | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
| 98765 | Bourikas | Elec. Eng. | 98       | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
| 98988 | Tanaka   | Biology    | 120      | 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
+-------+----------+------------+----------+-------+-----------+--------+----------+------+-------+
286 rows in set (0.37 sec)

小结

  • 左外连接保留左边的元组,右边补null
  • 右外连接保留右边的元组,左边补null
  • 交换两个表的顺序后,左外连接可以转成右外连接,右外连接可以可以转成左外连接.
  • 不满足外连接的条件时补null

使用的表

本节的例子涉及student和takes两个关系,如下所示:

student关系内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select * from student;
+-------+----------+------------+----------+
| ID    | name     | dept_ame  | tot_cred |
+-------+----------+--------n----+----------+
| 00128 | Zhang    | Comp. Sci. | 102      |
| 12345 | Shankar  | Comp. Sci. | 32       |
| 19991 | Brandt   | History    | 80       |
| 23121 | Chavez   | Finance    | 110      |
| 44553 | Peltier  | Physics    | 56       |
| 45678 | Levy     | Physics    | 46       |
| 54321 | Williams | Comp. Sci. | 54       |
| 55739 | Sanchez  | Music      | 38       |
| 70557 | Snow     | Physics    | 0        |
| 76543 | Brown    | Comp. Sci. | 58       |
| 76653 | Aoi      | Elec. Eng. | 60       |
| 98765 | Bourikas | Elec. Eng. | 98       |
| 98988 | Tanaka   | Biology    | 120      |
+-------+----------+------------+----------+
13 rows in set (0.03 sec)

takes关系内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> select * from takes;
+-------+-----------+--------+----------+------+-------+
| ID    | course_id | sec_id | semester | year | grade |
+-------+-----------+--------+----------+------+-------+
| 00128 | CS-101    | 1      | Fall     | 2009 | A     |
| 00128 | CS-347    | 1      | Fall     | 2009 | A-    |
| 12345 | CS-101    | 1      | Fall     | 2009 | C     |
| 12345 | CS-190    | 2      | Spring   | 2009 | A     |
| 12345 | CS-315    | 1      | Spring   | 2010 | A     |
| 12345 | CS-347    | 1      | Fall     | 2009 | A     |
| 19991 | HIS-351   | 1      | Spring   | 2010 | B     |
| 23121 | FIN-201   | 1      | Spring   | 2010 | C+    |
| 44553 | PHY-101   | 1      | Fall     | 2009 | B-    |
| 45678 | CS-101    | 1      | Fall     | 2009 | F     |
| 45678 | CS-101    | 1      | Spring   | 2010 | B+    |
| 45678 | CS-319    | 1      | Spring   | 2010 | B     |
| 54321 | CS-101    | 1      | Fall     | 2009 | A-    |
| 54321 | CS-190    | 2      | Spring   | 2009 | B+    |
| 55739 | MU-199    | 1      | Spring   | 2010 | A-    |
| 76543 | CS-101    | 1      | Fall     | 2009 | A     |
| 76543 | CS-319    | 2      | Spring   | 2010 | A     |
| 76653 | EE-181    | 1      | Spring   | 2009 | C     |
| 98765 | CS-101    | 1      | Fall     | 2009 | C-    |
| 98765 | CS-315    | 1      | Spring   | 2010 | B     |
| 98988 | BIO-101   | 1      | Summer   | 2009 | A     |
| 98988 | BIO-301   | 1      | Summer   | 2010 | NULL  |
+-------+-----------+--------+----------+------+-------+
22 rows in set (0.06 sec)