Paul Hastings wrote: >>I think that is a limitation of MS SQL Server, I can't find any such >>thing in the SQL standard and it works fine for me in PostgreSQL: > > > a plain jane inner join with identitical columns removed (manually) from the > select list is a "natural join" for sql server. > SELECT * becomes SELECT table1.a, table1.b, table2.* (where columns a,b are > identical in both tables). sql server 2k is "Entry Level SQL-92" if thats > the sql standard you are referring to.
I am refering to the fact that I can't find any limitation to the number of natural joins allowed in a FROM. The error message was "the correlation name NATURAL is specified multiple times in a FROM clause", which makes me believe one natural join would be supported, but multiple aren't. I find that a bit weird. How would that work out if you are doing a natural join on 2 views that themselves have natural joins in their definition? If you rewrite the query tree, you suddenly end up with 3 natural joins and the query fails? I hope not. > just curious, how many dbs actually fully support sql-92 or sql-99? I don't think there is a single database that does. > does > full support imply not using things that aren't part of the standard, if i > recall correctly triggers & indexes weren't part sql-92 (and some dbs like > ocelot-sql wouldn't support these as a result). You are allowed to extend features. For instance, I would certainly never expect indexes to make it into the standard. The only reason they are used is for speed, not because they have any meaning in terms of the data stored in them. As such, they sort of break normalization (you are storing whatever you are indexing twice, what is your unique row identifier etc.) and thus are evil in a relational model. But in real live, I love indexes ;-) Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
