except 能够查看表一对表二不同的数据,有点像是对表一进行表一表二交集的反集的交集,好绕;table
intersect 能够查看表一和表二同样的数据,求交集;select
select t1.name,t1.age,t1.country from table_1 t1数据
excepttab
select t2.name,t2.age,t2.country from table_2 t2 order by name,age;co
能够对两表使用where条件;ab
能够将except结果建立成一张新的表:
create table table_3 as (
select t1.name,t1.age,t1.country from table_1 t1
except
select t2.name,t2.age,t2.country from table_2 t2 order by name,age;
);
能够将except结果插入另外一张表:
insert into table_2(name,age,country)
select t1.name,t1.age,t1.country from table_1 t1
except
select t2.name,t2.age,t2.country from table_2 t2 order by name,age;