1、内连接:inner join
两张表的并集,也就是A表和B表都必须有数据才能查询出来
-- join
select * from course c join teacher t on c.t_id = t.t_id
-- inner join
select * from course c inner join teacher t on c.t_id = t.t_id
-- 逗号的连表方式就是内连接
select * from course c , teacher t where c.t_id = t.t_id
“inner join”两边的表的位置可以互换,结果都一样
2、外连接:outer join
2.1、左(外)连接:left [outer] join
左连接从左表取出所有记录,与右表匹配。如果没有匹配,以null值代表右边表的列
-- left join
select * from course c left join teacher t on c.t_id = t.t_id
-- left outer join
select * from course c left outer join teacher t on c.t_id = t.t_id
“left join”两边的表的位置不可以互换,交换后结果可能不一样。需要考虑好哪个是主表,哪个是从表。写在前面的是主表
2.2、 右(外)连接:right [outer] join
右连接从右表取出所有记录,与左表匹配。如果没有匹配,以null值代表左边表的列
-- right join
select * from course c right join teacher t on c.t_id = t.t_id
-- right outer join
select * from course c right outer join teacher t on c.t_id = t.t_id
3、全连接:full [outer] join
两个表的并集,MySQL暂不支持这种语句,不过可以使用union将两个结果集“堆一起”,利用左连接,右连接分两次将数据取出,然后用union将数据合并去重
-- mysql的全连接
-- mysql中没有full join,mysql可以使用union实现全连接;
select * from a left join b on a.id = b.id
union
select * from a right join b on a.id = b.id