does LEFT JOIN return same row count as left table?

Question

SELECT * 
FROM t1
LEFT JOIN t2 ON t1.fk = t2.id;

Will it always return the same row count as that of t1 ?

Answer 1

The contract of a left join states that, in the absence of a WHERE clause which might remove records from the result set, all records which appear in the left side of the join will appear at least once. Consider the following data set:

t1
id | fk
1  | 1
2  | 2

t2
id | value
1  | 1
1  | 2

Your query would return this result set:

id | fk | id   | value
1  | 1  | 1    | 1
1  | 1  | 1    | 2
2  | 2  | NULL | NULL

Note carefully that the first table's fk = 2 did not match anything to the second table. This record still appears in the result set, but all columns coming from the second table are NULL. Also, note that fk = 1 records appear twice, because that single record in the first table matched twice to the second table.