select isnull(a.id,b.id) as id , a.t1,b.t2 from table1 a full join table2 b on a.id = b.id
---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-06-17 21:13:00 ---------------------------------------------------------------- --> 测试数据:[table1] if object_id('[table1]') is not null drop table [table1] create table [table1]([id] int,[t1] int) insert [table1] select 1,100 union all select 2,200 --> 测试数据:[table2] if object_id('[table2]') is not null drop table [table2] create table [table2]([id] int,[t2] int) insert [table2] select 2,200 union all select 3,300 --------------开始查询-------------------------- select isnull(a.id,b.id) as id , a.t1,b.t2 from table1 a full join table2 b on a.id = b.id ----------------结果---------------------------- /* id t1 t2 1 100 null 2 200 200 3 null 300 */
select isnull(a.id,b.id) as id , a.t1,b.t2 from table1 a join table2 b on a.id = b.id
create table table1 ( id int, t1 int )create table table2 ( id int, t2 int )insert table1 select 1, 100 union select 2, 200 insert table2 select 2, 200 union select 3, 300 select isnull(table1.id,table2.id) as id, t1, t2 from table1 full join table2 on table1.id = table2.id drop table table1 drop table table2结果: 1 100 NULL 2 200 200 3 NULL 300
IF OBJECT_ID('LI') IS NOT NULL DROP TABLE LI GO
IF OBJECT_ID('LI2') IS NOT NULL DROP TABLE LI2 GO CREATE TABLE LI(ID INT ,T1 INT ) INSERT INTO LI(ID,T1) SELECT 1,100 UNION ALL SELECT 2,200 GO CREATE TABLE LI2(ID INT,T2 INT) INSERT INTO LI2(ID,T2) SELECT 2,200 UNION ALL SELECT 3,300 GO --结果 SELECT ISNULL(L.ID,T.ID) AS ID ,L.T1,T.T2 FROM LI L FULL JOIN LI2 T ON L.ID = T.ID /* ID T1 T2 ----------- ----------- ----------- 1 100 NULL 2 200 200 3 NULL 300(3 行受影响) */
if object_id('[table1]') is not null drop table [table1] create table [table1]([id] int,[t1] int) insert [table1] select 1,100 union all select 2,200 --> 测试数据:[table2] if object_id('[table2]') is not null drop table [table2] create table [table2]([id] int,[t2] int) insert [table2] select 2,200 union all select 3,300select id=ISNULL(a.id,b.id),t1,t2 from table1 a full join table2 b on a.id=b.id /*---------------- 1 100 NULL 2 200 200 3 NULL 300 ---------------*/
from table1 a full join table2 b on a.id = b.id
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-17 21:13:00
----------------------------------------------------------------
--> 测试数据:[table1]
if object_id('[table1]') is not null drop table [table1]
create table [table1]([id] int,[t1] int)
insert [table1]
select 1,100 union all
select 2,200
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
create table [table2]([id] int,[t2] int)
insert [table2]
select 2,200 union all
select 3,300
--------------开始查询--------------------------
select isnull(a.id,b.id) as id , a.t1,b.t2
from table1 a full join table2 b on a.id = b.id
----------------结果----------------------------
/*
id t1 t2
1 100 null
2 200 200
3 null 300
*/
a.t1,b.t2
from table1 a join table2 b on a.id = b.id
(
id int,
t1 int
)create table table2
(
id int,
t2 int
)insert table1
select 1, 100 union
select 2, 200 insert table2
select 2, 200 union
select 3, 300 select
isnull(table1.id,table2.id) as id,
t1,
t2
from table1
full join table2 on table1.id = table2.id
drop table table1
drop table table2结果:
1 100 NULL
2 200 200
3 NULL 300
IF OBJECT_ID('LI') IS NOT NULL
DROP TABLE LI
GO
IF OBJECT_ID('LI2') IS NOT NULL
DROP TABLE LI2
GO CREATE TABLE LI(ID INT ,T1 INT )
INSERT INTO LI(ID,T1)
SELECT 1,100 UNION ALL
SELECT 2,200
GO CREATE TABLE LI2(ID INT,T2 INT)
INSERT INTO LI2(ID,T2)
SELECT 2,200 UNION ALL
SELECT 3,300
GO --结果
SELECT ISNULL(L.ID,T.ID) AS ID ,L.T1,T.T2
FROM LI L FULL JOIN LI2 T
ON L.ID = T.ID /*
ID T1 T2
----------- ----------- -----------
1 100 NULL
2 200 200
3 NULL 300(3 行受影响)
*/
create table [table1]([id] int,[t1] int)
insert [table1]
select 1,100 union all
select 2,200
--> 测试数据:[table2]
if object_id('[table2]') is not null drop table [table2]
create table [table2]([id] int,[t2] int)
insert [table2]
select 2,200 union all
select 3,300select id=ISNULL(a.id,b.id),t1,t2 from table1 a full join table2 b on a.id=b.id
/*----------------
1 100 NULL
2 200 200
3 NULL 300
---------------*/