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.