SQL-leetcode刷题笔记-175组合两个表

题目描述

表1：Person (Primary Key: PersonId)

表2：Address (Primary Key: AddressId)

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：

​ FirstName, LastName, City, State

相关知识

1. 之前课上学过笛卡尔积可以将两个表进行m*n拼接并去除重复项，但是不清楚SQL中如何使用笛卡尔积

2. SQL的查询语句中，常常使用连接，连接的基础便是笛卡尔积

   • 查询

     • 先确定数据要用到哪些表。
     • 将多个表先通过笛卡尔积变成一个表。
     • 然后去除不符合逻辑的数据（根据两个表的关系去掉）。
     • 最后当做是一个虚拟表一样来加上条件即可。

   • 笛卡尔积

     -- 没有限制条件时，就是笛卡尔积的结果
     select 字段名1,字段名2 from 表1名,表2名
     

   • 连接

     • 内连接：取两表的公共数据，有where条件的称为内连接

       -- 有限制条件时，表名用inner join进行连接
       select 字段名1,字段名2 from 表1名 inner join 表2名 on ... where 限制条件
       -- 或者inner join 直接用英文逗号进行简写
       select 字段名1,字段名2 from 表1名,表2名 on ...where 限制条件
       

     • 外连接

       • 左外连接：连接结果保留左表的全部数据

         -- 有限制条件时，表明用left outer join进行连接
         select 字段名1,字段名2 from 表1名 left outer join 表2名 on... where 限制条件
         

         A左外连接B，如果A表中有的数据而B表没有，则显示A中有的数据并且B表对应字段为空

       • 右外连接：连接结果保留右表的全部数据

         -- 有限制条件时，表名用right outer join进行连接
         select 字段名1,字段名2 from 表1名 right outer join 表2名 on... where 限制条件
         

         A右外连接B，如果B表中有的数据而A表没有，则显示B中有的数据并且A表对应字段为空

   • 限制条件

     • on：其后的限制条件不满足，就不进行连接，但是查询结果中会以null补足额外数据继续显示
     • where：其后的限制条件不满足，查询结果中没有不满足条件的行

解题过程

1. 它要求查询一个person的地址信息，所以需要匹配PersionId

2. 无论person有没有地址信息，都要返回，那就是无地址信息时，用null填充City和State，这符合on条件的用法

3. 构造sql，报语法错误You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘==Address.PersonId’

   select Person.FirstName, Person.LastName, Address.City, Address.State from Person, Address on Person.PersonId==Address.PersonId
   

4. 发现sql中的判断相等是一个等号，修改sql，仍然报错You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘on Person.PersonId=Address.PersonId’

   select Person.FirstName, Person.LastName, Address.City, Address.State from Person, Address on Person.PersonId=Address.PersonId
   

5. 发现使用on时，不能采用简写，必须用inner join连接两个表名，修改后结果如下

   • sql

     select FirstName, LastName, City, State from Person inner join Address on Person.PersonId = Address.PersonId
     

   • input

     {
         "headers":{
             "Person":[
                 "PersonId",
                 "LastName",
                 "FirstName"
             ],
             "Address":[
                 "AddressId",
                 "PersonId",
                 "City",
                 "State"
             ]
         },
         "rows":{
             "Person":[
                 [
                     1,
                     "Wang",
                     "Allen"
                 ]
             ],
             "Address":[
                 [
                     1,
                     2,
                     "New York City",
                     "New York"
                 ]
             ]
         }
     }
     

   • my output，并没有用null补足Address数据，结果错误

     {
         "headers":[
             "FirstName",
             "LastName",
             "City",
             "State"
         ],
         "values":[
          
         ]
     }
     

6. 去掉on限制条件，再次运行

   • sql

     select FirstName,LastName,City,State from Person inner join Address
     

   • my output

     {
         "headers":[
             "FirstName",
             "LastName",
             "City",
             "State"
         ],
         "values":[
             [
                 "Allen",
                 "Wang",
                 "New York City",
                 "New York"
             ]
         ]
     }
     

   • 添加on条件之后不进行连接，自然结果一行都没有

7. 修改sql，执行报错You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ ，搜索之后发现是使用外连接时必须有on关键字

   select FirstName,LastName,City,State from Person left outer join Address
   

8. 修改sql，提交测试通过

   select FirstName,LastName,City,State from Person left join Address on Person.PersonId=Address.PersonId 
   

9. 发现其他人大部分题解的执行时间都少于我，查看题解

学习总结

1. 当本题一开始选用内连接查询结果不对，是因为内连接只保留公共数据，加上on关键字只会使结果更少

2. 本题中要求当Address表没有Person数据时，要以null填充，按照内连接的处理逻辑直接就没有这一条记录了，所以在无on关键字时返回结果已经是空了

3. 多表连接查询总结

   • A∩B

     select ... from 表1 as a inner join 表2 as b on a.列名=b.列名
     

   • 当B表可能没有A表条目相关数据时用null填充

     select ... from 表1 as a left join 表2 as b on a.列名=b.列名
     

   • 从B中查找A中条目的相关信息，如果B中没有，则不显示A的相关信息

     select ... from 表1 as a left join 表2 as b on a.列名=b.列名 where b.列名 is null