子查询

exists 子查询

select
  *
from
  a
where
   exists (
    select
      id
    from
      b
    where
      name = '市场部'
      and a.b_id = id
  );

in 子查询

select
  *
from
  a
where
  b_id in (
    select
      id
    from
      b
    where
      name = '市场部'
  );

标量子查询

select
  id,
  name,
  (
    select
      name
    from
      b
    where
      a.b_id = b.id
  ) as b_name
from
  a;