-- INNER JOIN: only rows with match on both sides
SELECT
  u.name AS customer_name,
  o.id AS order_id,
  p.name AS product_name,
  oi.quantity,
  oi.unit_price_agorot / 100.0 AS unit_price_nis
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 'delivered'
  AND o.ordered_at >= NOW() - INTERVAL '30 days';

-- LEFT JOIN: all users including those without orders
SELECT
  u.name,
  COUNT(o.id) AS total_orders,
  COALESCE(SUM(o.total_agorot), 0) / 100.0 AS total_spent_nis
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status != 'cancelled'
GROUP BY u.id, u.name
ORDER BY total_spent_nis DESC;

Relational Databases — עקרונות ו-SQL

Relational Databases — עקרונות ו-SQL

Relational Databases — עקרונות ו-SQL