怎么样查询a表中有b表中无的数据

[hckls]

a表：

a b c d
1 2 3 4
2 3 4 5
3 4 5 6


b表

a b c d
1 2 3 4
3 4 5 6


用什么语句可以查出a表中有，而b表中无的数据呢？

[快溜]

select * from a表
except
select * from b表

[-狙击手-]

select *
from ta a
where not exists(select 1 from tb where a.a = b.a)

[幸运的意外]

select a.* from a left outer join b on a.a = b.a and a.b = b.b and a.c = b.c and a.d = b.d where b.a is null

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2011-04-25 15:37:36
-- Verstion:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--	Jul  9 2008 14:43:34 
--	Copyright (c) 1988-2008 Microsoft Corporation
--	Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[a]
if object_id('[a]') is not null drop table [a]
go 
create table [a]([a] int,[b] int,[c] int,[d] int)
insert [a]
select 1,2,3,4 union all
select 2,3,4,5 union all
select 3,4,5,6
--> 测试数据：[b]
if object_id('[b]') is not null drop table [b]
go 
create table [b]([a] int,[b] int,[c] int,[d] int)
insert [b]
select 1,2,3,4 union all
select 3,4,5,6
--------------开始查询--------------------------
select * from a where checksum(*) not in(select checksum(*) from b)
----------------结果----------------------------
/* a           b           c           d
----------- ----------- ----------- -----------
2           3           4           5
*/

[Billy]

[Quote=引用 4 楼 fredrickhu 的回复:]
SQL code
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2011-04-25 15:37:36
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 1……
[/Quote]

真是天才呀！

[lang_love_java]

[Quote=引用 1 楼 ssp2009 的回复:]
SQL code
select * from a表
except
select * from b表
[/Quote]

楼主上面的是SQL server，如果是oracle，就如下
select * from a表
minus
select * from b表

[hckls]

各位大大太厉害了

[renwenli07461]

[renwenli07461]