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. Of course, you go on to point out your mistake (i.e. you omitted MANAGER_ID from the join condition) but fail to attribute this to your code, instead blaming the implementation (even though it seemingly complies with the SQL Standard).
The thrust of your argument seems to be that for the casual observer the two tables should be joined using DEPARTMENT_ID alone. Surely, then, your complaint should be directed to the designer of the model who chose to use the same element name for two different predicates, "Employee...reports to manager MANAGER_ID" and "Department... is headed by manager MANAGER_ID" respectively, and commits this design flaw on tables that are expected to be frequently joined in SQL DML.
Finally, consider that many aspects of SQL are non-intuitive, the prime example being nulls. Not only is three-valued logic (3VL) hard for users familiar with first-order logic to comprehend, 3VL is inconsistently implemented in SQL, one must learn all the exceptional cases (e.g. that SUM for a column of type INTEGER that is all nulls returns NULL but COUNT for the same column returns 0). An analogue of your answer would be a novice using the equals operator involving nulls e.g. using NULL = NULL and intuitively thinking it to evaluate TRUE. 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.
May 23, 2013 - 3:02 pm UTC
... 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.