select m.货号 from table1 m where m.货号 not in (select 货号 from table2) union all select m.货号 from table1 m , table2 n where m.货号 = n.货号 and m.数量 <> n.数量
方法一: select m.货号 from table1 m where m.货号 not in (select 货号 from table2) union all select m.货号 from table1 m , table2 n where m.货号 = n.货号 and m.数量 <> n.数量方法二: select m.货号 from table1 m where not exists (select 1 from table2 n where n.货号 = m.货号) union all select m.货号 from table1 m , table2 n where m.货号 = n.货号 and m.数量 <> n.数量
--t1有,t2没有:select 货号 from table1 a where not exists(select 1 from table2 where 货号=a.货号)--货号有,数量不同: select 货号,数量 from table1 a where exists(select 1 from table2 where 货号=a.货号) and not exists(select 1 from table2 where 货号=a.货号 and 数量=a.数量)
select t1.* from table1 t1 where 货号 not in (select 货号 from table2) and 数量 <> (select 数量 from table2 t2 where t2.货号 = t1.货号)
select 货号 from table1 except select 货号 from table2 union all select 货号 from table1 as T1 inner join table2 as T2 on T1.货号=T2.货号 where T1.数量<>T2.数量
select 货号 from table1 a where not exists(select 1 from table2 where 货号=a.货号) union all select 货号 from table1 a where exists(select 1 from table2 where 货号=a.货号) and not exists(select 1 from table2 where 货号=a.货号 and 数量=a.数量)
union all
select m.货号 from table1 m , table2 n where m.货号 = n.货号 and m.数量 <> n.数量
select m.货号 from table1 m where m.货号 not in (select 货号 from table2)
union all
select m.货号 from table1 m , table2 n where m.货号 = n.货号 and m.数量 <> n.数量方法二:
select m.货号 from table1 m where not exists (select 1 from table2 n where n.货号 = m.货号)
union all
select m.货号 from table1 m , table2 n where m.货号 = n.货号 and m.数量 <> n.数量
select 货号,数量 from table1 a where exists(select 1 from table2 where 货号=a.货号) and
not exists(select 1 from table2 where 货号=a.货号 and 数量=a.数量)
and 数量 <> (select 数量 from table2 t2 where t2.货号 = t1.货号)
except
select 货号 from table2
union all
select 货号 from table1 as T1
inner join table2 as T2
on T1.货号=T2.货号
where T1.数量<>T2.数量
union all
select 货号 from table1 a where exists(select 1 from table2 where 货号=a.货号) and
not exists(select 1 from table2 where 货号=a.货号 and 数量=a.数量)