查询 在A表中存在，B表中不存在的数据--多个属性做主键的解决方案

   我有两张表Table1和Table2，Table1中有A、B、E属性，A和B共同做主键;Table2有C、D属性,C和D共同做主键。为了方便说明我将A、C定义为integer型，B、D定义为char型，E为varchar型数据。

  

   第一，用not in来解决：

           select

               Table1.A,

               Table1.B，

               Table1.E

            from

               Table1

            where

                (Table1.A,Table1.B) not in (select Table2.C,Table2.D from table2)

 

   第二，用not exists 来解决

          select

              Table1.A,

              Table1.B,

              Table1.E

           from

              Table1

           where

               not exists (select Table2.C,Table2.D from Table2 where Table1.A = Table2.C and Table.B = Table.D)

 

   第三，用left outer join来解决

          select

              Table1.A,

              Table1.B,

              Table1.E

          from

              Table1 left outer join Table2 on Table1.A = Table2.C and Table1.B = Table2.D

           where Table1.E = null

 

    第四，用except解决

           select

               Table1.A,

               Table1.B

            from

               Table1

           

            except

            

            select

                Table2.C

                Table2.D

             from

                Table2

 

         except 用于查找两个集合中的不同项