a表取经四人组 b表悟空的兄弟
1.Join操作的类型–Inner Join
內连接Inner join 基于连接谓词将两张表(如A和B)的列组合在一起,产生新的结果表。
产生的结果集如(图一)红色区域
例如:同时存在与取经四人组中的和悟空的兄弟表中的记录为:
SELECT a.user_name,a.over,b.over FROM a inner JOIN b ON a.user_name=b.user_name
2.Join操作的类型–Left Outer Join
SELECT a.user_name,a.over,b.over FROM a LEFT JOIN b ON a.user_name=b.user_name;
例:查询取经四人组中哪些不是孙悟空的兄弟
SELECT a.user_name,a.over,b.over FROM a LEFT JOIN b ON a.user_name=b.user_name WHERE b.user_name is NULL;
3.Join操作的类型–Right Outer Join
SELECT b.user_name,b.over,a.over FROM a RIGHT JOIN b ON a.user_name=b.user_name ;
例如:悟空的结拜兄弟中哪些没有去取经
SELECT b.user_name,b.over,a.over FROM a RIGHT JOIN b ON a.user_name=b.user_name WHERE a.user_name IS NULL;
4.Join操作的类型–Full Join
SELECT b.'user_name',b.'over',a.'over' FROM a FULL JOIN b ON a.'user_name'=b.'user_name'
在MYSQL中执行会报错,MYSQL**不支持Full Join** 通过UNION ALL实现。
SELECT a.user_name,a.over,b.over FROM a LEFT JOIN b ON a.user_name=b.user_name
UNION ALL
SELECT b.user_name,b.over,a.over FROM a RIGHT JOIN b ON a.user_name=b.user_name
5.Join操作的类型–Corss Join 交叉查询得到的结果为两个表的记录相乘的结果集 笛卡尔基
SELECT a.user_name,a.over,b.user_name,b.over FROM a CROSS JOIN b