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');