select * from tablea where tableA.a1 not in(select tablea.a1 from tablea,tableb where tablea.a1=tableb.b1 and tablea.a2=tableb.b2) or
(tablea.a1 in(select tablea.a1 from tablea,tableb where tablea.a1=tableb.b1 and tablea.a2=tableb.b2) and
tablea.a2 not in (select tablea.a2 from tablea,tableb where tablea.a1=tableb.b1 and tablea.a2=tableb.b2))
create table tableA (a1 int,a2 int)
insert tableA select 1,2
Union all select 3,4
Union all select 5,6
Union all select 7,8
create table tableB(b1 int,b2 int)
insert tableB select 1,2
Union all select 3,4
Union all select 9,10
1:
Select a.* from tableA a join tableB b on a.a1 = b.b1 and a.a2 = b.b2
a1 a2
----------- -----------
1 2
3 4
(所影响的行数为 2 行)
2:
Select * from tableA a where not exists(select * from tableB where b1 = a.a1 and b2 = a.a2)
1/ select tableA.* from tableA,tableB where tableA.a1=tableB.b1 and tableA.a2=tableB.b2
2/ select tableA.* from tableB,tableB where tableA.a1<>tableB.b1 and tableA.a2<>tableB.b2
语句无错误。
1/ select * from tableA,tableB where tableA.a1=tableB.b1 and tableA.a2=tableB.b2
2/ select * from tableB,tableB where tableA.a1<>tableB.b1 and tableA.a2<>tableB.b2
多看两眼书什么都出来了!