MySQL 连接join

 

 

 上图是sql的连接 其中mysql不支持 全外连接(full outer join outer)

 

右外连接(rignt outer join，outer可以省略)

SELECT *
FROM table_a a
INNER JOIN table_b b on a.aid = b.aid

 

 

左外连接（left outer join，outer可以省略)

SELECT *
FROM table_a a
LEFT OUTER JOIN table_b b on a.aid = b.aid

 

 

SELECT *
FROM table_a a
LEFT JOIN table_b b on a.aid = b.aid where b.aid is null

 

 

右外连接(rignt outer join，outer可以省略)

SELECT *
FROM table_a a
RIGHT OUTER JOIN table_b b on a.aid = b.aid

 

 

SELECT *
FROM table_a a
RIGHT JOIN table_b b on a.aid = b.aid where a.aid is null

 

 

附录:

sql脚本

CREATE TABLE `table_a` (
  `aid` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `table_b` (
  `bid` int(11) NOT NULL,
  `aid` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `demo`.`table_a` (`aid`, `name`) VALUES ('1', 'name1');
INSERT INTO `demo`.`table_a` (`aid`, `name`) VALUES ('2', 'name2');
INSERT INTO `demo`.`table_a` (`aid`, `name`) VALUES ('3', 'name3');

INSERT INTO `demo`.`table_b` (`bid`, `aid`, `name`) VALUES ('1', '1', 'name1');
INSERT INTO `demo`.`table_b` (`bid`, `aid`, `name`) VALUES ('2', '2', 'name2');
INSERT INTO `demo`.`table_b` (`bid`, `aid`, `name`) VALUES ('4', '4', 'name4');
INSERT INTO `demo`.`table_b` (`bid`, `aid`, `name`) VALUES ('5', '1', 'name12');
INSERT INTO `demo`.`table_b` (`bid`, `aid`, `name`) VALUES ('6', '1', 'name13');