sql - 有条件地加入表格

我的测试表是：

create table products (
  product varchar(50) primary key,
  show_reviews boolean
);
create table reviews (
  review_id integer primary key,
  product varchar(50) references products (product),
  review varchar(200)
);
insert into products (product, show_reviews) values
  ('blender', true),
  ('toaster', false),
  ('microwave', true);
insert into reviews (review_id, product, review) values
  (1, 'blender', 'Excellent'),
  (2, 'toaster', 'Terrible'),
  (3, 'toaster', 'Would not buy again'),
  (4, 'microwave', 'Wonderful'),
  (5, 'microwave', 'Splendid');

我正在寻找生成如下结果集的查询：

  product    |  show_reviews  |  review_id  |  review
-------------+----------------+-------------+----------
  blender    |  true          |  1          |  Excellent
  toaster    |  false         |  null       |  null
  microwave  |  true          |  4          |  Wonderful
  microwave  |  true          |  5          |  Splendid

实际上，我想根据reviews的值有条件地加入show_reviews表。我想到的是：

select products.*, rev.*
from products
left join (
    select reviews.*
    from reviews
    join products as p on p.product = reviews.product
    where p.show_reviews = true
  ) as rev on rev.product = products.product;

还有什么别的办法吗？

最佳答案

select *
from
    products p
    left join
    reviews r on r.product = p.product and p.show_reviews
;

关于sql - 有条件地加入表格，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/16652985/