SQL
Retrieve All Records with Optional Related Data using LEFT JOIN
Use `LEFT JOIN` to retrieve all records from a primary table and their matching records from a secondary table, including primary records that have no matches.
SELECT
p.product_id,
p.product_name,
p.price,
c.category_name,
c.description AS category_description
FROM
products p
LEFT JOIN
categories c ON p.category_id = c.category_id
WHERE
p.is_active = TRUE
ORDER BY
p.product_name;
How it works: This SQL snippet demonstrates how to use a `LEFT JOIN` to retrieve all products, along with their associated category information. If a product does not have a matching category (e.g., `category_id` is NULL or refers to a non-existent category), the product will still be included in the result, and the category-related columns (`category_name`, `category_description`) will show as `NULL`. This is essential for displaying comprehensive lists where related data might be optional or incomplete.