How do I add a boolean column to an existing table to indicate if there's a matching id value f-CodePudding

Suppose I have a table, foo

and another table bar

id product
 1     abc
 1     def
 4     ghi
 4     abc

I want to add a boolean field, has_product to foo that indicates whether it has at least one record in bar with a matching id. In this example,

id has_product
 1        true
 2       false
 3       false
 4        true

How can I do this?

Reproducible Example

CREATE OR REPLACE TABLE test.foo AS

(
  SELECT 1 AS id
  UNION ALL SELECT 2 AS id
  UNION ALL SELECT 3 AS id
  UNION ALL SELECT 4 AS id
);

CREATE OR REPLACE TABLE test.bar AS

(
  SELECT 1 AS id, "abc" as product
  UNION ALL SELECT 1 AS id, "def" as product
  UNION ALL SELECT 4 AS id, "ghi" as product
  UNION ALL SELECT 4 AS id, "abc" as product
);

What I've tried

I suspect there's a combination of

CodePudding user response：

If you just want to view this particular output, you may use a left anti-join:

SELECT DISTINCT f.id, b.id IS NOT NULL AS has_product
FROM foo f
LEFT JOIN bar b
    ON b.id = f.id