区分SQL Server关联查询之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

 

SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1

 

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    

 

 

    

SELECT * FROM Tables1 t1 LEFT OUTER JOIN Table2 t2 on t1.Col1 = t2.Col2

 

SELECT * FROM Table1 t1 LEFT OUTER JOIN Table2 t2 ON t1.Col1 = t2.Col1 WHERE t2.Col1 IS 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

 

SELECT * FROM Tables1 t1 RIGHT OUTER JOIN Table2 t2 on t1.Col1 = t2.Col2

 

SELECT * FROM Table1 t1 RIGHT OUTER JOIN Table2 t2 ON t1.Col1 = t2.Col1 WHERE t2.Col1 IS NULL

 

 

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

 

SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.Col1 = t2.Col1 

 

SELECT * FROM Table1 t1 FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID WHERE t1.ID IS NULL OR t2.ID IS NULL

 

CROSS JOIN：

交叉连接不需要任何连接条件。这个会把两个表的的数据进行笛卡尔积操作。

SELECT * FROM Table1 t1 CROSS JOIN Table2 t2