关于minus 求助!

[dctg2003]

假设有2张表A,B
A表有
NAME NO
DC 1
DW 2
DF 3
B表有
NAME NO
DC 1
DZ 6
DS 7

我要找出A表中没有的完整数据
select B_NAME_NAME from B minus select A_NAME from A ;
这样查出来的是
NAME
DZ
DS
但是我想查出的是B表在A表中没有的全部数据
请问如何写?

[hyrongg]

是这样子的吗？

SQL> create table a(name nvarchar2(30),no int);

Table created

SQL> create table b(name nvarchar2(30),no int);

Table created

SQL> 
SQL> insert into a
  2  select 'dc',1 from dual union select 'dw',2 from dual union select 'df',3 from dual;

3 rows inserted

SQL> insert into b
  2  select 'dc',1 from dual union select 'dz',6 from dual union select 'ds',7 from dual;

3 rows inserted

SQL> select * from a;

NAME                                                                                              NO
------------------------------------------------------------ ---------------------------------------
dc                                                                                                 1
df                                                                                                 3
dw                                                                                                 2

SQL> select * from b;

NAME                                                                                              NO
------------------------------------------------------------ ---------------------------------------
dc                                                                                                 1
ds                                                                                                 7
dz                                                                                                 6

SQL> select * from b where not exists(select 1 from a where a.name=b.name);

NAME                                                                                              NO
------------------------------------------------------------ ---------------------------------------
ds                                                                                                 7
dz                                                                                                 6

SQL> 

[dctg2003]

A表的数据不一定只有1....有可能有,2,3,4,5...

[popeye627]

--LZ用*不就行了
select *
  from (select 'dc' NAME, 1 no
          from dual
        union
        select 'dz', 6
          from dual
        union
        select 'ds', 7 from dual) B
minus
select *
  from (select 'dc', 1
          from dual
        union
        select 'dw', 2
          from dual
        union
        select 'df', 3 from dual) A
--------------------------------------------
   	NAME	NO
1	ds	7
2	dz	6

[dctg2003]

数据很多的,我只是举例子,我不可能全部列出来吧

[dctg2003]

如果2个表里面有几千条的数据的话.请问如何处理?

[hyrongg]

我晕，

select * from b where not exists(select 1 from a where a.name=b.name);

这里面的 1 并不是代表只有1，

任何数据都行呀，汗，建议楼主从基础学起

[dctg2003]

我正在学习..呵呵,受教了

[ruihuahan]

完整数据
==============
要取A表的所有列吗？

[dctg2003]

是的.已经想到了
selec * from b
where b.b_name in

( select B_NAME from B minus select A_NAME from A )

[ruihuahan]

6楼的就可以了，还可以用外连接实现。

select b.* from b left outer join a
on b.name = a.name
where a.name is null;

[popeye627]

是的.已经想到了
selec * from b
where b.b_name in
( select B_NAME from B minus select A_NAME from A )
------------------
LZ这样简直是多此一举！
既然你要用minus，还不如这样呢：
select * from B
minus
select * from A

[dctg2003]

select * from b where not exists(select 1 from a where a.name=b.name);
的确简单很多.但是我的实际的2张表的结构还有细微的差别.所以我用IN了,感谢几位朋友