LEFT JOIN
A join that keeps every row of the left table and adds the matching right-hand rows, or NULLs when there is no match.
In more detail
Its result is the inner join plus the unmatched left rows. Count a right-hand column such as COUNT(o.id), not COUNT(*), or an unmatched row counts as one. A WHERE condition on the right table removes the NULL rows and turns it back into an inner join, so put such conditions in ON.
Example
FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.idRelated terms
- outer join — A join that also keeps rows with no match, filling the other table's columns with NULL.
- anti join — A query that returns the rows of one table that have no matching row in another, such as products nobody has ordered.
- inner join — A join that keeps only the pairs of rows that satisfy the ON rule, dropping rows with no match on either side.
- COUNT — An aggregate that counts rows with COUNT(*), or non-NULL values with COUNT(column).
Taught in
- Why Tables And Relations Beat Files — PostgreSQL, Indexing & Relational Data Modeling
- What An Outer Join Really Returns — PostgreSQL, Indexing & Relational Data Modeling
- The N Plus One Problem — PostgreSQL, Indexing & Relational Data Modeling