1 交集 这种比条件里面加 IN 效率好多了,数据多的时候马上出来效果了 select ID,NAME from A WHERE EXISTS SELECT ID FROM B WHERE ID = A.ID AND NAME = A.NAME 2.并集 select ID,NAME from A union select ID,NAME from B
假设 id 为主键 交集 select a.id, a.name from TableA a join TableB b on A.id = b.id;并集 select a.id, a.name, b.id, b.name from TableA a full join TableB b on A.id = b.id
交集 内连接 并集 Union 差集 最麻烦,Minus(适合Oracle)、In 或Exits或其他(适合SqlServer)
select A.* from A,B where A.ID=B.IDtable A union table B
去看看join on和union的用法,就都在了!
针对oracle:交集 select * from tabw1 intersect select * from tab2并集 select * from tabw1 union all/union select * from tab2 差集: select * from tabw1 minus select * from tab2
交:select * from table1 intersect select * from table2 并:select * from table1 union all select * from table2 差:select * from table1 minus select * from table2
并集用union
这种比条件里面加 IN 效率好多了,数据多的时候马上出来效果了
select ID,NAME from A WHERE EXISTS SELECT ID FROM B WHERE ID = A.ID AND NAME = A.NAME
2.并集
select ID,NAME from A union select ID,NAME from B
交集
select a.id, a.name from TableA a
join TableB b
on A.id = b.id;并集
select a.id, a.name, b.id, b.name from TableA a
full join TableB b
on A.id = b.id
并集 Union
差集 最麻烦,Minus(适合Oracle)、In 或Exits或其他(适合SqlServer)
select * from tabw1
intersect
select * from tab2并集
select * from tabw1
union all/union
select * from tab2
差集:
select * from tabw1
minus
select * from tab2
并:select * from table1 union all select * from table2
差:select * from table1 minus select * from table2