SQL 自连接和非自连接,内连接和外连接

#06章 多表查询

/*
SELECT....,....,....,
FROM....
WHERE...AND / OR /NOT ...
ORDER BY ..... (ASC/DESC),...,....
LIMIT....，.....
*/


DESC employees

DESC departments

DESC locations    

 

#查询员工名为‘Abel’的人在哪个城市工作
SELECT * 
FROM employees
WHERE last_name = 'Abel'

SELECT * 
FROM departments·
WHERE department_id = 80



SELECT * 
FROM locations
WHERE location_id = '2500'


#2 . 出现笛卡尔积（或交叉连接）的错误
# 错误的原因:缺少了多表的连接条件

#错误的实现方式:每个员工都和每个部门匹配了一遍出现了错误

SELECT employee_id, department_name
FROM employees,departments; #查询出2889条记录


SELECT * 
FROM employees #107 条记录



#错误的实现方式:
SELECT employee_id, department_name
FROM employees  CROSS JOIN departments;


#3.多表查询的正确方式，需要有连接条件

SELECT employee_id, department_name 
FROM employees,departments
#两个表的连接条件
WHERE employees.`department_id` = departments.`department_id`;


#4.如果查询语句中出现了多个表都存在的字段，则必须指明此字段所在的表

SELECT  employees.employee_id, departments.department_name ,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

#建议：从sql优化的角度，建议多表查询时候，每个字段前都指明其所在的表

#5.可以给表其别名,在SELECT 和WHERE中使用表的别名

SELECT  t1.employee_id, t2.department_name ,t1.department_id
FROM employees t1,departments t2
WHERE t1.`department_id` = t2.`department_id`;


# 6.如果有n个表实现多表查询，则至少需要n-1个连接条件
#练习查询 员工employee_id, last_name ,department_name,  city

SELECT  t1.employee_id, t1.last_name, t2.department_name, t3.city
FROM employees t1 , departments t2, locations t3
WHERE t1.`department_id` = t2.`department_id`
AND t2.`location_id` = t3.`location_id`


/*
演绎式:提出问题1 -----> 解决问题1 ------> 提出问题2 -----> 解决问题2

归纳式：总 -- 分

*/




#7.多表查询的分类

/*
角度1 : 等值连接 vs 非等值连接

角度2: 自连接 vs 非自连接

角度3: 内连接 vs 外连接




*/


# 7.1 等值连接 vs 非等值连接
            
#非等值连接的例子: 员工工资等级，在区间内进行划分

SELECT * 
FROM job_grades;        
    

SELECT t1.last_name ,t1.salary , t2.grade_level
FROM employees t1, job_grades t2
#WHERE t1.`salary` BETWEEN t2.`lowest_sal` AND t2.`highest_sal`
WHERE t1.`salary` >= t2.`lowest_sal` AND t1.`salary`<= t2.`highest_sal`
ORDER BY  grade_level  ASC





# 7.1 自连接 vs 非自连接

SELECT *FROM employees;

#自连接例子
#练习：查询员工id, 员工姓名及管理这id和姓名，在同一张表下查询

SELECT t1.employee_id , t1.last_name , t2.employee_id , t2.last_name
FROM employees t1 ,employees t2
WHERE  t1.`manager_id` = t2.`employee_id`





#7.3内连接  和  外连接

#内连接:     合并具有同一列的两个以上表的行, 结构集中不包含一个表与另一个表不匹配的行

SELECT employee_id, department_name 
FROM employees t1,departments t2
WHERE t1.`department_id` = t2.`department_id`;

# 外连接: 合并具有同一列的两个以上表的行, 结构集中除了包含一个表与另一个表不匹配的行之外
#                        还查询到了 左表 或 右表中不匹配的行

#外连接的分类:左外连接、右外连接、满外连接

# 左外连接: 两个表在连接过程中, 除了返回满足连接条件的行以外还返回左表中不满足条件的行, 这种连接称为左外连接
#    右外连接: 两个表在连接过程中, 除了返回满足连接条件的行以外还返回右表中不满足条件的行, 这种连接称为右外连接

# 查询所有员工的last_name, department_name信息 (查所有的信息，是外连接！！！)

SELECT employee_id, department_name 
FROM employees t1,departments t2
WHERE t1.`department_id` = t2.`department_id`;

#SQL 92语法实现内连接: 见上， 略
#SQL 92语法实现外连接 : 使用 + ---------MYSQL 不支持SQL 92语法中外连接的写法 oracle支持！

SELECT employee_id, department_name 
FROM employees t1,departments t2
WHERE t1.`department_id` = t2.`department_id`(+);


#SQL99语法中使用JOIN ... ON 的方式实现多表查询, 这种方式也能解决外连接的问题, mysql是支持这中方式的
#SQL99语法如何实现多表查询的


#SQL99语法如何实现内连接
SELECT employee_id, department_name 
FROM employees t1     JOIN departments t2
ON t1.`department_id` = t2.`department_id`;

#SQL 92 语法实现内连接

SELECT  t1.employee_id, t1.last_name, t2.department_name, t3.city
FROM employees t1 , departments t2, locations t3
WHERE t1.`department_id` = t2.`department_id`
AND t2.`location_id` = t3.`location_id`


SELECT * FROM locations


#SQL 99 语法实现内连接

SELECT last_name, department_name,city 
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;


#SQL 99 语法实现外连接
#查询所有员工的last_name, department_name信息 (查所有的信息，是外连接！！！)

#右外连接：
SELECT  last_name, department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id`  = d.`department_id`

#左外连接：
SELECT  last_name, department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`


#满外连接：MYSQL 不支持 FULL OUTER JOIN
SELECT  last_name, department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;