tagged_union.plsql

create table apples (
  apple_id   int  not null generated by default as identity primary key,
  apple_name text not null
);

create table oranges (
  orange_id   int  not null generated by default as identity primary key,
  orange_name text not null
);

create table bananas (
  banana_id   int  not null generated by default as identity primary key,
  banana_name text not null
);

create table baskets (
  basket_id   int  not null generated by default as identity primary key,
  basket_name text not null
);

create type fruit as enum('apple', 'orange', 'banana');

create table basket_fruits (
  basket_id  int   not null references baskets,
  fruit_kind fruit not null,
  apple_id   int   references apples,
  orange_id  int   references oranges,
  banana_id  int   references bananas,
  quantity   int   not null default 1,
  unique(basket_id, apple_id),
  unique(basket_id, orange_id),
  unique(basket_id, banana_id),
  check(
    (fruit_kind = 'apple')  = (apple_id is not null) and
    (fruit_kind = 'orange') = (orange_id is not null) and
    (fruit_kind = 'banana') = (banana_id is not null)
  )
);

   select basket_name,
          fruit_kind,
          coalesce(apple_id, orange_id, banana_id) as fruit_id,
          coalesce(apple_name, orange_name, banana_name) as fruit_name,
          quantity
     from basket_fruits
     join baskets using (basket_id)
left join apples  using (apple_id)
left join oranges using (orange_id)
left join bananas using (banana_id)
 order by basket_name, fruit_kind, fruit_name;