SQL基础4 —— 多表联合查询_多表联查4表sql-优快云博客

本文链接：https://blog.youkuaiyun.com/bandianrong/article/details/108492025

本文深入探讨了SQL中的各种联表查询方式，包括内连接、左连接、右连接、全连接以及笛卡尔积等，通过具体示例展示了如何在实际场景中应用这些查询技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建测试数据

mysql> create table t_join_test1 (t1_id int,t1_name varchar(20)) tablespace sqltestspace;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t_join_test2 (t2_id int,t2_name varchar(20)) tablespace sqltestspace;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_join_test1;
+-------+---------+
| t1_id | t1_name |
+-------+---------+
|     1 | Tina    |
|     2 | Tom     |
|     3 | Rose    |
|     4 | Jack    |
+-------+---------+
4 rows in set (0.00 sec)

mysql> select * from t_join_test2;
+-------+---------+
| t2_id | t2_name |
+-------+---------+
|     1 | Rose    |
|     2 | Lily    |
|     3 | Tina    |
|     4 | Angel   |
|     5 | Alice   |
+-------+---------+
5 rows in set (0.00 sec)

inner join

mysql> /*返回两表的交集*/
mysql>     select t1.t1_id
    ->            ,t1.t1_name
    ->            ,t2.t2_id
    ->            ,t2.t2_name
    ->       from t_join_test1 t1
    -> inner join t_join_test2 t2
    ->         on t1.t1_name = t2.t2_name;
+-------+---------+-------+---------+
| t1_id | t1_name | t2_id | t2_name |
+-------+---------+-------+---------+
|     3 | Rose    |     1 | Rose    |
|     1 | Tina    |     3 | Tina    |
+-------+---------+-------+---------+
2 rows in set (0.00 sec)

left join

mysql> /*左表全部保留，右表关联不上的用null表示*/
mysql>     select t1.t1_id
    ->            ,t1.t1_name
    ->            ,t2.t2_id
    ->            ,t2.t2_name
    ->       from t_join_test1 t1
    ->  left join t_join_test2 t2
    ->         on t1.t1_name = t2.t2_name;
+-------+---------+-------+---------+
| t1_id | t1_name | t2_id | t2_name |
+-------+---------+-------+---------+
|     1 | Tina    |     3 | Tina    |
|     2 | Tom     |  NULL | NULL    |
|     3 | Rose    |     1 | Rose    |
|     4 | Jack    |  NULL | NULL    |
+-------+---------+-------+---------+
4 rows in set (0.00 sec)
 
mysql> /*左表独有*/
mysql>     select t1.t1_id
    ->            ,t1.t1_name
    ->            ,t2.t2_id
    ->            ,t2.t2_name
    ->       from t_join_test1 t1
    ->  left join t_join_test2 t2
    ->         on t1.t1_name = t2.t2_name
    ->      where t2.t2_name is null;
+-------+---------+-------+---------+
| t1_id | t1_name | t2_id | t2_name |
+-------+---------+-------+---------+
|     2 | Tom     |  NULL | NULL    |
|     4 | Jack    |  NULL | NULL    |
+-------+---------+-------+---------+
2 rows in set (0.00 sec)

right join

mysql> /*右表全部保留，左表关联不上的用null表示*/
mysql>     select t1.t1_id
    ->            ,t1.t1_name
    ->            ,t2.t2_id
    ->            ,t2.t2_name
    ->       from t_join_test1 t1
    -> right join t_join_test2 t2
    ->         on t1.t1_name = t2.t2_name;
+-------+---------+-------+---------+
| t1_id | t1_name | t2_id | t2_name |
+-------+---------+-------+---------+
|     3 | Rose    |     1 | Rose    |
|  NULL | NULL    |     2 | Lily    |
|     1 | Tina    |     3 | Tina    |
|  NULL | NULL    |     4 | Angel   |
|  NULL | NULL    |     5 | Alice   |
+-------+---------+-------+---------+
5 rows in set (0.00 sec)

mysql> /*右表独有*/
mysql>     select t1.t1_id
    ->            ,t1.t1_name
    ->            ,t2.t2_id
    ->            ,t2.t2_name
    ->       from t_join_test1 t1
    -> right join t_join_test2 t2
    ->         on t1.t1_name = t2.t2_name
    ->      where t1.t1_name is null;
+-------+---------+-------+---------+
| t1_id | t1_name | t2_id | t2_name |
+-------+---------+-------+---------+
|  NULL | NULL    |     2 | Lily    |
|  NULL | NULL    |     4 | Angel   |
|  NULL | NULL    |     5 | Alice   |
+-------+---------+-------+---------+
3 rows in set (0.00 sec)

full join

/*返回两表的并集(MySQL并没有full (outer) join语法，这里只给出语法，可以在其他数据库中测试)*/
    select t1.t1_id
           ,t1.t1_name
           ,t2.t2_id
           ,t2.t2_name
      from t_join_test1 t1
 full join t_join_test2 t2
        on t1.t1_name = t2.t2_name;

mysql> /*MySQL中可以使用union来实现full join*/
mysql> select * from t_join_test1 t1 left join t_join_test2 t2 on t1.t1_name = t2.t2_name
    -> union
    -> select * from t_join_test1 t1 right join t_join_test2 t2 on t1.t1_name = t2.t2_name;
