请教，取得AB两表的交集，A表减交集，B表减交集，这三个，SQL的语法。

[flyland]

请教，取得AB两表的交集，A表减交集，B表减交集，这三个SQL的语法。
谢谢大家。

[AntingZ]

假设id是主键

AB两表的交集
select a.* from A a where exists (select 1 from B b where b.id=a.id)

A表减交集
select a.* from A a where not exists (select 1 from B b where b.id=a.id)

B表减交集
select b.* from B b where not exists (select 1 from A a where b.id=a.id)

[dnvodc]

使用 EXISTS 和 NOT EXISTS 查找交集与差集
使用 EXISTS 和 NOT EXISTS 引入的子查询可用于两种集合原理的操作：交集与差集。两个集合的交集包含同时属于两个原集合的所有元素。差集包含只属于两个集合中的第一个集合的元素。

city 列中 authors 和 publishers 的交集是作者和出版商共同居住的城市的集合。

USE pubs
SELECT DISTINCT city
FROM authors
WHERE EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)

下面是结果集：

city
--------
Berkeley

(1 row(s) affected)

当然，该查询可以写成一个简单的联接。

USE pubs
SELECT DISTINCT authors.city
FROM authors INNER JOIN publishers
ON authors.city = publishers.city

city 列中 authors 和 publishers 的差集是作者所居住的、但没有出版商居住的所有城市的集合，也就是除 Berkeley 以外的所有城市。

USE pubs
SELECT DISTINCT city
FROM authors
WHERE NOT EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)

该查询也可以写成：

USE pubs
SELECT DISTINCT city
FROM authors
WHERE city NOT IN
(SELECT city
FROM publishers)

[子陌红尘]

select A.* from A ,B where A.co1 = B.co1 and A.co2 = B.co2 ...

select C.* from (
select * from A
union all
select A.* from A,B where A.co1 = B.co1 and A.co2 = B.co2 ...) C
group by C.co1,C.co2 having count(*) <2

select C.* from (
select * from B
union all
select A.* from A,B where A.co1 = B.co1 and A.co2 = B.co2 ...) C
group by C.co1,C.co2 having count(*) <2

[shitong]

如果你两个表的结构一样，可以这么做，效果和楼上的差不多

取得AB交集：
select * from ta where a.id in (select b.id from tb )

A表减交集：
select * from ta where a.id not in (select b.id from tb)

B表减交集：
select * from tb where b.id not in (select a.id from ta)

[Andy__Huang]

AB两表的交集
select a.col1,a.col2 from tb1 a inner join tb2 on a.col1=b.col1 and a.col2=b.col2

A表减交集,有相同記錄的選出來
select * from ta
union select * from tb

[mazekui]

哎！来迟了！不行的话我再帮你解决！

[老宛]

create table #a(
a int,
b int
)
create table #b(
a int,
b int
)
insert into #a select 1,1
insert into #a select 2,1
insert into #a select 3,1
insert into #a select 4,1
insert into #a select 1,2
insert into #a select 2,2
insert into #a select 3,2

insert into #b select 1,1
insert into #b select 2,1
insert into #b select 3,1
insert into #b select 1,1
insert into #b select 5,1
--交集
select a,b from (
select distinct a,b from #a
union all
select distinct a,b from #b
) x group by a,b having count(*)>1
--A表减交集
select a,b from (
select a,b from (
select distinct a,b from #a
union all
select distinct a,b from #b
) x group by a,b having count(*)=1
union all
select distinct a,b from #a
) y group by a,b having count(*)>1
--B表减交集
select a,b from (
select a,b from (
select distinct a,b from #a
union all
select distinct a,b from #b
) x group by a,b having count(*)=1
union all
select distinct a,b from #b
) y group by a,b having count(*)>1
drop table #a
drop table #b

[flyland]

谢谢，大家，给分了