Question

SQL中如何查询A表全部数据与A表有关联的B表部分数据

Answer 1

1、创建测试表，

create table test_col_1(id number, var varchar2(200));

create table test_col_2(id number, var varchar2(200));

2、插入测试数据，

insert into test_col_1 

select level*8, 'var'||level*8 from dual connect by level <= 20;

insert into test_col_2 

select level, 'var'||level from dual connect by level <= 100;

3、查询A表与B表关联记录，

select *

  from test_col_2 b

 where exists (select 1 from test_col_1 a where b.id = a.id)

4、查询A表全部数据及A、B有关联的数据，

select *

  from test_col_1 a

union all

select *

  from test_col_2 b

 where exists (select 1 from test_col_1 a where b.id = a.id)

Answer 2

左连Left Join
表B中存了表A要的信息，相连字段假设是AID，即B表中有个字段AID，是与A表中ID相关联的
SELECT A.*, B.你要的字段 FROM A
LEFT JOIN B ON A.ID = B.AID

Answer 3

1. 当b表没条件时，select * from A a left join B b on a.ID=b.ID

2. 当b表有条件时

   SELECT a.*,b.*  FROM a

   left join(SELECT * FROM b as bb WHERE bb.Field=1) AS b ON a.ID=b.ID

   或者

   with t as (select * from b where b.Field=1) select * from t right join  a on t.ID=a.ID

Answer 4

select * from 表A名称 a left join 表B名称 b on a.关联字段=b.关联字段