SQL 关联查询数据 ---- 交叉连接、内连接、外连接、全连接、连接查询

我不是女神ヾ 2021-10-16 01:48 447阅读 0赞

## 1.多表查询(交叉连接 / ROSS JOIN) ##

示例表
             A：                                B:
                 id    val                        id   val
                 1      ab                         1    ab
                 2      a                          3    b

** ①从多张表同时查询数据的语法是：**

**       SELECT \* FROM <表1> , <表2>** 或者 **SELECT \* FROM <表1> ROSS JOIN<表2>**。

例如，查询 A表和 B表的“乘积”，即 **A表 的每一行与 B表 的每一行都两两拼在一起返回**。使用多表查询可以获取M x N行记录，结果集的列数是 A表 和 B表 的列数之和，行数是 A表 和 B表 的行数之积。这种**多表查询**又称**笛卡尔查询**。多表查询的结果集可能非常巨大，要小心使用。

mysql> select * from a, b;   -- 或使用 select * from a cross join b;
    +----+-----+----+-----+
    | id | val | id | val |
    +----+-----+----+-----+
    |  1 | ab  |  1 | ab  |
    |  2 | a   |  1 | ab  |
    |  1 | ab  |  3 | b   |
    |  2 | a   |  3 | b   |
    +----+-----+----+-----+
    4 rows in set (0.00 sec)

②可以看到上述的查询结果集有两列 id 和 val，在结果集中，不好区分是哪张表的 id 和 val。可以利用投影查询的“设置列的别名”来**给两个表各自的 id 和 val 列起别名**。可以使用 **< 表名.列名 > 的方式来引用列表中某列并设置别名,** 避免了结果集的列名重复问题。**( 给列设置别名 )**

# A.id a_id : 引用A表中的id列设置别名为 a_id
    mysql> SELECT A.id a_id, A.val a_val, B.id b_id, B.val b_val FROM A, B;
    +------+-------+------+-------+
    | a_id | a_val | b_id | b_val |
    +------+-------+------+-------+
    |    1 | ab    |    1 | ab    |
    |    2 | a     |    1 | ab    |
    |    1 | ab    |    3 | b     |
    |    2 | a     |    3 | b     |
    +------+-------+------+-------+
    4 rows in set (0.00 sec)
    
    #  也可以使用以下方法，A.id as a_id 等价 A.id a_id
    mysql> SELECT A.id as a_id, A.val as a_val, B.id as b_id, B.val as b_val
        ->  FROM A, B;
    +------+-------+------+-------+
    | a_id | a_val | b_id | b_val |
    +------+-------+------+-------+
    |    1 | ab    |    1 | ab    |
    |    2 | a     |    1 | ab    |
    |    1 | ab    |    3 | b     |
    |    2 | a     |    3 | b     |
    +------+-------+------+-------+
    4 rows in set (0.00 sec)

③ 用 表名.列名 这种方式 列举两个表的所有列，**当表名比较长和复杂的时候**有可能会写错并且列举两个表的所有列实在是很麻烦，SQL还允许**给表设置一个别名**，FROM子句给表设置别名的**语法是FROM <表名1> <别名1>, <表名2> <别名2>**。这样在投影查询中引用起来稍微简洁一点。**(给表设置别名)**

# 给A表设置别名 a1, 给B表设置别名为 b1
    mysql> SELECT a1.id a1_id, a1.val a1_val, b1.id b1_id, b1.val b1_val 
        -> FROM A a1, B b1;
    +-------+--------+-------+--------+
    | a1_id | a1_val | b1_id | b1_val |
    +-------+--------+-------+--------+
    |     1 | ab     |     1 | ab     |
    |     2 | a      |     1 | ab     |
    |     1 | ab     |     3 | b      |
    |     2 | a      |     3 | b      |
    +-------+--------+-------+--------+
    4 rows in set (0.00 sec)
    
    # A as a1  等价 A a1
    mysql> SELECT a1.id a1_id, a1.val a1_val, b1.id b1_id, b1.val b1_val 
        -> FROM A as a1, B as b1;
    +-------+--------+-------+--------+
    | a1_id | a1_val | b1_id | b1_val |
    +-------+--------+-------+--------+
    |     1 | ab     |     1 | ab     |
    |     2 | a      |     1 | ab     |
    |     1 | ab     |     3 | b      |
    |     2 | a      |     3 | b      |
    +-------+--------+-------+--------+
    4 rows in set (0.00 sec)

