左连接

关联查询

SELECT
  *
FROM
  TableA LEFT  JOIN
  TableB
  ON condition;

SELECT
  *
FROM
  ykd_teacher
  LEFT JOIN ykd_course ON ykd_teacher.id = ykd_course.teacher_id;

SELECT
  ykd_student.id,
  ykd_student.name,
  ykd_student.birthday,
  ykd_student.course_id,
  ykd_student.gender,
  ykd_course.id,
  ykd_course.name
FROM
  ykd_student
  LEFT JOIN ykd_course ON ykd_student.course_id = ykd_course.id;

表结构设计规范

右连接

右连接就是返回右表的所有数据，即使左表没有匹配的数据。

SELECT
  *
FROM
  TableA RIGHT JOIN 
  TableB 
  ON condition;

查询课程对应的老师（不管课程有没有对应的老师，都要展示课程信息。）

SELECT
  *
FROM
  ykd_teacher
  RIGHT JOIN ykd_course ON ykd_teacher.id = ykd_course.teacher_id;

多表关联查询

语法

SELECT
  *
FROM
  TableA LEFT JOIN 
  TableB ON conditionA
  LEFT JOIN
  TableB
  ON conditionB;

比如我们要查询三张表

SELECT
  *
FROM
  ykd_course
  LEFT JOIN ykd_student ON ykd_student.course_id = ykd_course.id
  LEFT JOIN ykd_teacher on ykd_course.teacher_id = ykd_teacher.id;

多表查询的本质是，A和B先关联，然后再和C关联查询，以此类推。

老王需要小王用一条SQL语句的关联查询三张表，用左连接查询课程对应的学生和老师（不管有没有对应的血神或老师，优先展示课程信息），均以ykd_course为左表。

SELECT
  ykd_student.name AS "学生",
  ykd_student.birthday,
  ykd_student.gender,
  ykd_course.id,
  ykd_course.name AS "课程名",
  ykd_teacher.name AS "授课老师"
FROM
  ykd_course
  LEFT JOIN ykd_student ON ykd_student.course_id = ykd_course.id
  LEFT JOIN ykd_teacher on ykd_course.teacher_id = ykd_teacher.id;

内连接

SELECT
  *
FROM
  Table_A
  INNER JOIN Table_B
ON
  Table_A.id = Table_B.student_id;

外连接的拓展（左右连接）

查询后的数据：


实际应用中我们希望查询A中和B完全没有关系的数据,比如查询班级A中没有参与B考试的人员。

SELECT
  *
FROM
  Table_A
  LEFT JOIN Table_B ON Table_A.id = Table_B.student_id
WHERE
  Table_B.student_id IS NULL;

UNION关键字

有时候需要联合查询所有的内容。

查询A中所有的学生和B中所有的学生，不管他们是否参与了考试。有没有成绩。

SELECT
  *
FROM
  Table_A
  LEFT JOIN Table_B
ON
  Table_A.id = Table_B.student_id
UNION DISTINCT
SELECT
  *
FROM
  Table_A
  RIGHT JOIN Table_B ON Table_A.id=Table_B.student_id;