The Difference Between SQL’s JOIN .. ON Clause and the Where Clause

That's the old join syntax before ANSI SQL was designed. Except your operators look like "* =" and "= *" (without whitespace of course, blame HN's markdown parser). Sybase supported this syntax well into its version 15.x (maybe still in version 16?). And Sybase did not (does not?) support full outer joins.

It may seem simple on the surface, but once you get into complex queries, it's a horrible syntax, and it's easy to forget the relations between the tables, particularly with several tables (4+) involved.

As someone who has converted an old and large SQL code base from non-ANSI JOINs to ANSI JOINs, I am happy to see this style of syntax abandoned.

Eh—I would have designed SQL differently if I were in charge (especially for casts, function definitions, and string munging) but leaving that aside, I disagree that the JOIN ON syntax is unnecessarily complex.

We do not write programming languages solely for computers. We write them for humans too. Humans make mistakes and come at codebases at all manners of experience, both in breadth and depth.

For example, it may literally be someone's first time looking at SQL when they're confronted with the following:

    SELECT *
    FROM classrooms, enrolments
    WHERE classrooms.id=\classroom_id;

It is inherently less clear than the following:

    SELECT *
    FROM classrooms
    LEFT JOIN enrolments
    ON classrooms.id = classroom_id;

It's easier to figure out bugs too, because it leaves more room to understand what the person intended to do rather than what they actually did. For example, say the classrooms table had, due to legacy issues, a nullable id field, but we didn't want to include those classrooms. Someone may have inadvertently typed:

    SELECT *
    FROM classrooms
    LEFT JOIN enrolments
    ON classrooms.id = classroom_id
    WHERE classroom_id IS NOT NULL;
    //    ^ should have been classrooms.id

Because the NOT NULL criteria is in the where clause, it's clear it's not supposed to impact the join. It's supposed to be part of the filter. I know it's the same thing practically, but they're conceptually different.

Further, as you get into more complex queries (unions, recursive, cross joins, aggregations) stuffing everything into one hairy WHERE statement gets impossible to understand because it does everything all at once.

But sometimes you want to outer join on the ID matching and some other condition, and thats where the JOIN syntax gets more useful, because the 'outer' then obviously applies to a whole series of logic statements that are and/or'd together. If you write reports its not long before you start hitting really obscure joins and conditions and subqueries and so on.

How would you solve a left join like this with that kind of syntax?

LEFT JOIN ( inner join table2 ON table2.ID = table1.table2id and table2.othertype = 1234 ) on table1.outerid = outertable.Id

I have noticed that many authors of SQL articles don't seem to be aware of Oracles quirks. For example, recently I read an article about NULLs in SQL. The author didn't specify any particular database, but wrote as if what he was saying applied to all relational databases. But Oracle treats empty strings and NULLs as the same thing, which I don't think the article's author was aware of.

Oracle's (+) syntax does not allow for full outer joins.

The conclusion would be the same with Oracle's syntax. It's just more obscure, as it would turn into a "fa.film_id < 10" vs "fa.film_id(+) < 10" discussion.

Only if you're resting for a non null value. If you test for a null value, you'll be looking for all values in one table that don't have a match in the other table.