Real QA Scenarios With Joins

-- Orders referencing a customer that no longer exists
SELECT o.order_id, o.customer_id, o.total_amount, o.order_date
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

-- Payments referencing a non-existent order
SELECT p.payment_id, p.order_id, p.amount, p.payment_date
FROM payments p
LEFT JOIN orders o ON p.order_id = o.order_id
WHERE o.order_id IS NULL;

-- Order items referencing a product that was deleted
SELECT oi.item_id, oi.product_id, oi.quantity
FROM order_items oi
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE p.product_id IS NULL;

-- Order total does not match sum of line items
SELECT
  o.order_id,
  o.total_amount AS stored_total,
  SUM(oi.quantity * oi.unit_price) AS calculated_total,
  o.total_amount - SUM(oi.quantity * oi.unit_price) AS difference
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount != SUM(oi.quantity * oi.unit_price);

-- Payment amount does not match order amount
SELECT
  o.order_id,
  o.total_amount AS order_amount,
  p.amount AS payment_amount,
  o.total_amount - p.amount AS difference
FROM orders o
INNER JOIN payments p ON o.order_id = p.order_id
WHERE o.total_amount != p.amount
  AND o.status = 'completed';

-- Duplicate orders: same customer, same amount, same date
-- (possible double-submit bug)
SELECT
  a.order_id AS order_1,
  b.order_id AS order_2,
  a.customer_id,
  a.total_amount,
  a.order_date
FROM orders a
INNER JOIN orders b
  ON a.customer_id = b.customer_id
  AND a.total_amount = b.total_amount
  AND a.order_date = b.order_date
  AND a.order_id < b.order_id;

-- Customers with duplicate emails (unique constraint missing?)
SELECT
  a.customer_id AS id_1,
  b.customer_id AS id_2,
  a.email,
  a.first_name AS name_1,
  b.first_name AS name_2
FROM customers a
INNER JOIN customers b
  ON a.email = b.email
  AND a.customer_id < b.customer_id;

-- Orders marked "shipped" but no shipment record exists
SELECT o.order_id, o.status, o.order_date
FROM orders o
LEFT JOIN shipments s ON o.order_id = s.order_id
WHERE o.status = 'shipped'
  AND s.shipment_id IS NULL;

-- Orders with payment but still in "pending" status
SELECT
  o.order_id,
  o.status AS order_status,
  p.payment_status,
  p.payment_date
FROM orders o
INNER JOIN payments p ON o.order_id = p.order_id
WHERE p.payment_status = 'completed'
  AND o.status = 'pending';
-- This is a bug: payment succeeded but order was not updated