SQL Querying

Joins

A join combines rows according to a predicate. Inner, outer, cross, self, semi, and anti joins answer different existence and preservation questions, so the correct form follows from required result cardinality rather than syntax preference.

An inner join returns matching row combinations.
An outer join preserves unmatched rows by null extension.
A cross join produces every left-right pair and is appropriate only when that Cartesian product is the intended domain.
A self join assigns different roles to separate references of the same table, such as a category and its parent.
Semi and anti joins test existence without multiplying left rows.
Join keys and filters must reflect the full relationship, including tenant, version, or effective-time attributes when those are part of identity.

Choose by preservation and multiplicity

Join family comparison
Form	Rows produced	Unmatched handling	Commerce use
Inner	Every matching pair	Discard either-side nonmatches	Orders with their customer
Left outer	Matches plus every left row	Right columns become null	All products, including never-ordered products
Right/full outer	Matches plus preserved side(s)	Missing-side columns become null	Reconciliation between two feeds
Cross	Every possible pair	Not applicable	All size/color combinations
Self	Depends on predicate	Depends on inner/outer form	Category and parent category
Semi (`EXISTS`)	At most one copy of each left row	Keep left row when a match exists	Customers with paid orders
Anti (`NOT EXISTS`)	At most one copy of each left row	Keep left row when no match exists	Products never ordered

Null extension and predicate placement

SELECT p.product_id, p.name, i.order_id
FROM products AS p
LEFT JOIN order_items AS i
  ON i.product_id = p.product_id
 AND i.quantity >= 5
ORDER BY p.product_id, i.order_id;

Products without a qualifying large-quantity line remain, with `i.order_id` null. Moving `i.quantity >= 5` to `WHERE` would remove those null-extended rows.

Self join with explicit roles

SELECT child.name AS category_name,
       parent.name AS parent_name
FROM categories AS child
LEFT JOIN categories AS parent
  ON parent.category_id = child.parent_category_id;

Suppose product 42 has two order lines and product 99 has none. The left join returns two rows for 42 and one null-extended row for 99. If the question is only “which products were ordered?”, EXISTS expresses the needed semi join and returns product 42 once without requiring DISTINCT.