How does using LEFT JOIN differ from a simple WHERE condition when joining tables?

LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there are no matches in the right table, the right table's fields will be filled with NULL.

The WHERE condition when joining tables is usually used with INNER JOIN (or without JOIN, just listing tables), and it filters rows after the join, leaving only those where the condition is true.

Main difference:

• LEFT JOIN preserves all rows from the left table, even if there are no matches in the right.
• Using a WHERE condition to filter by the right table's fields after a LEFT JOIN can effectively turn it into an INNER JOIN, as rows with NULL in the right table will be filtered out.

Example:

SELECT a.id, b.value
FROM A a
LEFT JOIN B b ON a.id = b.a_id
WHERE b.value = 'test';

This query will only return rows where there is a match in B and b.value = 'test', effectively an INNER JOIN.

To preserve all rows from A, the condition on B.value should be moved to the ON clause:

SELECT a.id, b.value
FROM A a
LEFT JOIN B b ON a.id = b.a_id AND b.value = 'test';