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