显示两表中具有相同姓名的行
SS表与SS2表
select id,name,sel from ss order by id; select id,name,sel from ss2;
查询SS1表与SS2表相同的人名
IN 写法 EXISTS 写法
select distinct name from ss where name in select distinct name from ss where exists
(select name from ss2); (select distinct name from ss2 where ss.name = ss2.name);
查询SS1表与SS2表不同的人名,以SS1为主表
NOT IN 写法 NOT EXISTS 写法
select distinct name from ss where name not in select distinct name from ss where not exists
(select name from ss2); (select distinct name from ss2 where ss.name = ss2.name);
IN 多字段查询写法 EXISTS 多字段查询写法
select distinct name from ss where (name,sel) in select distinct name from ss where exists
(select name,sel from ss2); (select distinct name from ss2 where ss.name = ss2.name and ss.sel = ss2.sel);
关键语句
distinct 去除重复字段
IN,EXISTS ,NOT IN,NOT EXISTS