if object_id('[table1]') is not null drop table [table1] go create table [table1]([id] int,[name] varchar(3)) insert [table1] select 1,'abc' union all select 2,'def' if object_id('[cardtable2]') is not null drop table [cardtable2] go create table [cardtable2]([usedid] int,[used] int,[usedtime] datetime) insert [cardtable2] select 0,0,null union all select 1,1,'2008-3-3' union all select 2,1,'2008-3-3' union all select 0,0,nullselect cardtable2.*, table1.name from cardtable2 left JOIN table1 on cardtable2.usedid=table1.id/** usedid used usedtime name ----------- ----------- ------------------------------------------------------ ---- 0 0 NULL NULL 1 1 2008-03-03 00:00:00.000 abc 2 1 2008-03-03 00:00:00.000 def 0 0 NULL NULL(所影响的行数为 4 行) **/
create table table1(id int,name varchar(3)) insert table1 select 1,'abc' union all select 2,'def' create table cardtable2(usedid int,used int,usedtime datetime) insert cardtable2 select 0,0,null union all select 1,1,'2008-3-3' union all select 2,1,'2008-3-3' union all select 0,0,null goselect m.* , isnull(n.name,'0') usedname from cardtable2 m left join table1 n on m.usedid = n.id and used = 1drop table table1 , cardtable2/* usedid used usedtime usedname ----------- ----------- ------------------------------------------------------ -------- 0 0 NULL 0 1 1 2008-03-03 00:00:00.000 abc 2 1 2008-03-03 00:00:00.000 def 0 0 NULL 0(所影响的行数为 4 行) */create table table1(id int,name varchar(3)) insert table1 select 1,'abc' union all select 2,'def' create table cardtable2(usedid int,used int,usedtime datetime) insert cardtable2 select 0,0,null union all select 1,1,'2008-3-3' union all select 2,1,'2008-3-3' union all select 0,0,null goselect m.* , isnull(n.name,'0') usedname from cardtable2 m left join table1 n on m.usedid = n.id and used = 1drop table table1 , cardtable2/* usedid used usedtime usedname ----------- ----------- ------------------------------------------------------ -------- 0 0 NULL 0 1 1 2008-03-03 00:00:00.000 abc 2 1 2008-03-03 00:00:00.000 def 0 0 NULL 0(所影响的行数为 4 行) */
select cardtable2.*,table1.name from cardtable2 LEFT JOIN table1 on cardtable2.usedid=table1.id
go
create table [table1]([id] int,[name] varchar(3))
insert [table1]
select 1,'abc' union all
select 2,'def'
if object_id('[cardtable2]') is not null drop table [cardtable2]
go
create table [cardtable2]([usedid] int,[used] int,[usedtime] datetime)
insert [cardtable2]
select 0,0,null union all
select 1,1,'2008-3-3' union all
select 2,1,'2008-3-3' union all
select 0,0,nullselect
cardtable2.*,
table1.name
from cardtable2
left JOIN table1
on cardtable2.usedid=table1.id/**
usedid used usedtime name
----------- ----------- ------------------------------------------------------ ----
0 0 NULL NULL
1 1 2008-03-03 00:00:00.000 abc
2 1 2008-03-03 00:00:00.000 def
0 0 NULL NULL(所影响的行数为 4 行)
**/
insert table1
select 1,'abc' union all
select 2,'def'
create table cardtable2(usedid int,used int,usedtime datetime)
insert cardtable2
select 0,0,null union all
select 1,1,'2008-3-3' union all
select 2,1,'2008-3-3' union all
select 0,0,null
goselect m.* , isnull(n.name,'0') usedname
from cardtable2 m left join table1 n
on m.usedid = n.id and used = 1drop table table1 , cardtable2/*
usedid used usedtime usedname
----------- ----------- ------------------------------------------------------ --------
0 0 NULL 0
1 1 2008-03-03 00:00:00.000 abc
2 1 2008-03-03 00:00:00.000 def
0 0 NULL 0(所影响的行数为 4 行)
*/create table table1(id int,name varchar(3))
insert table1
select 1,'abc' union all
select 2,'def'
create table cardtable2(usedid int,used int,usedtime datetime)
insert cardtable2
select 0,0,null union all
select 1,1,'2008-3-3' union all
select 2,1,'2008-3-3' union all
select 0,0,null
goselect m.* , isnull(n.name,'0') usedname
from cardtable2 m left join table1 n
on m.usedid = n.id and used = 1drop table table1 , cardtable2/*
usedid used usedtime usedname
----------- ----------- ------------------------------------------------------ --------
0 0 NULL 0
1 1 2008-03-03 00:00:00.000 abc
2 1 2008-03-03 00:00:00.000 def
0 0 NULL 0(所影响的行数为 4 行)
*/