sql联接那点儿事儿

时间:2023-01-09 20:42:09

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

  sql联接那点儿事儿

  利用相等列名的内联接

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.

  sql联接那点儿事儿

  内联接与外联接的区别是:外联接一定会提供数据行,无论另一个表中是否找到相匹配的行。出现null是告诉我们没有相匹配的行

  select g.girl t.toy from girls as g left outer join toys as t on g.toy_id = t.toy_id

  sql联接那点儿事儿

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