MySQL中的多表查询(超级详细)

MySQL中的多表查询

多表查询的分类

• 等值连接—— 非等值连接
• 自连接 —— 非自连接
• 内连接 —— 外连接

# 等值连接 vs 非等值连接
# 非等值连接的例子
SELECT* FROM job_grades;

SELECT last_name, salary,grade_level
FROM employees e, job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`

# 自连接 vs 非自连接
 SELECT * FROM employees;
 
 # 自连接
 # 查询员工id,员工姓名及其管理者的id和姓名
 SELECT emp.employee_id, emp.last_name, mgr.employee_id ,mgr.last_name
 FROM employees emp, employees mgr
 WHERE emp.`manager_id` = mgr.`employee_id`
 
 # 内连接 vs 外连接
 # 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
 SELECT employee_id, department_name
 FROM employees e, departments d
 WHERE e.`department_id` = d.`department_id`
 
 # 外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外
 #     还查询到了坐标或右表不匹配的行。
 
 # 外连接的分类：左外连接、右外连接、满外连接

 # 左外连接：两个表在连接过程中除了瞒住连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接
 
 # 右外连接：两个表在连接过程中除了瞒住连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接
 
 # 查询所有员工的last_name,department_name信息
 # SQL92语法实现内连接 
 SELECT employee_id, department_name
 FROM employees e, departments d
 WHERE e.`department_id` = d.`department_id`
 # SQL92语法实现外连接  使用+ ————MySQL中不支持SQL92语法中的外连接的写法
 SELECT employee_id, department_name
 FROM employees e, departments d
 WHERE e.`department_id` = d.`department_id`(+)
 # SQL99语法中使用JOIN...ON的方式实现多表的查询，这种方式也能拿个解决外连接的问题，MySQL是支持此种方式的
 # SQL99语法如何实现多表的查询
 
 # SQL99语法实现内连接
 SELECT last_name,department_name
 FROM employees JOIN departments 
 ON employees.`department_id` = departments.`department_id`
 
 # SQL99语法实现左外连接：
  SELECT last_name,department_name
 FROM employees LEFT JOIN departments 
 ON employees.`department_id` = departments.`department_id`
 
  # SQL99语法实现右外连接：
  SELECT last_name,department_name
  FROM employees RIGHT JOIN departments 
  ON employees.`department_id` = departments.`department_id`
 
 #满外连接：mysql不支持 FULL OUTER JOIN
  SELECT last_name,department_name
  FROM employees FULL OUTER JOIN departments 
  ON employees.`department_id` = departments.`department_id`
 
 # SQL99语法实现满外连接:UNION [ALL] 的使用
 # union：会执行去重的操作
 # union all：不会执行去重的操作

UNION的使用

合并查询结果

利用UNION关键字可以给出多条SELECT语句，并将他们的结果组合成单个结果集。合并时，两个表对应的列数和数据必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

语法格式：
SELECT column,... from table1
UNION [ALL]
SELECT column,... from table2

UNION操作符返回两个查询的结果的并集，取出重复记录

UNION ALL操作符返回两个查询的结果集的并集，对于两个结果集的重复的部分，不去重。

如果明确知道合并后的数据不存在重复的诗句，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率

SQL99语法中的新特性1：自然连接

SQL99在SQL92基础上提供了一些特殊语法，比如NATURAL JOIN用来表示自然连接，我们可以把自然连接理解为SQL92中的等值连接，他会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接

SQL99语法中的新特性2：USING

USING指定了具体的相同的字段名称，你需要在USING的括号()中填入指定的同名字段，使用使用JSON…USING可以简化JOIN ON的等值连接