... Your second query, which you claim is a "hand coded natural join", is not
semantically equivalent to the first query, which uses the NATURAL JOIN syntax. ...
I know, that is the point I was trying to make???
I did not fail to attribute this to my code, I pointed out that the natural join syntax is stupid and error prone. It does, it can, it has caused many a mistake. there is a zero percent chance I would ever use it.
which is what I wrote above.
I don't have a problem with the data model. I have a problem with the natural join stuff which joins two tables together based on column naming conventions - and not foreign keys.
Now, if natural join read the dictionary and looked for the primary key/foreign key relationships (an unambiguous one) and did the join based on that - then I'd be impressed.
Using the same column names? beyond naive. beyond not smart. I don't know why the sql committee did that. It just isn't smart.Finally, consider that many aspects of SQL are non-intuitive,
please replace SQL with <any language goes here>. I'm sure you wouldn't claim this was a bug, rather attribute the mistake
to the user's unfamiliarity with the language or the data model.
well, umm, YES - why wouldn't I? Do you learn a language before you use it? What does?????
Here, take this code:
int x = 42;
int y = 0;
x = x + (--y + y--);
printf( "x = %d, y = %d\n", x, y );
what is the answer? Unless you know C, unless you really know C, you won't be able to answer that short of guessing and you won't be able to write safe C code (well, I guess you could write the most pedantic C code ever using 1% of the language.... hmmm, that sounds like a lot of SQL coders I know...)
The answer by the way is 40, because Y is decremented once with the --y, resulting in -1 being added to -1 and then y is decremented again resulting in -2. The answer is:
[tkyte@localhost ~]$ make test
cc test.c -o test
[tkyte@localhost ~]$ ./test
x = 40, y = -2
Not intuitive, unless of course you knew C.