What is a left join in SQL?

The LEFT JOIN (or LEFT OUTER JOIN) in SQL is used to combine rows from two tables.

The result of a LEFT JOIN includes:

• All rows from the left (first) table.
• Matching rows from the right (second) table where the join condition is met.
• For rows in the left table with no match in the right table, the columns from the right table will contain NULL values.

Syntax example:

SELECT <column_list>
FROM <left_table> AS L
LEFT JOIN <right_table> AS R ON L.<join_column> = R.<join_column>;

How it works:

Each row of the left table is examined. For each row, matching rows in the right table are searched based on the ON condition.

• If matches are found, the left table row is combined with all matching right table rows.
• If no matches are found, the left table row is included in the result, and columns from the right table are filled with NULL.

Visual representation:

Imagine two data sets: Left (left table) and Right (right table). LEFT JOIN returns all elements from Left, as well as those from Right that have a match in Left. If there are elements in Left without a match in Right, these will still be included in the result, but the corresponding fields from Right will be NULL.

Comparison with other JOIN types:

JOIN Type	Description
INNER JOIN	Returns only rows where there is a match between both tables.
LEFT JOIN	Returns all rows from the left table and matching rows from the right. NULL for non-matching rows from the right.
RIGHT JOIN	Returns all rows from the right table and matching rows from the left. NULL for non-matching rows from the left.
FULL OUTER JOIN	Returns all rows where there is a match in either table. NULL for non-matching rows in both tables.

Thus, LEFT JOIN is useful when you want to get a complete list of records from one table and supplement them with information from another table, even if not all records have matching data.