a 表
id aname
1 a1
2 a2
4 a4 <==b表中没有b 表
id bname
1 b1
2 b2
3 b3 <== a表中没有
====================
需要把 a表中有的数据和没有的数据都搜索出来1 a1 b1
2 a2 b2
3 null b3
4 a4 b4
====
我是用的
left join 和right join 然后用union连接select id ,aname,bname from b
left join a on a.id=b.id
union
select id,aname,bname from b
right join a on a.id=b.id
===请问还有更好的方法
id aname
1 a1
2 a2
4 a4 <==b表中没有b 表
id bname
1 b1
2 b2
3 b3 <== a表中没有
====================
需要把 a表中有的数据和没有的数据都搜索出来1 a1 b1
2 a2 b2
3 null b3
4 a4 b4
====
我是用的
left join 和right join 然后用union连接select id ,aname,bname from b
left join a on a.id=b.id
union
select id,aname,bname from b
right join a on a.id=b.id
===请问还有更好的方法
需要把 有的数据和没有的数据都搜索出来1 a1 b1
2 a2 b2
3 null b3
4 a4 null
create table t2(id int,bname varchar(10))
insert into t1
select 1,'a1' union all
select 2,'a2' union all
select 4,'a4'insert into t2
select 1,'b1' union all
select 2,'b2' union all
select 3,'b3'
---------------------------
select isnull(t1.id,t2.id) as id ,aname,bname from t1
full join t2
on t1.id=t2.id/*
id aname bname
----------- ---------- ----------
1 a1 b1
2 a2 b2
3 NULL b3
4 a4 NULL*/drop table t1,t2
drop table ta
gocreate table ta
(
id int,
aname varchar(10)
)insert into ta(id,aname) values(1,'a1')
insert into ta(id,aname) values(2,'a2')
insert into ta(id,aname) values(4,'a4')if object_id('pubs..tb') is not null
drop table tb
gocreate table tb
(
id int,
bname varchar(10)
)insert into tb(id,bname) values(1,'a1')
insert into tb(id,bname) values(2,'a2')
insert into tb(id,Bname) values(3,'a3')select isnull(ta.id,tb.id) as id,isnull(ta.aname,null) as aname,isnull(tb.bname,null) as bname
from ta full join tb on ta.id = tb.id
order by ta.iddrop table ta
drop table tb
id aname bname
----------- ---------- ----------
1 a1 a1
2 a2 a2
3 NULL a3
4 a4 NULL(所影响的行数为 4 行)
id aname ver
1 a1 2
2 a2 1
4 a4 <==b表中没有b 表
id bname ver
1 b1 2
2 b2 2
3 b3 <== a表中没有
需要把 有的数据和没有的数据都搜索出来 and b.ver-a.ver >02 a2 b2
3 NULL b3
4 a4 NULLselect isnull(t1.id,t2.id) as id ,aname,bname from t1
full join t2
on t1.id=t2.id
where(b.ver is null or b.ver-isnull(a.ver,0)) <===or还有其它方法没有?
/* SELECT c.cid, a.aname, b.bname
/* FROM (SELECT a.aid AS cid
/* FROM a
/* UNION
/* SELECT b.bid AS cid
/* FROM b) c
/* LEFT JOIN a ON c.cid = a.aid
/* LEFT JOIN b ON c.cid = b.bid
--------------------------------