SQL Joins Explained with Tables

Let’s consider three tables: 学生, 选课, and 课程.

学生表 (Students)

Now, let’s look at different types of joins:

sql

SELECT 学生.姓名, 课程.课程名称
FROM 学生
INNER JOIN 选课 ON 学生.学号 = 选课.学号
INNER JOIN 课程 ON 选课.课程编号 = 课程.课程编号;

Result:

Explanation: This returns only the rows where there’s a match in all three tables.

sql

SELECT 学生.姓名, 课程.课程名称
FROM 学生
LEFT JOIN 选课 ON 学生.学号 = 选课.学号
LEFT JOIN 课程 ON 选课.课程编号 = 课程.课程编号;

Result:

Explanation: This returns all students, even if they haven’t selected any courses.

sql

SELECT 学生.姓名, 课程.课程名称
FROM 学生
RIGHT JOIN 选课 ON 学生.学号 = 选课.学号
RIGHT JOIN 课程 ON 选课.课程编号 = 课程.课程编号;

Result:

Explanation: This returns all courses, even if no student has selected them.

sql

SELECT 学生.姓名, 课程.课程名称
FROM 学生
FULL OUTER JOIN 选课 ON 学生.学号 = 选课.学号
FULL OUTER JOIN 课程 ON 选课.课程编号 = 课程.课程编号;

Result:

Explanation: This returns all rows from all tables, using NULL where there is no match.