Multiple conditions in ON clause when using LEFT JOIN

We are familiar with SQL joins with only one join condition, the foreign key in one table equal to the referenced column in another table. In fact, we can do more than that by specifying extra conditions. To demonstrate that, we're gonna use two tables that will be joined later, the orders and order_items, as the order_id in order_items table references the id in order table.

orders

order_items

We've already got bunch of records in both tables.
First, let's specify one condition in the ON clause, the join column, and show the result.

SELECT * FROM orders AS o LEFT JOIN order_items AS i ON o.id = i.order_id

Fig.1



Now let's add one more condition, the name of the order_items must be ''Toast", and we got this.

SELECT * FROM orders AS o LEFT JOIN order_items AS i ON o.id = i.order_id AND i.name = "Toast"

Fig.2


Comparing Fig.1 and Fig.2, we can see that the addition of the second condition in ON clause disqualifies any records in order_items table whose name is not "Toast" as the join candidates. So the first order record ended up with no order items attached to it, thus having NULL value in each column of order item.

It's easy to think that (at least for me for the first time) moving the second condition in ON clause to WHERE clause would have the same effect.

SELECT * FROM orders AS o LEFT JOIN order_items AS i ON o.id = i.order_id WHERE i.name = "Toast"

After we run the SQL command above, we got this.

Obviously, they are different.

The essential difference between these two commands is that the first one eliminate any order item records that do not meet the condition BEFORE join, while all the condition in WHERE clause remove any records that do not meet the condition AFTER the join is done.