先创建两个表
create t1(id int, name varchar(10)); create t2(id int, score varchar(10)); insert into t1 values((1,lee),(2,zhang),(4,wang)); insert into t2 values((1,90),(2,100),(3,70));
用 left join和left outer join 几乎没有什么区别,都是返回左边表的所有和右边表中符合条件的记录
select * from t1 left join t2 on t1.id=t2.id;
用right join 和left join 相反
select * from t1 right join t2 on t1.id=t2.id
用full join 是返回两个表的所有记录,相同Id的则合并为一条,不同的则分开
select * from t1 full join t2 on t1.id=t2.id
用cross join(即笛卡尔积),不带条件
select * from t1 cross join t2;
用inner join 有点和full join感觉有点相反的记录,返回的是两边都符合条件的记录
select * from t1 inner t2 on t1.id=t2.id;