1、表ut
表 b
2.left join
SELECT * FROM ut left join b on ut.name=b.name order by ut.id
3.right join
SELECT * FROM ut right join b on ut.name=b.name order by ut.id;
4.full join
SELECT * FROM ut full join b on ut.name=b.name order by ut.id;
5.inner join
SELECT * FROM ut inner join b on ut.name=b.name order by ut.id;
6.cross join
CROSS JOIN 把表A和表B的数据进行一个N*M的组合,即笛卡尔积。如本例会产生6*3=16条记录,在开发过程中我们肯定是要过滤数据,所以这种很少用。
SELECT * FROM B CROSS JOIN UT;
7.并集 union 会去重、效率会降低 ;union all
SELECT * FROM ut union SELECT * FROM b;
8、差集 minus(oracle)、except(sql server)
SELECT Name FROM ut minus SELECT Name FROM b
9、INTERSECT 交集
SELECT name FROM ut
INTERSECT
SELECT name FROM b