八、MySQL基础系列笔记之SQL DQL7种连接查询

连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询，包含内连接、外连接等。

准备工作

新建2张表，数据如下

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">user</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| id | name      | email            |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> | 李四      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">123456</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> | 王五      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">124568</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> | 张三      | zhangsan<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> | 王美丽    | meili<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span>    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">5</span> | 你真帅    | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">7788641</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>   |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> | 刘一      | liuyi<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span>    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">7</span> | 陈二      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">782372340</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span> |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8</span> | 吴九      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8193730</span>-<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>  |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">8</span> rows <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">set</span> (<span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.00</span> sec)</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box"></span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> info;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| id | user_id | hobby     |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> | 篮球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> | 乒乓球    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> | 排球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> | 棒球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">5</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> | 溜溜球    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">9</span> | 足球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> rows <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">set</span> (<span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.00</span> sec)</span>

笛卡尔积

将出现48条记录，由于太长，不列出。

mysql> select * from user,info;

inner join - 内连接查询

inner join查询交集，也就是两表公有部分结果集。

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">user</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> a <span style="box-sizing: border-box;color: rgb(199, 146, 234)">inner</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">join</span> info <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> b <span style="box-sizing: border-box;color: rgb(199, 146, 234)">on</span> a<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.id</span> = b<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.user_id</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+----+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| id | name      | email            | id | user_id | hobby     |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+----+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> | 李四      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">123456</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>    |  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> | 篮球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> | 王美丽    | meili<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span>    |  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> | 乒乓球    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> | 王五      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">124568</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>    |  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> | 排球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> | 刘一      | liuyi<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span>    |  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> | 棒球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> | 张三      | zhangsan<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span> |  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">5</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> | 溜溜球    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+----+---------+-----------+</span>

left join - 左连接查询

left join 返回左表中所有的记录和右表中与连接字段相等的记录，右表中没有的记录补null。

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">user</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> a <span style="box-sizing: border-box;color: rgb(199, 146, 234)">left</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">join</span> info <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> b <span style="box-sizing: border-box;color: rgb(199, 146, 234)">on</span> a<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.id</span> = b<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.user_id</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+------+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| id | name      | email            | id   | user_id | hobby     |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+------+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> | 李四      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">123456</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>    |    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> | 篮球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> | 王美丽    | meili<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span>    |    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> | 乒乓球    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> | 王五      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">124568</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>    |    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> | 排球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> | 刘一      | liuyi<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span>    |    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> | 棒球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> | 张三      | zhangsan<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span> |    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">5</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> | 溜溜球    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">5</span> | 你真帅    | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">7788641</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>   | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |    <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">7</span> | 陈二      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">782372340</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |    <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8</span> | 吴九      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8193730</span>-<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>  | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |    <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+------+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">8</span> rows <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">set</span> (<span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.00</span> sec)</span>

right join - 右连接查询

right join 返回右表中所有的记录和左表中与连接字段相等的记录，左表中没有的记录补null。

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">user</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> a <span style="box-sizing: border-box;color: rgb(199, 146, 234)">right</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">join</span> info <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> b <span style="box-sizing: border-box;color: rgb(199, 146, 234)">on</span> a<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.id</span> = b<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.user_id</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------+-----------+------------------+----+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| id   | name      | email            | id | user_id | hobby     |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------+-----------+------------------+----+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> | 李四      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">123456</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>    |  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> | 篮球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> | 王美丽    | meili<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span>    |  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> | 乒乓球    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> | 王五      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">124568</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>    |  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> | 排球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> | 刘一      | liuyi<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span>    |  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> | 棒球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> | 张三      | zhangsan<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span> |  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">5</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> | 溜溜球    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>      | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>             |  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">9</span> | 足球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------+-----------+------------------+----+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> rows <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">set</span> (<span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.00</span> sec)</span>

left join b.key is null 查询左表度有部分

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">user</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> a <span style="box-sizing: border-box;color: rgb(199, 146, 234)">left</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">join</span> info <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> b <span style="box-sizing: border-box;color: rgb(199, 146, 234)">on</span> a<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.id</span> = b<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.user_id</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">where</span> b<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.user_id</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">is</span> <span style="box-sizing: border-box;color: rgb(247, 140, 108)">null</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+------+---------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| id | name      | email            | id   | user_id | hobby |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+------+---------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">5</span> | 你真帅    | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">7788641</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>   | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |    <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>  |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">7</span> | 陈二      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">782372340</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |    <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>  |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8</span> | 吴九      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8193730</span>-<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>  | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |    <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>  |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+----+-----------+------------------+------+---------+-------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> rows <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">set</span> (<span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.00</span> sec)</span>

