左连接,右连接,内连接和全连接的区别
学生表
教师表
左连接:以左表为基准,左边有数据,右边没有数据,则显示查询左表所有结果,右边内容有显示无为null
左边无数据,右边无论有无数据,均无查询结果
select student.* , teacher.* FROM student LEFT JOIN teacher ON student.id = teacher.id;
右连接:以右表为基准,右边有数据,右边没有数据,则显示查询右表结果,左边内容有显示无为null
右边无数据,左边无论有无数据,均无查询结果
select student.* , teacher.* FROM student RIGHT JOIN teacher ON student.id = teacher.id;
内连接:查询结果为两张表都有的部分
select student.* , teacher.* FROM student JOIN teacher ON student.id = teacher.id;
全外连接:两张表所有的内容均可查询出来 mysql暂不支持,所以用union来实现
mysql写法:
select student.*,teacher.* from student LEFT JOIN teacher ON student.id = teacher.id
UNION
select student.*,teacher.* from student RIGHT JOIN teacher ON student.id = teacher.id;
支持全连接的数据库写法:
select student.* , teacher.* FROM student FULL JOIN teacher ON student.id = teacher.id;