SQL left join reference

In a nutshell

Left joins let you combine rows from multiple tables. Here’s an example left join:

SELECT a.title, u.name
FROM articles a
         LEFT JOIN users u ON u.id = a.user_id

The result of this query will look something like:

Left joins return null for any columns (name in the above example) where there are no matching records from a join table (users in the above example) are found.

How a left join is processed by a SQL engine

This is what happens when the above query is parsed by a SQL engine:

1. The SQL engine begins by selecting a row from the articles table (let’s call it “Row A”).
2. It then performs a LEFT JOIN with the users table based on the condition u.id = a.user_id.
   • It searches the users table for a row where the value of u.id (users.id) is equal to the value of a.user_id (articles.id) from “Row A”.
   • If a match is found, it retrieves the name from the corresponding row in the users table (let’s call it “Row U”).
   • If no match is found, set the result of name to NULL).
3. The result set for “Row A” includes the title column from “Row A” and the corresponding name column from “Row U” (if a match was found) or NULL (if no match was found).
4. Steps 1-3 are repeated for each row.

What happens when there are multiple matching records in a join table?

Suppose you had the following tables:

users

articles

And you executed the following query:

SELECT a.id article_id, u.id user_id
FROM articles a
         LEFT JOIN users u ON u.id = a.user_id

The result would look something like:

We get two records in the result set where the user_id is 1 because of how the SQL engine handles multiple matching records in a join table:

1. The SQL engine begins by selecting a row from the articles table (let’s call it “Row A”).
   It then performs a LEFT JOIN with the users table based on the condition u.id = a.user_id.
2. It searches the users table for a row where the value of u.id (users.id) is equal to the value of a.user_id (articles.id) from “Row A”. If multiple matches are found as is the case when a.user_id is 1, then it generates a new row set in the result set.
3. The result set for “Row A” includes the id column from “Row A” and the corresponding user_id column from “Row U” (if a match was found) or NULL (if no match was found).
   Steps 1-3 are repeated for each row.