right join a.key is null查询右表独有

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">user</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> a <span style="box-sizing: border-box;color: rgb(199, 146, 234)">right</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">join</span> info <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> b <span style="box-sizing: border-box;color: rgb(199, 146, 234)">on</span> a<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.id</span> = b<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.user_id</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">where</span> a<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.id</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">is</span> <span style="box-sizing: border-box;color: rgb(247, 140, 108)">null</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------+------+-------+----+---------+--------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| id   | name | email | id | user_id | hobby  |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------+------+-------+----+---------+--------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>  |  <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">9</span> | 足球   |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------+------+-------+----+---------+--------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">row</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">set</span> (<span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.00</span> sec)</span>

全连接查询

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">user</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> a <span style="box-sizing: border-box;color: rgb(199, 146, 234)">left</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">join</span> info <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> b <span style="box-sizing: border-box;color: rgb(199, 146, 234)">on</span> a<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.id</span> = b<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.user_id</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">    -> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">union</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">    -> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">user</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> a <span style="box-sizing: border-box;color: rgb(199, 146, 234)">right</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">join</span> info <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> b <span style="box-sizing: border-box;color: rgb(199, 146, 234)">on</span> a<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.id</span> = b<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.user_id</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------+-----------+------------------+------+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| id   | name      | email            | id   | user_id | hobby     |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------+-----------+------------------+------+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> | 李四      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">123456</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>    |    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">1</span> | 篮球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> | 王美丽    | meili<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span>    |    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> | 乒乓球    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> | 王五      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">124568</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>    |    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">2</span> | 排球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> | 刘一      | liuyi<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span>    |    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> | 棒球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> | 张三      | zhangsan<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@163.com</span> |    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">5</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">3</span> | 溜溜球    |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">5</span> | 你真帅    | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">7788641</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>   | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |    <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">7</span> | 陈二      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">782372340</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |    <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8</span> | 吴九      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8193730</span>-<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>  | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |    <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>      | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>             |    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">9</span> | 足球      |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------+-----------+------------------+------+---------+-----------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">9</span> rows <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">set</span> (<span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.00</span> sec)</span>

单a与独b查询

<span style="box-sizing: border-box;padding-right: 0.1px">mysql> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">user</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> a <span style="box-sizing: border-box;color: rgb(199, 146, 234)">left</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">join</span> info <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> b <span style="box-sizing: border-box;color: rgb(199, 146, 234)">on</span> a<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.id</span> = b<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.user_id</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">where</span> b<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.user_id</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">is</span> <span style="box-sizing: border-box;color: rgb(247, 140, 108)">null</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">    -> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">union</span></span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">    -> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">select</span> * <span style="box-sizing: border-box;color: rgb(199, 146, 234)">from</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">user</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> a <span style="box-sizing: border-box;color: rgb(199, 146, 234)">right</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">join</span> info <span style="box-sizing: border-box;color: rgb(199, 146, 234)">as</span> b <span style="box-sizing: border-box;color: rgb(199, 146, 234)">on</span> a<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.id</span> = b<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.user_id</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">where</span> a<span style="box-sizing: border-box;color: rgb(238, 255, 255)">.id</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">is</span> <span style="box-sizing: border-box;color: rgb(247, 140, 108)">null</span>;</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------+-----------+------------------+------+---------+--------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| id   | name      | email            | id   | user_id | hobby  |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------+-----------+------------------+------+---------+--------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">5</span> | 你真帅    | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">7788641</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>   | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |    <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>   |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">7</span> | 陈二      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">782372340</span><span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |    <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>   |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">|    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8</span> | 吴九      | <span style="box-sizing: border-box;color: rgb(255, 83, 112)">8193730</span>-<span style="box-sizing: border-box;color: rgb(238, 255, 255)">@qq.com</span>  | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> |    <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>   |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">| <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span> | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>      | <span style="box-sizing: border-box;color: rgb(247, 140, 108)">NULL</span>             |    <span style="box-sizing: border-box;color: rgb(255, 83, 112)">6</span> |       <span style="box-sizing: border-box;color: rgb(255, 83, 112)">9</span> | 足球   |</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px">+------+-----------+------------------+------+---------+--------+</span><br></br><span style="box-sizing: border-box;padding-right: 0.1px"><span style="box-sizing: border-box;color: rgb(255, 83, 112)">4</span> rows <span style="box-sizing: border-box;color: rgb(199, 146, 234)">in</span> <span style="box-sizing: border-box;color: rgb(199, 146, 234)">set</span> (<span style="box-sizing: border-box;color: rgb(255, 83, 112)">0.00</span> sec)</span>

我是小白，期待和优秀的你一起同行!

小白

2020年10月11日