join 内外联

SQL中大概有这么几种 JOIN：

cross join(笛卡尔乘积）

inner join(在笛卡尔乘积的结果集中去掉不符合连接条件的行)

left join (左边的为主表，右边为从属表, 和left outer join 一样都是外连)

left outer join   (在inner join的结果集上加上左表中没被选上的行，行的右表部分每个字段都用NUll填充)

right join （右边的为主表，左边为从属表 ，和right outrt join 一样都是外连）

right outer join  (在inner join的结果集上加上右表中没被选上的行，行的左表部分全用NULL填充)

full outer join    (在inner join的结果集上加上左，右表中没被选上的行，左表时行的右表部分全用NULL填充
                           右表时行的左表部分全用NULL填充)

<--outer的意思就是"没有关联上的行"。-->

举例子：

表tb_student ,column 分别是ID, Name

1	sw
2	wy
3	wzm
4	lp
1	ws
5	xx
NULL	NULL

表tb_Course,column 分别是 ID, Name

1	语文
2	数学
3	外语
NULL	NULL

表tb_Grade ,column 分别是 StudentID, CourseId, Grade

1	2	90
2	1	60
3	1	80
4	3	70
1	1	77
1	3	74
2	2	72
NULL	NULL	NULL

select * from tb_student cross join tb_Grade
结果：
ID Name StudentID CourseID Grade
1    sw           1             2             90
1    sw           2             1             60
1    sw           3             1             80
1    sw           4             3             70
1    sw           1             1             77
1    sw           1             3             74
1    sw           2             2             72
1    sw           6             2             90
2    wy           1             2             90
2    wy           2             1             60
2    wy           3             1             80
2    wy           4             3             70
2    wy           1             1             77
2    wy           1             3             74
2    wy           2             2             72
2    wy           6             2             90
3    wzm        1             2             90
3    wzm        2             1             60
3    wzm        3             1             80
3    wzm        4             3             70
3    wzm        1             1             77
3    wzm        1             3             74
3    wzm        2             2             72
3    wzm        6             2             90
4    lp            1             2             90
4    lp            2             1             60
4    lp            3             1             80
4    lp            4             3             70
4    lp            1             1             77
4    lp            1             3             74
4    lp            2             2             72
4    lp            6             2             90
1    ws          1             2             90
1    ws          2             1             60
1    ws          3             1             80
1    ws          4             3             70
1    ws          1             1             77
1    ws          1             3             74
1    ws          2             2             72
1    ws          6             2             90
5    xx           1             2             90
5    xx           2             1             60
5    xx           3             1             80
5    xx           4             3             70
5    xx           1             1             77
5    xx           1             3             74
5    xx           2             2             72
5    xx           6             2             90

SELECT tb_Student.ID, tb_Student.Name, tb_Grade.Grade from
(tb_Grade INNER JOIN tb_Student
ON tb_Grade.StudentID = tb_Student.ID)
结果：
ID Name                      Grade
1 sw                               90
1 ws                               90
2 wy                              60
3 wzm                            80
4 lp                                70
1 sw                               77
1 ws                               77
1 sw                               74
1 ws                               74
2 wy                               72

SELECT tb_Student.ID, tb_Student.Name, tb_Grade.Grade from
(tb_Student INNER JOIN tb_Grade
ON tb_Grade.StudentID = tb_Student.ID)
结果：
ID Name                      Grade
1 sw                               90
1 sw                               77
1 sw                               74
2 wy                               60
2 wy                               72
3 wzm                              80
4 lp                               70
1 ws                               90
1 ws                               77
1 ws                               74

SELECT tb_Student.ID, tb_Student.Name, tb_Grade.Grade from
(tb_Student left  JOIN tb_Grade
ON tb_Grade.StudentID = tb_Student.ID)
和
SELECT tb_Student.ID, tb_Student.Name, tb_Grade.Grade from
(tb_Student left outer JOIN tb_Grade
ON tb_Grade.StudentID = tb_Student.ID)
结果：
ID Name                      Grade
1 sw                               90
1 sw                               77
1 sw                               74
2 wy                               60
2 wy                               72
3 wzm                              80
4 lp                               70
1 ws                               90
1 ws                               77
1 ws                               74
5 xx                               NULL

SELECT tb_Student.ID, tb_Student.Name, tb_Grade.Grade from
(tb_Student right JOIN tb_Grade
ON tb_Grade.StudentID = tb_Student.ID)
和
SELECT tb_Student.ID, tb_Student.Name, tb_Grade.Grade from
(tb_Student right outer JOIN tb_Grade
ON tb_Grade.StudentID = tb_Student.ID)
结果：
ID Name                      Grade
1 sw                               90
1 ws                               90
2 wy                               60
3 wzm                              80
4 lp                               70
1 sw                               77
1 ws                               77
1 sw                               74
1 ws                               74
2 wy                               72
NULL NULL 90

SELECT tb_Student.ID, tb_Student.Name, tb_Grade.Grade from
(tb_Student full JOIN tb_Grade
ON tb_Grade.StudentID = tb_Student.ID)
和
SELECT tb_Student.ID, tb_Student.Name, tb_Grade.Grade from
(tb_Student full outer JOIN tb_Grade
ON tb_Grade.StudentID = tb_Student.ID)
结果：
ID Name                      Grade
1 sw                               90
1 sw                               77
1 sw                               74
2 wy                               60
2 wy                               72
3 wzm                              80
4 lp                               70
1 ws                               90
1 ws                               77
1 ws                               74
5 xx                               NULL
NULL   NULL 90

转载于:https://www.cnblogs.com/sw22225458/archive/2007/04/26/728148.html