Retrieving Data from Multiple Related Tables with INNER JOINs

SELECT
    c.customer_name,
    o.order_id,
    o.order_date,
    p.product_name,
    oi.quantity,
    oi.price_per_unit
FROM
    customers c
INNER JOIN
    orders o ON c.customer_id = o.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';

How it works: This SQL query demonstrates how to retrieve comprehensive information by joining four different tables: `customers`, `orders`, `order_items`, and `products`. It uses `INNER JOIN` clauses to connect tables based on their common columns, ensuring that only records with matches in all joined tables are returned. The `WHERE` clause further filters results for orders placed after a specific date, allowing for detailed reporting or user-specific data display.