求两个表列与行的差异对比的sql 语句

[tony]

有两个两个表table1 table2
table1如下：
row1 row2
a o
b p
c q
d s

table2如下
row1 row2
a o
b p
c x
d y
e z

我想找出这两个边的差异并输出 table3
结果应该为
row1 row2
c x
d y
e z

这个sql 语句该怎么写，

[天-笑]

select * from table1 a inner join table2 b on a.row1 = b.row1 and a.row2<>b.row2

[Felixzhaowenzhong]

select * from tb2 except select * from tb1

[Felixzhaowenzhong]

create table tb1(row1 char(1),row2 char(2))
create table tb2(row1 char(1),row2 char(2))
insert tb1  
select  'a','o' union 
select  'b','p' union
select  'c','q' union
select  'd','s'  
insert tb2
select  'a','o' union
select  'b','p' union
select  'c','x' union  
select  'd','y' union
select  'e','z'

select * from tb2 except select * from tb1
/*
row1	row2
c	x 
d	y 
e	z */

[开着拖拉机泡妞]

select * table2 a where not exists(select 1 from table1 b where a.col1=b.col1 and a.col2<>b.col2)

[tony]

[Quote=引用 4 楼 的回复:]
select * table2 a where not exists(select 1 from table1 b where a.col1=b.col1 and a.col2<>b.col2)
[/Quote]

没看明白你这个a ,b代表的是什么意思，麻烦再解释一下

我的问题可能没有写清楚，
table1的内容如下
列1 列2
a | o
b | p
c | q

table2的内容是:
列1 列2
a | o
b | p
c | x
d | y
e | z

我想输入的是 列1 列2
c x
d y
e z

[十三门徒]

select * from tb2 except select * from tb1

[tony]

[Quote=引用 6 楼 的回复:]
SQL code
select * from tb2 except select * from tb1
[/Quote]

我用这条语句，但提示
服务器: 消息 156，级别 15，状态 1，行 1
在关键字 'except' 附近有语法错误。

[十三门徒]

你是什么版本的sql?

[tony]

[Quote=引用 8 楼 的回复:]
你是什么版本的sql?
[/Quote]

我的是sql server 2000，所以不支持这个命令

[十三门徒]

select * from tb2 a where not exists(
select 1 from tb1 b where a.row1=b.row1 and a.row2=b.row2)

那就是这个了
这个就搞定了

[开着拖拉机泡妞]

--> 测试数据：[table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1]([row1] varchar(1),[row2] varchar(1))
insert [table1]
select 'a','o' union all
select 'b','p' union all
select 'c','q' union all
select 'd','s'
--> 测试数据：[table2]
if object_id('[table2]') is not null drop table [table2]
create table [table2]([row1] varchar(1),[row2] varchar(1))
insert [table2]
select 'a','o' union all
select 'b','p' union all
select 'c','x' union all
select 'd','y' union all
select 'e','z'

select * from table2 a--给table2取别名a
where not exists(select 1 from table1 b --给table1取别名a
where a.row1=b.row1 and a.row2=b.row2)

/*
row1	row2
c	x
d	y
e	z
*/

[开着拖拉机泡妞]

--> 测试数据：[table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1]([row1] varchar(1),[row2] varchar(1))
insert [table1]
select 'a','o' union all
select 'b','p' union all
select 'c','q' union all
select 'd','s'
--> 测试数据：[table2]
if object_id('[table2]') is not null drop table [table2]
create table [table2]([row1] varchar(1),[row2] varchar(1))
insert [table2]
select 'a','o' union all
select 'b','p' union all
select 'c','x' union all
select 'd','y' union all
select 'e','z'

select * from table2 a--给table2取别名a
where not exists(select 1 from table1 b --给table1取别名a
where a.row1=b.row1 and a.row2=b.row2)

/*
row1	row2
c	x
d	y
e	z
*/

[开着拖拉机泡妞]

用except的结果是错的，你的2000的数据库也不能用

[tony]

问题解决了，很久没有用数据库了，谢谢大家

[liziwu666111]

select * from Table2 where (row1+row2) not in (select a.row1+a.row2 from table1 a join Table2 b on a.row1=b.row1 and a.row2=b.row2)