SQL 连接与 NULL：让零订单用户也正确出现在结果里

import java.util.*;
public class Main {
    record Order(long id,Long userId,String status) {}
    record Row(long userId,Long orderId) {}
    static List<Row> leftJoin(List<Long> users,List<Order> orders) {
        Map<Long,List<Order>> byUser=new HashMap<>();
        for (Order order:orders) {
            // NULL不参与普通等值匹配，状态限制属于ON条件。
            if (order.userId()==null||!"PAID".equals(order.status())) continue;
            byUser.computeIfAbsent(order.userId(),k->new ArrayList<>()).add(order);
        }
        Set<Long> seen=new HashSet<>();
        List<Row> rows=new ArrayList<>();
        for (Long user:users) {
            if (user==null||!seen.add(user)) throw new IllegalArgumentException("user primary key");
            List<Order> matches=byUser.getOrDefault(user,List.of());
            if (matches.isEmpty()) rows.add(new Row(user,null)); // 保留零匹配用户。
            else for (Order order:matches) rows.add(new Row(user,order.id()));
        }
        return List.copyOf(rows);
    }
    static long countOrders(List<Row> rows,long user) {
        // 类比COUNT(o.id)，忽略补空行而非统计全部行。
        return rows.stream().filter(r->r.userId()==user&&r.orderId()!=null).count();
    }
    static void eq(Object expected,Object actual) {
        if (!Objects.equals(expected,actual)) throw new AssertionError(actual);
    }
    public static void main(String[] args) {
        List<Row> rows=leftJoin(List.of(1L,2L,3L),List.of(
            new Order(11,1L,"PAID"),new Order(12,2L,"CANCELLED"),
            new Order(13,1L,"PAID"),new Order(14,null,"PAID")));
        eq(List.of(new Row(1,11L),new Row(1,13L),new Row(2,null),new Row(3,null)),rows);
        eq(2L,countOrders(rows,1));
        eq(0L,countOrders(rows,2));
        eq(1L,rows.stream().filter(r->r.userId()==2).count());
        eq(List.of(),leftJoin(List.of(),List.of(new Order(1,1L,"PAID"))));
        eq(List.of(new Row(9,null)),leftJoin(List.of(9L),List.of()));
        try { leftJoin(List.of(1L,1L),List.of()); throw new AssertionError("duplicate"); }
        catch (IllegalArgumentException expected) {}
        System.out.println("PASS 7 left join tests");
    }
}