table1
a1 b1 c1
1 a1 99
2 a2 98
3 a3 97
table2
a2 b2
1 aaa
2 sss
4 ddd
select table1.*,table2.* from table1 inner join table2 on (table1.a1 = table2.a2);
a1 b1 c1 a2 b2
1 a1 99 1 aaa
2 a2 98 2 sss
select table1.*,table2.* from table1 left outer join table2 on(table1.a1=table2.a2);
a1 b1 c1 a2 b2
1 a1 99 1 aaa
2 a2 98 2 sss
3 a3 97 null null
select table1.*,table2.* from table1 right outer join table2 on (table1.a1 = table2.a2);
a1 b1 c1 a2 b2
1 a1 99 1 aaa
2 a2 98 2 sss
null null null 4 ddd
★★★★★★★★★★
select table1.*,table2.* from table1 full outer join table2 on (table1.a1 = table2.a2);
至少在5.1。17中不支持FULL JOIN,用LEFT JOIN+RIGHT JOIN解决
select a.*,b.* from a left join b on a.id=b.id
union
select a.*,b.* from a right join b on a.id=b.id and a.id is null.