Pattern

Semi-join

The rows that have at least one match, each kept once.

SELECT p.*
FROM products p
WHERE EXISTS (SELECT 1 FROM order_lines l WHERE l.product_id = p.product_id);

A plain JOIN would repeat a product once per sale.

SELECT COUNT(*) AS products,
       COUNT(*) FILTER (WHERE EXISTS (SELECT 1 FROM order_lines l WHERE l.product_id = p.product_id)) AS sold
FROM products p;

products	sold
80	74

products

sold

Try it in Randori

Practise:Customers who ordered