连接查询

一：sql92标准

#进阶7：连接查询
/*
含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。

笛卡尔乘积现象：表1有m行，表2有n行，结果集=m*n行。
   发生原因：没有有效的连接条件
   如何避免：添加有效的连接条件

连接查询分类：

按年代分类：
   (1)sql92标准
   (2)sql99标准
按功能分类：
   (1)内连接：
	等值连接
	非等值连接
	自连接
   (2)外连接：
	左外连接
	右外连接
	全外连接
   (3)交叉连接：
*/

#sql92标准
#一：等值连接
/*
	语法：
	   select 查询列表
	   from 表名1 (AS) 别名1,表名2 (AS) 别名2,……
	   where 等值连接的连接条件
     AND 筛选条件
	为表起别名的好处：
	   提高语句的简洁度
	   区分多个重名的字段
	注意：
	   如果为表起了别名，则查询的字段就不能使用原来的表名去限定
	   多个筛选条件之间用AND连接
	   多表等值连接的结果为多表的交集部分
	   n表连接，至少需要n-1个连接的条件
	   多表的顺序没有要求
	   一般需要为表起别名
	   可以搭配前面介绍的所有子句
*/
#案例一：查询女神名和对应的男神名
select `name`,boyName 
from boys,beauty
where beauty.boyfriend_id = boys.id;

#二：非等值连接
#案例一：查询员工的工资和工资级别
select salary,grade_level
from employees e,job_grades g
where salary between g.`lowest_sal` and g.`highest_sal`;

#二：自连接（自己连接自己）
#案例一：查询员工名和上级的名称
select e.employee_id,e.last_name,m.employee_id,m.last_name
from employees e,employees m
where e.`manager_id`=m.`employee_id`;

二：sql99标准

(1)内连接

#二：sql99语法
/*
语法：
	select 查询列表
	from 表1 别名
	【连接类型】 join 表2 别名
	on 连接条件
	【where 筛选条件】
	【group by 分组】
	【having 筛选条件】
	【order by 排序列表】
分类：
    内连接(*)：inner
	等值连接
	非等值连接
	自连接
    外连接
	左外(*):left 【outer】
	右外(*):right 【outer】
	全外:full 【outer】
    交叉连接:cross
*/

#一:）内连接
/*
语法：
	select 查询列表
	from 表1 别名
	inner join 表2 别名
	on 连接条件
	inner join 表3 别名
	on 连接条件
特点：
    1.添加排序、分组、筛选
    2.inner可以省略
    3.筛选条件放在where后面，连接条件放在on后面，提高分离性、便于阅读
    4.inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
*/
#1:等值连接
#案例一：查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;

#2:非等值连接
#案例一：查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#3：自连接
#案例一：查询员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`;

(2)外连接

#二：外连接
/*
应用场景：用于查询一个表中有，另一个表中没有的记录
特点：
   1.外连接的查询结果为主表中的所有记录
      如果从表中有和它匹配的，则显示匹配的值
      如果从表中没有和它匹配的，则显示null
      外连接查询结果=内连接结果+主表中有而从表中没有的记录
   2.左外连接，left左边的是主表
     右外连接，right join 右边的是主表
   3.左外和右外交换两个表的顺序，可以实现同样的效果
   4.全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1中没有的
*/

#1)左/右外连接
#案例一：查询
SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;

#2)全外连接
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`;

(3)交叉连接

#三：交叉连接(其实就是笛卡尔乘积)
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;