④ 多表查询也是可以添加WHERE条件，**添加WHERE条件后结果集的数量大大减少了**。

mysql> SELECT a1.id a1_id, a1.val a1_val, b1.id b1_id, b1.val b1_val 
        -> FROM A as a1, B as b1
        -> where  a1.id = 1 and b1.val = 'ab';
    +-------+--------+-------+--------+
    | a1_id | a1_val | b1_id | b1_val |
    +-------+--------+-------+--------+
    |     1 | ab     |     1 | ab     |
    +-------+--------+-------+--------+
    1 row in set (0.00 sec)

## 2.连接查询 ##

**连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。**

**① 内连接——INNER JOIN**

**  结果集：当两个表存在满足条件的匹配时，才会返回匹配行**。INNER JOIN 是最常用的一种JOIN查询，它的**语法是SELECT ... FROM <表1> INNER JOIN <表2> ON <条件...>**。**INNER JOIN 可以缩写成 JOIN**。

# 使用别名不是必须的，但可以更好地简化查询语句。
    mysql> select a1.id a1_id, a1.val a1_val, b1.id b1_id, b1.val b1_val
        -> from A a1 inner join B b1
        -> on a1.id = b1.id;      --- on后面条件不能使用列别名，可以使用表别名
    +-------+--------+-------+--------+
    | a1_id | a1_val | b1_id | b1_val |
    +-------+--------+-------+--------+
    |     1 | ab     |     1 | ab     |
    +-------+--------+-------+--------+
    1 row in set (0.01 sec)
    
    # INNER JOIN 可以缩写成 JOIN
    mysql> select a1.id a1_id, a1.val a1_val, b1.id b1_id, b1.val b1_val
        -> from A a1 join B b1
        -> on a1.id = b1.id;
    +-------+--------+-------+--------+
    | a1_id | a1_val | b1_id | b1_val |
    +-------+--------+-------+--------+
    |     1 | ab     |     1 | ab     |
    +-------+--------+-------+--------+
    1 row in set (0.01 sec)

** INNER JOIN 查询的写法是：先确定主表，使用FROM <表1>的语法；再确定需要连接的表，使用INNER JOIN <表2>的语法；然后 确定连接条件，使用ON <条件...>，这里的条件是a1.id = b1.id，表示A表的id列与B表的id列相同的行需要连接；可选：加上WHERE子句、ORDER BY等子句。**

ON 后面条件，又可分为 **等值连接**on a1.id = b1.id ；**不等值连接** on a1.id > b1.id；**自连接**

# 自连接，其实就是同一张表有个别名a1和a2，用别名同时引用表id, 再用等号连接
    select * from A a1 inner join A a2 on a1.id = a2.id

**   ② 外连接（OUTER JOIN）**分为 右外连接 ( RIGHT OUTER JOIN) 和 左外连接(LEFT OUTER JOIN)。 左外连接 可以简写成 左连接 ，可以把 **LEFT OUTER JOIN 可以简写成  LEFT JOIN**。右外连接 可以简写成 右连接 ，可以把 **RIGHT OUTER JOIN 可以简写成 RIGHT JOIN**。

**左连接**返回左表中所有记录，即使右表中没有匹配的记录，没有匹配的字段会设置成 NULL。

**右连接**返回右表中所有记录，即使左表中没有匹配的记录，没有匹配的字段会设置成 NULL。

**③ 全外连接(全连接、FULL OUTER JOIN)。**

**全外连接 可以简写成 全连接(FULL JOIN)**，**需要注意的是 Mysql 不支持 这种全连接，可以用 left join、union 、right join 联合使用模拟。**

先用 left join 选出左表中所有行，然后再用 right join 选出右表中所有行，最后再把它联合起来。如果结果集中有重复的行(记录)会被 union 自动压缩。

**④ 联合查询(UNION 或 UNION ALL)**

如果我们需要将两个select语句的结果作为一个整体显示出来，我们就需要用到union或者union all关键字。**union 和 union al l的区别是，union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。**

Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序。

Union All：对两个结果集进行并集操作，包括重复行，不进行排序

### 我这里连接查询只做了知识点的梳理，关于连接查询的代码演示请查看  ###

[https://blog.csdn.net/qq\_37189082/article/details/99242078][https_blog.csdn.net_qq_37189082_article_details_99242078]

[https_blog.csdn.net_qq_37189082_article_details_99242078]: https://blog.csdn.net/qq_37189082/article/details/99242078