两张表 t1、t2。t1的主键为a、b列,t2的主键为a1、b1列。(a对应a1,b对应b1)
找出主键相同的记录
select * from t1,t2 where t1.a=t2.c and t1.b=t2.d
找主键值不同的记录应该怎么找呢?(a、b表没有单独的主键,类似id排序之类的)
找出主键相同的记录
select * from t1,t2 where t1.a=t2.c and t1.b=t2.d
找主键值不同的记录应该怎么找呢?(a、b表没有单独的主键,类似id排序之类的)
where not exists(select 1 from t2 where t1.a=t2.c and t1.b=t2.d)
union all
select * from t2
where not exists(select 1 from t1 where t1.a=t2.c and t1.b=t2.d)
EXCEPT
select * from t1,t2 where t1.a=t2.c and t1.b=t2.d)
union
(select * from t2
EXCEPT
select * from t1,t2 where t1.a=t2.c and t1.b=t2.d)
where a is null or a1 is null
-->找出2个表中不一致的数据
select * from t1 where id not in
(select id from t2)
union
select * from t2 where id not in
(select id from t1)
WHERE EXISTS (SELECT 1 FROM
(
SELECT a,b
FROM t1
INTERSECT
SELECT a1,b1
FROM t2)a WHERE t1.a=A.a AND t1.b=a.B)
UNION
SELECT * FROM t2
WHERE EXISTS (SELECT 1 FROM
(
SELECT a,b
FROM t1
INTERSECT
SELECT a1,b1
FROM t2)a WHERE t2.a=A.a AND t2.b=a.B)