mysql多表查询分类讲解

首先有两张表employees员工表以及departments部门表。员工表中department_id 外键与部门表中的主键department_id对应。各自的表结构如下

分类1：等值连接 vs 非等值连接

等值连接

比如

SELECT employees.employee_id, employees.last_name, 
       employees.department_id, departments.department_id,
       departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

需要注意的是，如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

非等值连接

 

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

分类2：自连接 vs 非自连接

• 当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

题目：查询employees表，返回“Xxx works for Xxx

SELECT CONCAT(worker.last_name ,' works for ' 
       , manager.last_name)
FROM   employees worker, employees manager
WHERE  worker.manager_id = manager.employee_id ;

 

分类3：内连接 vs 外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

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

• 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

• 如果是左外连接，则连接条件中左边的表也称为主表，右边的表称为从表。

  如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为从表。

 

SQL92：使用(+)创建连接   注意，这种方式再mysql中不能使用，其他比如Oracle可以

• 在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。

• Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。

• #左外连接
  SELECT last_name,department_name
  FROM employees ,departments
  WHERE employees.department_id = departments.department_id(+);
  
  #右外连接
  SELECT last_name,department_name
  FROM employees ,departments
  WHERE employees.department_id(+) = departments.department_id;

  而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

SQL99语法实现多表查询

使用JOIN...ON子句创建连接的语法结构：

 

SELECT table1.column, table2.column,table3.column
FROM table1
    JOIN table2 ON table1 和 table2 的连接条件
        JOIN table3 ON table2 和 table3 的连接条件

内连接(INNER JOIN)的实现

语法：

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.employee_id, e.last_name, e.department_id, 
       d.department_id, d.location_id
FROM   employees e JOIN departments d
ON     (e.department_id = d.department_id);

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

外连接(OUTER JOIN)的实现

左外连接(LEFT OUTER JOIN)

• 语法：

#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

右外连接(RIGHT OUTER JOIN)

• 语法：

  #实现查询结果是B
  SELECT 字段列表
  FROM A表 RIGHT JOIN B表
  ON 关联条件
  WHERE 等其他子句;

  SELECT e.last_name, e.department_id, d.department_name
  FROM   employees e
  RIGHT OUTER JOIN departments d
  ON    (e.department_id = d.department_id) ;

  满外连接(FULL OUTER JOIN)

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。

• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。

• 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。