请教一个简单的连接查询的问题。谢谢
现有表1表2
table11,table2

table1:
id value1
1 100
2 200
3 700

table2:
id value2
1 98
3 99

我想得到的查询结果为
id value1 value2
1 100 98
2 200 0 (或者显示null也行）
3 700 99

sql该如何写？
而我实际得到结果中没有2号记录。


[解决办法]

SQL code

select a.id,a.value1,b.value2 from table1 a left join table2 b on a.id=b.id
[解决办法]
SQL code
select  isnull(a.id,b.id) as id,isnull(a.value1,0) as value1,  isnull(b.value2,0) as value2from  table1 a full join table2 bon  a.id=b.id
[解决办法]
select tbl1.id,tbl1.value1,tbl2.value2 from tbl1 left join tbl2 on tbl1.id=tbl2.id

用左连接查询，你可能用了内连接
[解决办法]
SQL code
if object_id('table1') is not null   drop table table1gocreate table table1( id int, value1 int)goinsert into table1select 1,100 union allselect 2,200 union allselect 3,700goif object_id('table2') is not null   drop table table2gocreate table table2( id int, value2 int)goinsert into table2select 1,98 union allselect 3,99goselect a.id,a.value1,b.value2 from table1 a left join table2 b on a.id=b.id --显示nullselect a.id,a.value1,value2=isnull(b.value2,0) from table1 a left join table2 b on a.id=b.id --显示0/*id          value1      value2----------- ----------- -----------1           100         982           200         03           700         99(3 行受影响)*/
[解决办法]
SQL code
create table table1(id int,value1 int)insert into table1 select 1,100insert into table1 select 2,200insert into table1 select 3,700create table table2(id int,value2 int)insert into table2 select 1,98insert into table2 select 3,99goselect a.id,a.value1,b.value2 from table1 a left join table2 b on a.id=b.id/*id          value1      value2----------- ----------- -----------1           100         982           200         NULL3           700         99(3 行受影响)*/godrop table table1,table2
[解决办法]
探讨

SQL code

select
  isnull(a.id,b.id) as id,isnull(a.value1,0) as value1,
  isnull(b.value2,0) as value2
from
  table1 a full join table2 b
on
  a.id=b.id