mysql中各种常见join连表查询实例总结

CREATE TABLE `a` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `a_name` varchar(32) DEFAULT '' COMMENT 'a表名称',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `b` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `a_id` int(11) DEFAULT '0' COMMENT 'a表ID',
 `b_name` varchar(32) DEFAULT '' COMMENT 'b表名称',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

select * from a inner join b on a.id = b.a_id;
select * from a join b on a.id = b.a_id;
select * from a, b where a.id = b.a_id;

select * from a left join b on a.id = b.a_id;
select * from a left outer join b on a.id = b.a_id;

select * from a left join b on a.id = b.a_id where b.a_id is not null;
select * from a left outer join b on a.id = b.a_id where b.a_id is not null;

select * from a left join b on a.id = b.a_id where b.a_id is null;
select * from a left outer join b on a.id = b.a_id where b.a_id is null;

select * from a right join b on a.id = b.a_id;
select * from a right outer join b on a.id = b.a_id;

select * from a left join b on a.id = b.a_id
union
select * from a right join b on a.id = b.a_id;

select * from a left join b on a.id = b.a_id where b.a_id is null
union
select * from a right join b on a.id = b.a_id where a.id is null;

select * from a left join b on a.id = b.a_id where b.a_id is not null
union
select * from a right join b on a.id = b.a_id where a.id is not null;

select * from a cross join b;
select * from a, b;