SQLServer 4类join查询：inner join,left join, right join, full outer join

1.from A inner join B on A.ID=B.ID :两表都有的记录才列出

A表：  ID   Name                           B表： ID  Class

         1      BMW                                   1     C1

         2      Aodi                                    2     C2

         Null    Benz                                  4     Null

         3        Null                                   Null   C4

select  * from A inner join B on A.ID=B.ID:

            ID       Name            ID        Class

            1        BMW              1         C1

            2         Aodi              2         C2

2.from A left join B on A.ID=B.ID:

   A表中所有记录列出，B中无法匹配的用Null匹配

A表：  ID   Name                           B表： ID  Class

         1      BMW                                   1     C1

         2      Aodi                                    2     C2

         Null    Benz                                  4     Null

         3        Null                                   Null   C4

select  * from A left join B on A.ID=B.ID:

            ID       Name            ID        Class

            1        BMW             1          C1

            2        Aodi              2          C2

           Null      Benz             Null       Null  

            3        Null               Null        Null        

 

3.from A right join B on A.ID=B.ID:

  B表中所有记录列出,A中无法匹配的用Null匹配

A表：  ID   Name                           B表： ID  Class

         1      BMW                                   1     C1

         2      Aodi                                    2     C2

         Null    Benz                                  4     Null

         3        Null                                   Null   C4

select  * from A right join B on A.ID=B.ID:

        ID       Name                   ID        Class

       1          BMW                     1           C1

       2         Aodi                       2           C2

       Null      Null                        4           Null

       Null      Null                       Null        C4

4.from A full outer join B on A.ID=B.ID.

   列出A表所有记录，B表所有记录。无法匹配的用Null替代

A表：  ID   Name                           B表： ID  Class

         1      BMW                                   1     C1

         2      Aodi                                    2     C2

         Null    Benz                                  4     Null

         3        Null                                   Null   C4

select * from A full outer join B on A.ID=B.ID.

ID       Name                   ID        Class

  1      BMW                     1         C1

  2      Aodi                      2        C2

  Null   Benz                     Null     Null

  3       Null                       Null    Null

 Null     Null                       4        Null

 Null    Null                        Null      C4

其它join查询：

  (1) left outer join,等价于left join.

 (2)right outer join, 等价于right join.

  (3) cross join,    笛卡尔乘积查询