"H Cohen" <ha**********@y ahoo.com> wrote in message
news:15******** *************** ***@posting.goo gle.com...
If a database has relationships establshed between all of the tables
via primary and foreign key constraints, why isn't is possible to make
a SELECT statement across multiple tables without using a JOIN?
If the system knows the relationsip schema already why are JOINS
required?
Thanks,
HC
Well, for a start what type of join would it be - inner, outer, cross? And
what would you join on - you might not want to join on col1 = col2, you
might want to join on col1 < col2, or col1-1 = col2 etc. Or you might want
to join on non-key columns. And you would have to assume that every database
is normalised, there is only one possible relationship between each pair of
tables, and all the relationships are enforced correctly, which is unlikely
to be true all the time.
If you specify what you want explicitly then it's clear to others reading
your code what you intended, and it also makes it easier to handle schema
changes and other code changes without the added confusion of the system
'automagically' doing things for you.
I suspect you're thinking mainly of the simplest possible case - join two
tables with an inner join using an equality comparison. While I suppose you
could introduce some kind of meta-syntax to avoid fully typing out the
primary key column names, that would be a false economy compared to the
potential issues, and of course it wouldn't work at all in some of the cases
I mention above.
Simon