
The Difference Between SQL’s JOIN .. ON Clause and the Where Clause - pplonski86
https://blog.jooq.org/2019/04/09/the-difference-between-sqls-join-on-clause-and-the-where-clause/
======
snidane
The syntax for joins is unnecessarily complex. The difference in behaviour is
determined by the operator used for comparison between the two tables. If
there existed modifiers for the operators to be sensitive to nulls, no need
for special syntax would be needed.

If we define /<op> to make the operator include nulls on the left side and
<op>\ to include nulls on the right side, we can do joins of all kinds without
the JOIN ON syntax.

Example.

inner join

select * from a,b where a.id=b.id

left join

select * from a,b where a.id=\b.id

right join

select * from a,b where a.id/=b.id

outer join

select * from a,b where a.id/=\b.id

~~~
Svip
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.

~~~
noisy_boy
IIRC, Oracle also supports this "* =" and "= *" syntax. I started with that
and then learned ANSI JOINs; was difficult initially because I was used to the
former syntax but I would agree that ANSI JOINs are more clearer/cleaner. That
and the "with" clause makes queries so much more readable.

------
sheeshkebab
Contrary to articles conclusion, there are databases (i.e. Oracle) where it
doesn’t matter how you write joins, but oracle also has support for
left/right/full outer joins in the where clause

Left join ... is the same as leftcol=rightcol(+) (oracle has special syntax
for this with that + sign).

In summary, depending on database, it may not matter how you write these
joins, but it’s best to stick to SQL standard using JOIN if you’d like cross
database support.

~~~
irrational
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.

~~~
panarky
"this may not continue to be true in future releases, and Oracle recommends
that you do not treat empty strings the same as nulls"

[https://docs.oracle.com/database/121/SQLRF/sql_elements005.h...](https://docs.oracle.com/database/121/SQLRF/sql_elements005.htm#SQLRF30037)

~~~
mcguire
How are we supposed to treat them differently? Oracle's the one putting the
damn nulls in the db.

(Recently tripped over this one.)

------
jimmytucson
As soon as you filter on the table you’re joining with in the where clause it
becomes an inner join.

    
    
        FROM foo
        LEFT JOIN bar ON ...
        /* Makes it an inner join: */
        WHERE bar.baz ...

~~~
astine
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.

~~~
Svip
Indeed, a faster way to check if a table does _not_ have a comparable row is a
LEFT JOIN with a WHERE NULL clause:

SELECT t1.* FROM t1 LEFT JOIN t2 ON t2.id = t1.foreign_id WHERE t2.id IS NULL

Is a lot faster than the naïve alternative:

SELECT t1.* FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE id =
t1.foreign_id)

~~~
brokensegue
Unless the query is optimized?

~~~
Svip
For a simple query like this, then yes, it will be optimised. But once your
queries get more complicated, the optimiser might not be able to make that
deduction on its own.

