SQL
Retrieve User Details and Products They've Ordered Using INNER JOIN
Learn to combine data from multiple tables using an INNER JOIN to fetch user information along with details of all products they have purchased, demonstrating a common multi-table query pattern.
SELECT
u.user_id,
u.username,
p.product_name,
p.price,
oi.quantity
FROM
users u
INNER JOIN
orders o ON u.user_id = o.user_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
u.is_active = TRUE
ORDER BY
u.username, p.product_name;
How it works: This SQL snippet demonstrates a multi-table INNER JOIN, a fundamental operation for combining related data from several tables. It retrieves details about active users, their orders, the specific items in those orders, and the corresponding product information. The INNER JOIN clauses ensure that only rows with matching values in all joined tables are returned, providing a comprehensive view of what active users have purchased.