1、交叉联接(cross join)
select * from t.toy ,b.boy from toy as t cross join boy as b
其效果同select * from t.toy ,b.boy from toy as t boy as b,cross join 返回两张表每一行相乘的结果
2、内联接就是通过查询中的条件移除了某些结果数据行后的交叉联接
相等联接(equijoin)
select toys.toy boys.boy from toys inner join boys on boys.id = toys.id
测试相等性的内联接
3、不等联接(non-equijion)
select toys.toy boys.boy from toys inner join boys on boys.id <> toys.id
测试不相等性的内联接
4、自然联接(natual join)
select toys.toy boys.boy from toys natual join boys
利用相等列名的内联接
5、左外连接(left outer join)会匹配左表中每一行与右表中符合条件的行
select g.girl t.toy from girls as g lef outer join toys as t on g.toy_id = t.toy.
内联接与外联接的区别是:外联接一定会提供数据行,无论另一个表中是否找到相匹配的行。出现null是告诉我们没有相匹配的行
select g.girl t.toy from girls as g left outer join toys as t on g.toy_id = t.toy_id
6、右外联接(right outer join):就是左外连接左右表的调换
7、自连接(self join)
select c1.name c2.name from clown_info c1 inner join clown_info c2 on c1.id = c2.class_id