That wasnt the answer to the question, I know this, it was just a
question on natural join, im assuming natural join though isnt too
portable but very clean sql for join columns that are already setup that
way. We use mysql anyway.
Bill Karwin wrote:
-----Original Message-----
From: Dan Rossi [mailto:[EMAIL PROTECTED]
Both USING and NATURAL JOIN syntax forms are ANSI SQL
standard, but do all our supported database brands
support these forms?
Interesting. Yes I was also looking at this. Ive never used
this keyword at all. usually INNER so it collects the least
ammount of rows. Is it the same pretty much, but just
automatically detects the join columns ?
Im assuming like foreign keys the join columns need to be the
same type.
Okay -- now we are talking about SQL, not Zend Framework specifically.
INNER JOIN means that the result set contains only rows from each table
that match the join condition.
SELECT * FROM a INNER JOIN b ON a.col1 = b.col1
The keyword INNER is optional. So INNER JOIN and JOIN are the same
thing.
USING is an shorter form, but accomplishes exactly the same thing,
assuming the named column exists in both tables, and the comparison
operator is equality (=).
SELECT * FROM a JOIN b USING (col1)
USING can also take multiple column names. The following two queries
achieve the same result:
SELECT * FROM a JOIN b ON a.col1 = b.col1 AND a.col2 = b.col2
SELECT * FROM a JOIN b USING (col1, col2)
NATURAL JOIN is even shorter. You don't have to mention the columns in
the join condition at all; the query implicitly uses _all_ columns whose
names appear commonly in both tables. If col1 and col2 are the only
columns names both tables have in common, the following should achieve
the same result as the previous query:
SELECT * FROM a NATURAL JOIN b
There is no requirement that the columns have the same data type, only
that they are data types that can be compared with an equality operator
(the limits on what data types can be compared varies by RDBMS
implementation).
There is no requirement that the columns are related using a foreign key
constraint. Columns that are related by referential integrity are often
what you want to use in join conditions, but that is not a requirement
of the SQL language.
Regards,
Bill Karwin
