select * from tablename1 where 字段 not in(select 字段 from tablename2)
解决方案 »
- 网站遇到点麻烦,急求各位帮助。
- 大文本导入到sql serer中的问题
- 请教:sql的跟踪是否影响数据库性能?
- 关于SQLSERVER 2K与ORACLE数据类型的比较
- sql 2005 中的一个问题
- 问一条Sql语句
- 请教,数据库中某列值通过判定时间改变数值
- ********************问个刚才的sql语句***************************
- 第一次来这个论坛提问题,看样子就不了了之了 :( 真的就没有人知道???? 再加分!!!
- 在SQLServer中,如何计算中文的长度?因为中文是2个字节,我要算字节数量,怎么算?
- 100分求教,如何分担数据库压力
- 兄弟们,sql server里,除了update还有没有别的更新记录的命令了?
table1
id name sex
001 name1 f
table2
id name age
002 name2 18结果
id name sex age
001 name1 f
002 name2 18
drop table tb1
gocreate table tb1
(
id varchar(10),
name varchar(10),
sex varchar(10)
)insert into tb1(id,name,sex) values('001','name1','f')
goif object_id('pubs..tb2') is not null
drop table tb2
gocreate table tb2
(
id varchar(10),
name varchar(10),
age int
)insert into tb2(id,name,age) values('002','name2',18)
goselect isnull(tb1.id,tb2.id) as id , isnull(tb1.name,tb2.name) as name , isnull(tb1.sex,null) as sex , isnull(tb2.age,null) as age
from tb1
full join tb2
on tb1.id = tb2.iddrop table tb1,tb2id name sex age
---------- ---------- ---------- -----------
002 name2 NULL 18
001 name1 f NULL(所影响的行数为 2 行)
drop table tb1
gocreate table tb1
(
id varchar(10),
name varchar(10),
sex varchar(10)
)insert into tb1(id,name,sex) values('001','name1','f')
goif object_id('pubs..tb2') is not null
drop table tb2
gocreate table tb2
(
id varchar(10),
name varchar(10),
age int
)insert into tb2(id,name,age) values('002','name2',18)
goselect isnull(tb1.id,tb2.id) as id , isnull(tb1.name,tb2.name) as name , isnull(tb1.sex,null) as sex , isnull(tb2.age,null) as age
from tb1
full join tb2
on tb1.id = tb2.id
order by tb1.iddrop table tb1,tb2
id name sex age
---------- ---------- ---------- -----------
001 name1 f NULL
002 name2 NULL 18(所影响的行数为 2 行)
table1
id name in_num in_time num
001 name1 20 2006-12-10 30
002 name2 18 2006-12-10 25table2
id name out_num out_time num
002 name2 18 2006-12-12 7
001 name1 15 2006-12-13 15结果
id name in_num in_time out_num out_time num
001 name1 20 2006-12-10 30
002 name2 18 2006-12-10 25
002 name2 18 2006-12-12 7
001 name1 15 2006-12-13 15
id name sex
001 name1 f
table2
id name age
002 name2 18结果
id name sex age
001 name1 f
002 name2 18
select isnull(table1.id,table2.id) as id,isnull(table1.name,table2.name) as name,sex,age from table1
full join
table 2
on table1.id=table2.id
from t1
full join t2
on t1.id = t2.id
on table1.id=table2.id去掉
from t1
full join t2
on t1.id = t2.id and t1.name=t2.name
凑巧你这里的id和name都相同,如果不同,就会更体现效果