+-------+---------+-------+---------+
| t1_id | t1_name | t2_id | t2_name |
+-------+---------+-------+---------+
|     1 | Tina    |     3 | Tina    |
|     2 | Tom     |  NULL | NULL    |
|     3 | Rose    |     1 | Rose    |
|     4 | Jack    |  NULL | NULL    |
|  NULL | NULL    |     2 | Lily    |
|  NULL | NULL    |     4 | Angel   |
|  NULL | NULL    |     5 | Alice   |
+-------+---------+-------+---------+
7 rows in set (0.00 sec)

union 和 union all

mysql> /*union和并两表数据时会去重(两表的列数必须相同，数据类型可不同)*/
mysql> select t1_name as name from t_join_test1
    -> union
    -> select t2_name as name from t_join_test2;
+-------+
| name  |
+-------+
| Tina  |
| Tom   |
| Rose  |
| Jack  |
| Lily  |
| Angel |
| Alice |
+-------+
7 rows in set (0.00 sec)

mysql> /*union all和并两表数据时不会去重*/
mysql> select t1_name as name from t_join_test1
    -> union all
    -> select t2_name as name from t_join_test2;
+-------+
| name  |
+-------+
| Tina  |
| Tom   |
| Rose  |
| Jack  |
| Rose  |
| Lily  |
| Tina  |
| Angel |
| Alice |
+-------+
9 rows in set (0.00 sec)

笛卡尔积

mysql> select * from t_join_test1 inner join t_join_test2;
+-------+---------+-------+---------+
| t1_id | t1_name | t2_id | t2_name |
+-------+---------+-------+---------+
|     1 | Tina    |     1 | Rose    |
|     2 | Tom     |     1 | Rose    |
|     3 | Rose    |     1 | Rose    |
|     4 | Jack    |     1 | Rose    |
|     1 | Tina    |     2 | Lily    |
|     2 | Tom     |     2 | Lily    |
|     3 | Rose    |     2 | Lily    |
|     4 | Jack    |     2 | Lily    |
|     1 | Tina    |     3 | Tina    |
|     2 | Tom     |     3 | Tina    |
|     3 | Rose    |     3 | Tina    |
|     4 | Jack    |     3 | Tina    |
|     1 | Tina    |     4 | Angel   |
|     2 | Tom     |     4 | Angel   |
|     3 | Rose    |     4 | Angel   |
|     4 | Jack    |     4 | Angel   |
|     1 | Tina    |     5 | Alice   |
|     2 | Tom     |     5 | Alice   |
|     3 | Rose    |     5 | Alice   |
|     4 | Jack    |     5 | Alice   |
+-------+---------+-------+---------+
20 rows in set (0.00 sec)

自连接

* 经典面试题
* 要求：找出昨天和今天都下单的用户

mysql> /*表结构如下*/
mysql> create table t_order (
    -> order_no    int primary key auto_increment comment '订单号',
    -> user_no     int not null comment '用户编号',
    -> product_no  int not null comment '商品编号',
    -> created_at  timestamp default current_timestamp   comment '创建时间',
    -> updated_at  timestamp default current_timestamp on update current_timestamp   comment '更新时间',
    -> order_status int default 0 comment '订单状态(0-正常,1-失效,2-删除)'
    -> ) tablespace sqltestspace comment '订单表';
Query OK, 0 rows affected (0.01 sec)

mysql> /*表中数据如下*/
mysql> select * from t_order;
+----------+---------+------------+---------------------+---------------------+--------------+
| order_no | user_no | product_no | created_at          | updated_at          | order_status |
+----------+---------+------------+---------------------+---------------------+--------------+
|        1 |       1 |          3 | 2020-09-08 14:53:24 | 2020-09-09 15:53:24 |            0 |
|        2 |       1 |          7 | 2020-09-08 14:58:41 | 2020-09-09 15:58:41 |            0 |
|        3 |       2 |          4 | 2020-09-08 14:58:41 | 2020-09-09 15:58:41 |            0 |
|        4 |       3 |          6 | 2020-09-08 14:58:41 | 2020-09-09 15:58:41 |            0 |
|        5 |       5 |          8 | 2020-09-08 14:58:41 | 2020-09-09 15:58:41 |            0 |
|        6 |       6 |          3 | 2020-09-08 14:58:41 | 2020-09-09 15:58:41 |            0 |
|        7 |       1 |          9 | 2020-09-09 15:58:49 | 2020-09-09 15:58:49 |            0 |
|        8 |       3 |          7 | 2020-09-09 15:58:49 | 2020-09-09 15:58:49 |            0 |
|        9 |       5 |          3 | 2020-09-09 15:58:49 | 2020-09-09 15:58:49 |            0 |
|       10 |       7 |          2 | 2020-09-09 15:58:49 | 2020-09-09 15:58:49 |            0 |
|       11 |       7 |          3 | 2020-09-09 15:58:49 | 2020-09-09 15:58:49 |            0 |
|       12 |       4 |          3 | 2020-09-09 15:58:49 | 2020-09-09 15:58:49 |            0 |
+----------+---------+------------+---------------------+---------------------+--------------+
12 rows in set (0.00 sec)

mysql> /*关于函数我们后面会有讲解，此题主要考察表的自关联查询*/
mysql>     select distinct yestOrder.user_no
    ->       from (select user_no from t_order where date(created_at) = date_sub(curdate(),interval 1 day)) yestOrder
    -> inner join (select user_no from t_order where date(created_at) = curdate()) currOrder
    ->         on yestOrder.user_no = currOrder.user_no;
+---------+
| user_no |
+---------+
|       1 |
|       3 |
|       5 |
+---------+
3 rows in set (0.00 sec)