数据库_多表查询_内连接&外连接

mysql 文章 2022-07-21 21:20 0 全屏看文

首先明确一下定义:

内连接:在多表查询的时候,只检索出表之间相互匹配的行(满足匹配条件的行),结果中不包括两个表之间不匹配的行。

一个不太准确但易懂的例子:比如有两个表,表1存储了 (1,2,3,4,5 )这五个数字。表2存储了(3,4,5,6,7,8)这6个数字。多表查询时两个表的关联条件是 “表1 = 表2”.

那么检索的输出就是取这两个表的交集。,即 3,4,5.

外连接:在多表查询的时候,处理检索出内连接的数据外,还返回左表(或者右表)中不满足匹配条件的行。这种连接称为左(或者右)外连接。

还是上边那个小例子:左外连接的返回结果为(1,2,3,4,5);右外连接的返回结果为(3,4,5,6,7,8).

如果是左外连接,则连接条件中左边的表也称为主表 ,右边的表称为从表 。如下简图:

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

代码实现:

下面是代码举例中要用到的3个表

employees表:

+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id    | int         | NO   | PRI | 0       |       |
| first_name     | varchar(20) | YES  |     | NULL    |       |
| last_name      | varchar(25) | NO   |     | NULL    |       |
| email          | varchar(25) | NO   | UNI | NULL    |       |
| phone_number   | varchar(20) | YES  |     | NULL    |       |
| hire_date      | date        | NO   |     | NULL    |       |
| job_id         | varchar(10) | NO   | MUL | NULL    |       |
| salary         | double(8,2) | YES  |     | NULL    |       |
| commission_pct | double(2,2) | YES  |     | NULL    |       |
| manager_id     | int         | YES  | MUL | NULL    |       |
| department_id  | int         | YES  | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

departments表:

+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| department_id   | int         | NO   | PRI | 0       |       |
| department_name | varchar(30) | NO   |     | NULL    |       |
| manager_id      | int         | YES  | MUL | NULL    |       |
| location_id     | int         | YES  | MUL | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

 locations表:

+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| location_id    | int         | NO   | PRI | 0       |       |
| street_address | varchar(40) | YES  |     | NULL    |       |
| postal_code    | varchar(12) | YES  |     | NULL    |       |
| city           | varchar(30) | NO   |     | NULL    |       |
| state_province | varchar(25) | YES  |     | NULL    |       |
| country_id     | char(2)     | YES  | MUL | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

注:以下所用语法:JOIN...ON 为 SQL99 中的多表查询实现语法。

内连接:检索返回员工姓名,部门名组成的表(需要用到employees表及departments表)

mysql> SELECT last_name,department_name
    -> FROM employees e  # 表1 JOIN 表2 ON 匹配条件;
    -> JOIN departments d
    -> ON e.department_id = d.department_id;   # 关联条件

检索返回员工姓名、部门名称、部门所在城市组成的表。

mysql> 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;
    /*
    表1
    JOIN 表2
    ON 表1和表2的匹配条件
    JOIN 表3 
    ON 表2和表3的匹配条件;
    */

外连接:从两个表中,检索返回所有员工的姓名、部门名组成的表。

mysql> SELECT employee_id,department_name
    -> FROM employees e   # employees表为主表,departments表为从表。主表 LEFT OUTER JOIN 从表 ON 匹配条件;
    -> LEFT OUTER JOIN departments d   # 左外连接 
    -> ON e.department_id = d.department_id;
-EOF-