Joining Multiple Tables with INNER JOIN for Related Data

SELECT
  o.order_id,
  c.customer_name,
  p.product_name,
  oi.quantity,
  oi.price_at_order,
  o.order_date
FROM
  orders o
INNER JOIN
  customers c ON o.customer_id = c.customer_id
INNER JOIN
  order_items oi ON o.order_id = oi.order_id
INNER JOIN
  products p ON oi.product_id = p.product_id
WHERE
  o.order_date >= '2023-01-01'
ORDER BY
  o.order_date DESC;

How it works: This query uses `INNER JOIN` to combine rows from multiple tables based on related columns. It's highly useful for displaying a comprehensive view of data that is distributed across a normalized database schema. For instance, it can fetch order details along with the customer's name and the specific product names involved in that order, all in one go. The `ON` clause specifies the join condition, linking tables where their common columns match.