1、JOIN(内连接)
inner join可以简写为join,连接分为两种:内连接与外连接。
内连接,即最常见的等值连接,例:
SELECT
student.studentNo,
student.studentName,
student.native,
student.classNo,
class.className
FROM
student
JOIN class ON student.classNo = class.classNo
AND class.institute = '会计学院'
等价于
SELECT
student.studentNo,
student.studentName,
student.native,
student.classNo,
class.className
FROM
student,
class
WHERE
student.classNo = class.classNo
AND class.institute = '会计学院'
SELECT
student.studentNo,
student.studentName
FROM
student
INNER JOIN course ON course.courseName = '计算机原理'
INNER JOIN score ON score.studentNo = student.studentNo AND score.courseNo = course.courseNo
2、LEFT JOIN(左外连接)
左外连接
left outer join
或者
left join
SELECT
student.studentNo,
student.studentName,
student.native,
student.classNo,
class.className
FROM
student
LEFT JOIN class ON student.classNo = class.classNo
AND class.institute = '会计学院'
注释:
LEFT JOIN 关键字从左表(Student)返回所有的行,即使右表(class)中没有匹配。
3、RIGHT JOIN(右外连接)
右外连接
right outer join
或者
right join
SELECT
student.studentNo,
student.studentName,
student.native,
student.classNo,
class.className
FROM
student
RIGHT JOIN class ON student.classNo = class.classNo
AND class.institute = '会计学院'
RIGHT JOIN 关键字从右表(Student)返回所有的行,即使左表(class)中没有匹配。
3、FULL JOIN(全外连接)
全外连接
full outer join
或者
full join