SQL JOIN vs LEFT JOIN: A Comprehensive Guide with Examples

In SQL, JOIN and LEFT JOIN are operations used to combine rows from two or more tables. While they share a common goal, they have distinct behaviors.

JOIN returns rows where there's a match in both tables. It only includes rows with matching values in both tables. If a row in one table doesn't have a corresponding match in the other, it's excluded from the result.

LEFT JOIN returns all rows from the left table (the table mentioned first in the query), regardless of whether there's a match in the right table. If there's no matching row in the right table, NULL values are returned for the columns from that table.

Essentially, JOIN requires a match in both tables, while LEFT JOIN allows rows from the left table to exist without a match in the right table.

Illustrative Example

Let's assume we have two tables: Table A and Table B.

Table A:

| ID | Name | |----|------| | 1 | John | | 2 | Jane | | 3 | Tom |

Table B:

| ID | Age | |----|-----| | 1 | 25 | | 3 | 30 | | 4 | 35 |

Using JOIN:

SELECT A.ID, A.Name, B.Age
FROM A
JOIN B ON A.ID = B.ID;

Result:

| ID | Name | Age | |----|------|-----| | 1 | John | 25 | | 3 | Tom | 30 |

Using LEFT JOIN:

SELECT A.ID, A.Name, B.Age
FROM A
LEFT JOIN B ON A.ID = B.ID;

Result:

| ID | Name | Age | |----|------|-----| | 1 | John | 25 | | 2 | Jane | NULL| | 3 | Tom | 30 |

Observation:

The JOIN operation only returns rows with matching IDs (1 and 3). Conversely, the LEFT JOIN returns all rows from Table A, including the row with ID 2 that doesn't have a match in Table B. In this case, the Age column is filled with NULL for the ID 2 row since it doesn't have a corresponding entry in Table B.

Choosing between JOIN and LEFT JOIN depends on your data retrieval needs. Use JOIN when you only want rows with matches in both tables, and use LEFT JOIN when you need all rows from the left table, including those without a match in the right table.