根据B表的数据过滤A表记录的问题

[nickycheng]

A表
ID NAME

B表
A_ID U_CODE
==================

比如A中数据有
1 张三
2 李四
3 王二
4 麻子

B表数据有
1 sam
3 sam
1 rose
2 mike
表示 sam 可以访问id=1 3的a表数据， rose可以访问id=1的a表数据，mike可以访问id=2的a表数据

如果对于这样的查询我们可以用inner join：
select * from a inner join b on b.a_id=a.id and u_code='sam'

但是我现在想加入一个条件，如果B表中不存在的A_ID，我也需要能选择出来。
也就是说id=4的a表记录不包含在b表中，那么sam可以访问id=1 3 4的a表记录。

这样的查询语句怎么写比较高效一些？谢谢！

[测试逍遥子]

子查询

[waterfirer]

select * from a where a.a_id in (select a_id from b where u_code='sam') or a.a_id not in (select a_id from b)

[小灰狼W]

[Quote=引用 2 楼 waterfirer 的回复:]
select * from a where a.a_id in (select a_id from b where u_code='sam') or a.a_id not in (select a_id from b)
[/Quote]

支持
我再写个麻烦点的代码

select * from test_a a inner join  
(select * from test_b union 
  select id A_ID,U_code from (select * from test_a a1 where not exists(select 1 from test_b where a_id=a1.id))b1, test_b
  )b on a.id=b.a_id 
 where b.u_code='sam'

[枫。晨夕]

select * from a where a.id in (select a_id from b where u_code = 'sam' group by a_id)
union select * from a where a.id not in (select a_id from b group by a_id)

[ojuju10]

--用left join
select * from a left join b on b.a_id=a.id and u_code='sam'

[inthirties]

select * from a left join b on a.id = b.id where a.name = 'sam' or a.name is null

[inthirties]

select a.* from a left join b on a.id = b.id where b.name = 'sam' or b.name is null

[nickycheng]

select * from a where a.a_id in (select a_id from b where u_code='sam') or a.a_id not in (select a_id from b)


select * from test_a a inner join
(select * from test_b union
select id A_ID,U_code from (select * from test_a a1 where not exists(select 1 from test_b where a_id=a1.id))b1, test_b
)b on a.id=b.a_id
where b.u_code='sam'


其实我主要关注的就是查询的效率问题，在实际的情况b表的u_code有500个左右的时候，上面哪种可能会好些？或者有其他更好的查询方式？

[小灰狼W]

[Quote=引用 8 楼 nickycheng 的回复:]
select * from a where a.a_id in (select a_id from b where u_code='sam') or a.a_id not in (select a_id from b)


select * from test_a a inner join 
(select * from test_b union
  select id A_ID,U_code from (select * from test_a a1 where not exists(select 1 from test_b where a_id=a1.id))b1, test_b
  )b on a.id=b.a_id
where b.u_code='sam'


其实我主要关注的就是查询的效率问题，在实际的情况b表的u_code有500个左右的时候，上面哪种可能会好些？或者有其他更好的查询方式？
[/Quote]

效率的话，用7楼的吧

[nickycheng]

[Quote=引用 7 楼 inthirties 的回复:]
select a.* from a left join b on a.id = b.id where b.name = 'sam' or b.name is null
[/Quote]


嗯，谢谢，这样的不错。

[wanglei8]

select a.id, a.name, b.a_id, b.u_code
from a, b
where a.id = b.id(+)
and (b.id is null or b.u_code = 'sam')

[阿三]

用左连接吧
select * from ta left join tb on ta.id=tb.a_id and tb.u_code='sam'