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.
select * from a,b where a.id=b.id
select * from a,b where a.id=\b.id
select * from a,b where a.id/=b.id
select * from a,b where a.id/=\b.id
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.
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:
FROM classrooms, enrolments
LEFT JOIN enrolments
ON classrooms.id = classroom_id;
LEFT JOIN enrolments
ON classrooms.id = classroom_id
WHERE classroom_id IS NOT NULL;
// ^ should have been classrooms.id
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.
LEFT JOIN (
inner join table2 ON table2.ID = table1.table2id
and table2.othertype = 1234
) on table1.outerid = outertable.Id
Although, the most modern I've had this experience with is SQL Server 2014, but that definitely still needed guiding a few times.
(select *, outerid from table1,table2 where table1.table2id = table2.ID and table2.othertype = 1234) t12
where outertable.Id =\ t12.outerid
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.
Wow, that's ridiculous. They are very clearly two different things. For example "this has no value" and "we don't know the value of this".
I agree though. Makes as much sense as treating 0 and NULL as the same.
I've spent much more time with Oracle than with other RDBMS. So, what made you think so?
> The author didn't specify any particular database, but wrote as if what he was saying applied to all relational databases
Yes, in this article's case, the article and conclusion applies 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.
I am aware of this, but how is it related to the article?
MSSQL allows this behaviour, since it's based on Sybase, but you have to explicitly turn it on.
(Recently tripped over this one.)
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.
LEFT JOIN bar ON ...
/* Makes it an inner join: */
WHERE bar.baz ...
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)