Joining Multiple Tables to Retrieve Related Data

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

How it works: This SQL query uses `INNER JOIN` to retrieve related information from four different tables: `orders`, `customers`, `order_items`, and `products`. By linking tables on their common columns (e.g., `o.customer_id = c.customer_id`), you can consolidate data into a single, comprehensive result set, which is fundamental for displaying complex data on web pages.