sql左外连接、右外连接、全外连接

一 语法

1 左外连接 LEFT  JOIN或LEFT OUTER JOIN 

2 右外连接  RIGHT  JOIN 或 RIGHT  OUTER  JOIN

3  全外连接  FULL  JOIN 或 FULL OUTER JOIN   mysql不支持全外连接 等价方式 FULL  JOIN  =  LEFT  JOIN + RIGHT  JOIN 

二 demo

两张表，表结构如下

student 表

CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL DEFAULT '',
  `s_name` varchar(30) DEFAULT NULL,
  `t_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

teacher表

CREATE TABLE `teacher` (
  `t_id` varchar(20) NOT NULL DEFAULT '',
  `t_name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

说明 下面查询中出现的student0  student1  student3表结构均与student一样 ，student0  、student2  、student3分别包含有0、1、3条teacherid

表含数据

student3

student1

student0

teacher

表为teacher student3

左连接

SELECT
	s.s_id AS `学生id`,
	s.s_name AS `学生姓名`,
	t.t_id AS `老师id`,
	t.t_name AS `老师姓名`
FROM
	student3 AS s
LEFT JOIN teacher  as t ON s.t_id = t.t_id

右连接

SELECT
	s.s_id AS `学生id`,
	s.s_name AS `学生姓名`,
	t.t_id AS `老师id`,
	t.t_name AS `老师姓名`
FROM
	student3 AS s
right JOIN teacher  as t ON s.t_id = t.t_id

外连接

SELECT
	s.s_id AS `学生id`,
	s.s_name AS `学生姓名`,
	t.t_id AS `老师id`,
	t.t_name AS `老师姓名`
FROM
	student3 AS s
INNER  JOIN teacher  as t ON s.t_id = t.t_id

表为teacher student1

左连接

SELECT
	s.s_id AS `学生id`,
	s.s_name AS `学生姓名`,
	t.t_id AS `老师id`,
	t.t_name AS `老师姓名`
FROM
	student1 AS s
LEFT JOIN teacher  as t ON s.t_id = t.t_id

右连接

SELECT
	s.s_id AS `学生id`,
	s.s_name AS `学生姓名`,
	t.t_id AS `老师id`,
	t.t_name AS `老师姓名`
FROM
	student1 AS s
right JOIN teacher  as t ON s.t_id = t.t_id

外连接

SELECT
	s.s_id AS `学生id`,
	s.s_name AS `学生姓名`,
	t.t_id AS `老师id`,
	t.t_name AS `老师姓名`
FROM
	student1 AS s
INNER  JOIN teacher  as t ON s.t_id = t.t_id

表为teacher student0

左连接

SELECT
	s.s_id AS `学生id`,
	s.s_name AS `学生姓名`,
	t.t_id AS `老师id`,
	t.t_name AS `老师姓名`
FROM
	student0 AS s
LEFT JOIN teacher  as t ON s.t_id = t.t_id

右连接

SELECT
	s.s_id AS `学生id`,
	s.s_name AS `学生姓名`,
	t.t_id AS `老师id`,
	t.t_name AS `老师姓名`
FROM
	student0 AS s
right JOIN teacher  as t ON s.t_id = t.t_id

外连接

SELECT
	s.s_id AS `学生id`,
	s.s_name AS `学生姓名`,
	t.t_id AS `老师id`,
	t.t_name AS `老师姓名`
FROM
	student0 AS s
INNER  JOIN teacher  as t ON s.t_id = t.t_id