The Difference Between SQL’s Join –. On Clause and the Where Clause

I’m using the Sakila database, as always:

This will yield something like:

Of course, we could have written this instead, and received the same result:

Now, I’ve moved the filter from the clause to the clause. Both queries yield the same result as well as the same plan. How about this:

This used to produce the same result for an ( ) , but given the , we’re now not getting Susan Davis in the result:

The plans are also different:

The first query did not produce an outer join operation, the second one did!

Source: blog.jooq.org